Best practise for DB query builder location

Level 102

You could use actions. https://freek.dev/1371-refactoring-to-actions

martinbean

Level 80

Do you have any advice, or is it a thumbs up to go with the model?

@rightwayround Yes. Prefer using models over raw DB expressions.

karam mustafa

Level 1

Hello, you can use a separate layer such as a Respoitories Design pattern or a Service pattern.

Tray2

Level 73

I place my queries in the controller, something like this

return view('book.show')
		->with([
		'book' => Book::findOrFail($id)
	]);

I try to KISS.

Level 1

@martinbean, @sinnbeck, @karam mustafa - thanks for your input. The reason I am using DB query builder is because I have users that belong to organisations (many-many). In the admin section, an admin can associate a user with multiple organisations.

That page shows one long list of all organisations, sorted firstly by organisations that the user is a member of, then organisations that the user is not a member of - with a toggle to add membership. I had a long fight with eloquent to in an attempt to get the results sorted on the child relationship - but it was not fruitful, and seems like exactly the type of task best suited to a database.

My DB query looks like this:

    $usersOrgs = DB::table('organisations')
        ->leftJoin('organisation_user', 'organisations.id', '=', 'organisation_user.organisation_id')
        ->leftJoin('users', 'users.id', '=', 'organisation_user.user_id')
        ->select('users.*', 'organisations.*')
        ->where('users.id', '=', $user->id)
        ->orWhereNull('users.id')
        ->orderByRaw('-users.name DESC')
        ->orderBy('organisations.OrgName')
        ->get();

My resource route is /admin/users/{user}/organisations/{organisation}

I am most tempted to add the query to the organisations model as I think that I will be easily able to find it there in the future. I struggle to see the benefit of a separate layer as suggested by @karam mustafa - to me this is adding complexity.

Is the way I am going about this wrong in some way, am I doomed to failure / hard to maintain code like this?

Level 102

@rightwayround You can still use eloquent and mix it in :)

$usersOrgs = Organisation::query()
        ->leftJoin('organisation_user', 'organisations.id', '=', 'organisation_user.organisation_id')
        ->leftJoin('users', 'users.id', '=', 'organisation_user.user_id')
        ->select('users.*', 'organisations.*')
        ->where('users.id', '=', $user->id)
        ->orWhereNull('users.id')
        ->orderByRaw('-users.name DESC')
        ->orderBy('organisations.OrgName')
       ->toBase() //if you dont want eloquent models.
        ->get();

martinbean

Level 80

@rightwayround You do realise that Eloquent models just use the DB query builder under the hood, so anything you can do with DB you can do with an Eloquent model, right…?

Level 1

@martinbean yes I understand that. Although I don't think that I could retrieve the models sorted in the way that I want them without resorting to Query Builder could I?

Level 1

@Sinnbeck ... a local dynamic query scope in the model??

Best Answer

Level 102

@rightwayround sure you could in theory move that whole thing to a scope and call it using eloquent

I use eloquent for my raw queries as well. I just use toBase to get a stdClass

1 like

Level 1

@Sinnbeck sincere thanks for your advice