CODEWITHSUNDEEP
phpmysqllaraveleloquent
Mr. B.
Mr. B.

Reputation: 8717

Laravel/Eloquent: How to use WHERE LIKE for combined fields?

I've a form with 1 input to filter by the full(!) name:

<label>Search by fullname:</label>
<input type="text" name="fullname">

Problem: the fullname is splitted into 3 fields in my databases user table:

users

id title firstname lastname
1 Dr. John Doe
2 Peter Pan

fullname = title + firstname + lastname

I'd like to find results for inputs like Dr. Doe, but also John Doe or just Peter.

Currently I only know how to filter each field separately:

$users = User::where('firstname', 'like', '%'.$keyword.'%')->get();

Upvotes: 2

Views: 1818

Answers (3)

Rwd
Rwd

Reputation: 35220

You can use DB::raw() and MySQL's concat to achieve this:

$users = User::where(DB::raw('concat(title," ",firstname," ",lastname)'), 'like', '%'.$keyword.'%')->get();

Upvotes: 4

stokoe0990
stokoe0990

Reputation: 473

Like Davit's answer, not a best solution, but it'll probably work.

$fullName = "Mr Philip Phil Philips";

$nameParts = explode(' ', $fullName);

$matching_users = User::whereIn('first_name', $nameParts)
                        ->orWhereIn('last_name', $nameParts)
                        ->orWhereIn('title', $nameparts)
                        ->get();

Upvotes: 1

Oluwafemi Sule
Oluwafemi Sule

Reputation: 38992

Creating a full-text index of title, firstname and lastname fields in the user table is a more correct approach to go.

You can perform a DB::raw query using MATCH(...columns) AGAINST expr syntax. 1

The cost of this approach is that the index is updated on writes. However, this gives you a robust search without you having to care about the ordering of any of the fields in the search query.

Upvotes: 1

Related Questions