Friday, March 17, 2017

How to order a join by date column with Eloquent?

So I have models Foo and Bar. Foo has many Bars and Bar belongs to Foo.

I'm trying to get a collection of Foos ordered by it's newest/latest Bar.

$foos = Foo::select('foo.*', 'bar.id as bar_id', 'bar.created_at AS bar_created_at')
    ->join('bar', function($join) {
        $join->on('foo.id', '=', 'bar.foo_id')
            ->where('bar.baz', '=', 1)
            ->where('bar.foobaz', '=', 1);
    })
    ->groupBy('bar_id')
    ->orderBy('bar_created_at', 'desc')
    ->get();

But when I dd($foos->lists('bar_created_at', 'id')); I see that the dates are not the latest Bar record, they are in fact, the oldest.

Here is the generated SQL:

select `foo`.*, `bar`.`foo_id` as `foo_id`, `bar`.`created_at` as `bar_created_at` from `foo` inner join `bar` on `foo`.`id` = `bar`.`foo_id` and `bar`.`foo` = ? and `bar`.`foobaz` = ? where `foo`.`deleted_at` is null group by `foo_id` order by `bar_created_at` desc

Any help would be greatly appreciated. I am using Laravel 5.0.



via Chris

Advertisement