CODEWITHSUNDEEP
phpmysqllaraveleloquentfiltering
Zoltán Fekete
Zoltán Fekete

Reputation: 594

Laravel eloquent - filter by concatenation of multiple columns

I'm new to Laravel, and I got stuck trying to perform the following. I have a simple users table with the following columns:

I'm about to make a user list with an option of filtering. One of the filters is full_name, but I do not store full_name of the users, and I can't modify the table structure.

Over a few days I got to this:

$query = \DB::table('users');
$query->select(\DB::raw('CONCAT_WS(" ", `last_name`, `first_name`) 
        as `full_name`, id'))->having('full_name', 'LIKE',$input['filter_name']);
$result = $query->get(['*']);

But it's not working.

Spec: I'm using the latest laravel.

Upvotes: 2

Views: 2821

Answers (3)

Zoltán Fekete
Zoltán Fekete

Reputation: 594

I solved it. The first problem was, I forgot the "%" wildcard.

The second that is used a concatenated value as filter, then the Laravels paginate tried to count the overall result count, by using that concatenated value. Sure it does not exists. The solution is:

$query = \App\User::whereDeletedAt(null);
$query->where(\DB::raw('CONCAT_WS(" ", `last_name`, `first_name`)'), 'like', '%' . $input['filter_name'] . '%');
$query->paginate(15, ['*'], 'page', 1);

Upvotes: 0

Lars Mertens
Lars Mertens

Reputation: 1439

I think you forgot the wildcards in the LIKE statement.

Instead of this

$query->select(\DB::raw('CONCAT_WS(" ", `last_name`, `first_name`) 
    as `full_name`, id'))->having('full_name', 'LIKE',$input['filter_name']);

Try:

$query->select(\DB::raw('CONCAT_WS(" ", `last_name`, `first_name`) 
    as `full_name`, id'))->having('full_name', 'LIKE', '%' . $input['filter_name'] . '%');

Upvotes: 1

Tony
Tony

Reputation: 513

Maybe instead of trying to achieve it by modyfing query, get query like as normal: $users = DB::table('users')->get(); And then, use collections. ->filter(function($key, $value) { return strpos($value['first_name'].' '.$value['last_name'], $to_find) !== false })->all();

Upvotes: 0

Related Questions