Difficulty translating a query to Query Builder

I am working on an ajax query shown here:

SELECT * 
FROM users
join provider_user on users.id = provider_user.user_id
WHERE provider_user.provider_id = 16 (this will be a parameter)
AND (firstname LIKE '%r%'
or lastname LIKE '%r%'
or email LIKE '%r%')

This returns 3 results which is correct - no duplicates. However, when I convert it to Laravel Query Builder it is returning a user 3 times for a total of 5 times. Here is the code for the query builder:

$provider_user = Auth::user(); (This is 16)
$query = $request->get('query');
            $data = DB::table('users')
                ->join('provider_user', 'users.id', '=', 'provider_user.user_id')
                ->when($query, function($localquery, $query) {
                    return $localquery->where('firstname', 'LIKE', '%'.$query.'%')
                        ->orWhere('lastname', 'LIKE', '%'.$query.'%')
                        ->orWhere('email', 'LIKE', '%'.$query.'%');
                })
                ->where('provider_user.provider_id', '=', $provider_user->provider_id)
                ->get();

Any help would be appreciated.

ctyler

6 years ago

Level 6

Good Afternoon all. I did come up with a work around with a DB::select.


$data = DB::select(
                'select *
                        from users
                        join provider_user on users.id = provider_user.user_id
                        WHERE provider_user.provider_id = :provider
                        AND (firstname LIKE :query1
                        OR lastname LIKE :query2
                        OR lastname LIKE :query3)', ['provider'=> $provider_user->provider_id, 'query1' => '%'.$query.'%', 'query2' => '%'.$query.'%', 'query3' => '%'.$query.'%']
            );

Why am I using query1, query2 etc when they are all the same. You cannot reuse a name parameter. However, my initial post still stands. Wh would that not work?

jlrdw

6 years ago

Level 75

You could use as is but bind parameters

https://laracasts.com/discuss/channels/laravel/sql-native-to-query-builder

Or an example of an eloquent query that works

$quy = Powner::query()->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
                ->select('dc_powners.ownerid', 'dc_powners.oname')
                ->selectRaw('count(dc_pets.petid) as countOfPets')
                ->groupby('dc_powners.ownerid')
                ->orderby('dc_powners.oname')
                ->get();

Results basically give:

ownerid, oname, countOfPets

Like:

5|Bob|3
4|Greg|9
2|Rob|1
// more

The last example bindings are taken care of for you.

Note that these types of queries can take a little trial and error to work out.

Please or to participate in this conversation.