Laravel8 - Eloquent query builder: how add a raw query?

Hi to all, I cannot write a Query Builder with a raw query inside.

I need to use the Query Builder 'cause I'm paginating and sorting the results via scoped query. Is it possible to inject that raw query that at the moment is commented out?

Taken alone, it works and return right recordset ;)

return $this
            //->select(DB::raw("SELECT * FROM users WHERE id IN (SELECT user_id FROM users_licenses WHERE license_id IN (SELECT id FROM licenses WHERE agency_id IN (SELECT agency_id FROM users_agencies WHERE users_agencies.user_id = $user_id)))"))
            ->with('role', 'agencies', 'licenses', 'permissions')
            ->sort(request())->paginate($this->getPerPage());

Thank you to all!

a4ashraf

5 years ago

Best Answer

Level 33

@robdevel

here is the solution


App\User::
			whereIn('id', function($query) use ($user_id) {
			    $query->select('user_id')
			    ->from('users_licenses')
			    ->whereIn('license_id', function($query)  use ($user_id) {
			    	$query->select('id')
			    	->from('licenses')
			    	->whereIn('agency_id', function($query)  use ($user_id) {
			    		$query->select('agency_id')
			    		->from('users_agencies')
			    		->where('user_id', $user_id);
			    	});
			    });
			})->with(['role', 'agencies', 'licenses', 'permissions'])->sort(request())
            ->paginate($this->getPerPage());

1 like

robdevel

5 years ago

Level 1

It is... simply perfect!

Only missing a semicolon after last ->from('users_agencies')->where('user_id', $user_id) :)

Really thank you!

a4ashraf

5 years ago

Level 33

@robdevel

feel good its help you,

make it close

Please or to participate in this conversation.