Eloquent order by related table

You could use an eager loading constraint possibly? http://laravel.com/docs/4.2/eloquent#eager-loading (scroll down a bit).

Note that this will only order that column / relation result... not order the entire collection by that. If you want to order the entire collection / result set by something from a related model, you'll have to to use a join.

What about query scopes? http://laravel.com/docs/4.2/eloquent#query-scopes

As austenc said you would need to use a join if you want the whole collection to be ordered - if you're using paging that's probably what you want.

Possible partial solution using a join:

SELECT categories.id, categories.name AS category_name, COUNT(pictures.id) AS picture_count
FROM categories
JOIN pictures
ON categories.id = pictures.category_id
GROUP BY categories.id
ORDER BY picture_count DESC

(I'm assuming you have categories table with id, name and pictures table with id, category_id, name).

The problem with the above is if there are no images in the category, then the category will not show up! Any SQL gurus here?

Thanks everybody!

Unfortunately, a SQL join (with Eloquent) will mess up the ->count(). As for SQL, it's almost exactly as WookieMonster commented.

A nice approach is here: http://laravel.io/forum/05-23-2014-counting-and-filtering-results-via-a-pivot-table for pivot tables, which I think it can be easily modified for hasMany relationship.

Bashy, care to elaborate on the scopes? I've used them heavily, but only for an elegant and fluent substitute to complex where's (within a single query). I do not see how you can have a new field returned with the model and order by it outside the scope.

Basically, what I'm looking for is this:

$categories = \Category::with('Picture')->fooPicCount()->orderBy('agg_pic_count',$ord);
// where fooPicCount is a scope function?

// and the fulminant result will be:
foreach($categories->get() as $category)
{
    echo "category" . $category->name . ' has ' . $category->agg_pic_count . ' pictures';
}

Thanks again.

Why would it mess up the count? If you join correctly (and use a proper groupBy), this shouldn't be a problem? You could also select the count of a given field as a new property. http://laravel.com/docs/4.2/queries#raw-expressions The example there shows what I'm talking about.

Something like this line:

->select(DB::raw('count(category_id) as total_categories, category'))

Hopefully some of that helps. You can definitely accomplish this via query builder methods to replicate exactly the SQL wookie mentioned... without having to write the full query in the raw.

Maybe you can try something like this:

$user->accounts()->with(['bookings' => function($query)
{
    $query->orderBy('id', 'desc')->take(1);
}])->get();

The relations on this query are exactly the same like yours (accounts hasMany bookings, and a booking belongs to an account).

Think of it as like a subquery where you can extend the main query. I'm not sure if aggregate functions works as well, but you can give it a try.

I've literally just had the same issue and my solution (modified to fit your situation) was

$categories = Category::select(DB::raw('categories.*, count(*) as `aggregate`'))
    ->join('pictures', 'categories.id', '=', 'pictures.category_id')
    ->groupBy('category_id')
    ->orderBy('aggregate', 'desc')
    ->paginate(10);

Mine involved a pivot table but the idea is the same, join the tables, group by category, order by aggregate.

I just realised I should have used a left join instead of a regular join, then it will include categories where there are no pictures too:

SELECT categories.id, categories.name AS category_name, COUNT(pictures.id) AS picture_count
FROM categories
LEFT JOIN pictures
ON categories.id = pictures.category_id
GROUP BY categories.id
ORDER BY picture_count DESC

Use ->leftJoin() method in eloquent.

Hi, What if softDelete is used on Picture model? then the count wouldn't be correct.

And in case of using a morph relation like comments instead pictures, then this is a deeper issue. And in my case, I am paginating the whole result! so a proper collection must be passed to the paginate method in order to work correctly. A real case for this is having a blog, and trying to sort the posts according to the Most Discussed and Most liked ones I ended up using fluent engine, but I hope there is an elegant way using Eloquent. Thank you guys

@alsofronie For the count itself on hasMany relation, this is the way to go: softonsofa.com/tweaking-eloquent-relations-how-to-get-hasmany-relation-count-efficiently/

This won't let you order by the count, so you still need the join to achieve that. That said, in this very case, there's no need for this helper relation, that is in the linked article, however it makes it very easy for any other situation, including eager loading the count (without being forced to write any joins).

@bashy query scopes are just a way to wrap/abstract some actions you want on your model's query. They do not provide any additional functionalities for you, apart from being able to write eg. ->joinPicturesCount() instead of ->leftJoin(...)->groupBy(..)...

@JarekTkaczyk 2 months ago + didn't understand what they meant by elegant. Thought it was making the query tidy :)

This would only count pics that aren't softly deleted

$categories = Category::select(DB::raw('categories.*, count(*) as `aggregate`'))
    ->leftJoin(DB::raw('(select * from pictures where deleted_at is null) as pics'), 'categories.id', '=', 'pics.category_id')
    ->groupBy('category_id')
    ->orderBy('aggregate', 'desc')
    ->paginate(10);

I would love to share this situation as well, counting a Polymorphic Relation.

In my example, I have an experiences table related to a comments table with a Polymorphic Relation.

The goal here is to paginate through the most discussed experiences and sorting them by the one with the highest count of comments. The count must exclude the softly deleted comments

$experiences = Experience::selectRaw('experiences.*, count(coms.commentable_id) AS `count`')->
            leftJoin(DB::raw('(select * from comments where deleted_at is null and commentable_type="Experience") as coms'),'experiences.id', '=', 'coms.commentable_id')->
            groupBy('experiences.id')->
            orderBy('count','DESC')->
            orderBy('created_at','DESC')->paginate(10);

@bashy does this look elegant ? :p

One of the cleaner queries I've seen :P can you query scopes for that leftJoin?

lool I haven't really used query scopes before, but from what I read in the documentations they just encapsulate a series of queries. So I don't know!

@ KluVerKamp

This works BUT the pagination total and lastPage attributes on the LengthAwarePaginator are incorrect.

$data["tags"] = Tag::selectRaw('tags.*, count(mods.id) AS `count`')
  ->leftJoin(DB::raw('(select * from module_tag) as mods'),'tags.id', '=', 'mods.tag_id')
  ->groupBy('tags.id')
  ->orderBy('count','DESC')
  ->orderBy('created_at','DESC')
  ->paginate(10);

You need a left outer join to take into account no pictures for a given category.

I am facing the same issue but in my case the table has reverse relationship than what specified in the question.

IMO the best way to do this is to grab the information first and then use the sortBy method on the collection to sort by your relationships like so:

$collection = collect($data)->sortBy(function($item) {
    return sprintf('%-12s%s', $item->tier, $item->lastName, $item->firstName);
});

This way you don't have any nasty joins and stick to using the eloquent models and relationships.

This way you don't have any nasty joins and stick to using the eloquent models and relationships.

There is nothing wrong with joins, and if tables are indexed correctly it is much more performant than with the collection method.

I'm running into a similar problem. Like any good DB admin I've got separate tables for my categorical values that are indexed and my main tables (like products) simply use the index values. However, I want to build a table that is alpha-ordered by the category name. I've got as far as returning an stdClass array of values but I would like to retain the Laravel model instances. How to I recast the result as an instance of my model?

Here is the function I have written thus far for returning the array:

function orderProducts(Vendor $vendor)
{
  $sources=DB::table('products')
  ->select('products.*','seafoodCategories.category')
  ->join('seafoodCategories','products.fk_seafoodCategory_id','=','seafoodCategories.id')
  ->where(['products.fk_vendor_id'=>$vendor->id])
  ->orderBy('seafoodCategories.category')
  ->get();
  return $sources;
}

Any thoughts on how to convert this back to a products array?

I cannot test right now, @supermanzer, but would not work like this?

function orderProducts(Vendor $vendor)
{
    return Product::join('seafoodCategories', 'products.fk_seafoodCategory_id', '=', 'seafoodCategories.id')
                                 ->where('products.fk_vendor_id', '=', $vendor->id)
                                 ->orderBy('seafoodCategories.category')
                                 ->select('products.*')
                                 ->get();
}

This way you ensure you get your products and the collection should be ordered as you want. As other info attached, the eloquent has the awesome with function in place.

@alsofronie I was totally in the dark about how you could approach the join and were conditions first and THEN follow up with the SELECT. I'm used to always writing old school SQL and pretty standard query structure. This works beautifully, thank you so much!

For anyone who would just be interested in the count and the possibility to order by that count; whilst paginating:

Category::withCount('pictures')->orderBy('pictures_count', 'desc')->paginate(10); //<3 Tested in 5.3

Hi, I Hope this help

https://gist.github.com/ariefhikam/78168367b879931afdb872a6b83d0a24

sort by relationship attribute can be very trick, but this package can help you https://github.com/fico7489/laravel-eloquent-join