Eloquent: Order parent results by relationship column

eloquent-parent-orderby.php

<?php

$products = Shop\Product::join('shop_products_options as po', 'po.product_id', '=', 'products.id')
   ->orderBy('po.pinned', 'desc')
   ->select('products.*')       // just to avoid fetching anything from joined table
   ->with('options')         // if you need options data anyway
   ->paginate(5);

// SELECT clause is there in order to not appending joined columns to your Product model.
// Originaly found at: http://stackoverflow.com/questions/23530051/laravel-eloquent-sort-by-relation-table-column

Thanks

I have built this practice exactly the same way on my own and when I get here I can confirm that I am doing well.

However, I am presenting a problem.
I currently have a model called Album and another Song

Album::whereDate('albums.release_date', '>=', Carbon::now()->subWeeks(1))
                ->join('songs', 'songs.album_id', '=', 'albums.id')
                ->orderBy('songs.downloads', 'desc')
                ->select('albums.*')
                ->limit(10)
                ->get()

As you can see I'm only getting 10 results. This query makes the albums repeat if they have more than one song. That is, if an album has 3 songs in the 10 results I'm getting, it will be the same album 3 times. Do you know how to avoid this behavior?

This is being caused by the join type you’re using. At first glance I would recommend a LEFT JOIN, but you probably need to study SQL joins and arrive at the best one for your data models.

Once you have a proper join set up, your ORDER BY clause will need to be addressed. What you have now will simply order the albums by the downloads number on the first song record that is joined. It would make more sense to sum downloads for all songs on an album first and then order by that value.

Thank you for your quick response.

I used to have it with leftjoin but the result is the same.

That's what I'm trying to do, sort the Albums by the number of downloads that have their Songs, but with the collections after the query. I tried for hours to try to do it directly with Eloquent but I don't know how to do it.