ctyler
3 months ago

Difficulty translating a query to Query Builder

Posted 3 months ago by ctyler

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.

Please sign in or create an account to participate in this conversation.