Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

mrkarma4ya's avatar

Order a query by its relationship

Lets say I have three models - Artbook, Collection, Artwork with relationship as follows

Artbook -> hasMany -> Collection -> hasMany -> Artwork

Artbook -> hasMany -> Artwork -> through -> Collection

----------------------------------

Artbook is a public model while Collection belongs to a user. So any user can create a Collection for any Artbook. Thus, the user relation is as:

User -> hasMany -> Collection

User -> hasMany -> Artwork -> through -> Collection

Note: Collection has an added_by column that contains the user's id. I have no idea why the previous developer didn't just use user_id instead.

----------------------------------

Now I have a page where I display all Artbooks a user has contributed to. Here, I need to sort the Artbooks by latest image uploaded by that user in that Artbook. The image may be in a new Collection or an existing one.

How do I achieve this? I've having a hard time querying the Artbook model in such a manner.

This is my current query for retrieving Artbooks (default sorting)

$artbooks = Artbook::whereHas('collection', function ($query) use ($request) {
   $query->whereAddedBy($request->user()->id);
})->withCount(['artwork' => function ($query) {
   return $query->whereHas('collection', function ($q) {
         return $q->whereAddedBy(request()->user()->id);
   });
}])->withFilters()->paginate($request->perPage ?: 20);

return ArtbookResource::collection($artbooks );

I want to add an orderBy clause in the above query that orders it by latest image in each artbook for a user.

0 likes
10 replies
aschmelyun's avatar

@mrkarma4ya First off, thanks for all of the background information (super helpful)!

Second, if you're okay with this being performed by PHP instead of the database, you should be able to call a sort method on the returned artbooks collection like this:

return ArtbookResource::collection($artbooks->sortByDesc('artwork.created_at'));

But thinking about it you'll also need to include that relationship lazy-loaded into the query by a ->with('artwork') method.

Tray2's avatar

Wile @aschmelyun solution would work, it's not the best approach. It's much better to let the database do it's job.

Here is an example made with the query builder that should work

DB::table('table1')
->join('table2', 'table1.table2_id', '=',  'table2.id')
->select('table1.*', 'table2.*')
->orderBy('table2.column2')
->get();
3 likes
mrkarma4ya's avatar

@aschmelyun @tray2

Thank you guys for your help!

I spent some time building raw query for this, please take a look if its done correctly ( I barely do raw SQL, so kind of a noob in that department :P ). I may also need some help writing this in Eloquent or Query Builder if its correct.


SELECT artbooks.*, 
	MAX(artwork.created_at) AS aw_date,
	COUNT(awc.id) AS aw_count
FROM artbooks 
INNER JOIN collections ON artbooks.id = collections.artbook_id AND collections.added_by = 4
INNER JOIN artwork ON artwork.id = (
	SELECT id
	FROM artwork AS aw
	WHERE aw.collection_id = collections.id
	ORDER BY created_at DESC
	LIMIT 1
)
INNER JOIN artworks AS awc ON awc.collection_id = collections.id
GROUP BY artbooks.id
ORDER BY aw_date DESC

PS: collections.added_by should be replaced by $user_id later

bugsysha's avatar

This goes into more details than I'm willing to write. Check it out. A very nice explanation so you should be able to do it yourself after reading.

1 like
mrkarma4ya's avatar

@bugsysha Thanks a lot! It doesn't cover has many through relationships, but I will follow this guide and try to do it on my own!

Please or to participate in this conversation.