i want to search into users table by fullname which needs to concat two columns firstname and lastname and compare to the given value
User::where(function($usersSearchQuery) use ($user,$fullName){
$usersSearchQuery->whereRaw("CONCAT('first_name', 'last_name') = ?", [$firstName]); })->get();
i got this error
message: "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'full_name' in 'where clause' (SQL: select * from
userswhere (1 = 1 and 1 = 1 andfull_nameLIKE sdfdf%))"
You can make a function in User model itself.
public $appends = ['full_name'];
public function getFullNameAttribute()
{
return "{$this->first_name} {$this->last_name}";
}
// You can call it like this from controller or from view
$users = User::all();
foreach($users as $user) {
$fullName[] = $user->full_name;
}
I think you should change your select to ->select(DB::raw("CONCAT(first_name, ' ', last_name) as full_name")), after that, it should work. Or (if you are using Laravel 8), you can directly use selectRaw.
If it is still not working, change where with whereRaw like ->whereRaw('full_name LIKE "?%"', [$fullName]);. So have both selectRaw and whereRaw.
User::where(function ($usersSearchQuery) use ($user, $fullName) {
return $usersSearchQuery->selectRaw("CONCAT(first_name, ' ', last_name) as full_name")
->whereRaw('full_name LIKE "?%"', [$fullName]);
})->get();
users where (1 = 1 and 1 = 1 Julianne% full_name))"users where (1 = 1 and 1 = 1 and full_name LIKE "Julianne%"))"The error exist in
->whereRaw('full_name','LIKE',$fullName.'%');
since the query output of the above eloquent query is
select CONCAT(first_name, ' ', last_name) as full_name from users
where full_name like 'SANDEEP%';
In this case the "full_name" attribute is not yet known to the query and it is not a column of users table, so the error says column not found, for this reason it is good to use sub query
Select * from (select CONCAT(first_name, ' ', last_name) as full_name from users) as u
where full_name like 'SANDEEP%';
whose equivalent query is
$sub = DB::table('users as u')->selectRaw("u.*, CONCAT(first_name,' ',last_name) as full_name");
$users = DB::table(DB::raw("({$sub->toSql()}) as sub"))
->whereRaw("full_name LIKE '{$fullName}%'")
->get();
