CODEWITHSUNDEEP
mysqllaravel
Chris
Chris

Reputation: 3795

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.

Upvotes: 1

Views: 773

Answers (1)

Paul Spiegel
Paul Spiegel

Reputation: 31772

You need to group by foo.id and order by MAX(bar.created_at):

$foos = Foo::select('foo.*', DB::raw('MAX(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('foo.id')
    ->orderBy('bar_created_at', 'desc')
    ->get();

And you don't need to put the wehere conditions into the join:

$foos = Foo::select('foo.*', DB::raw('MAX(bar.created_at) AS bar_created_at)')
    ->join('bar', 'foo.id', '=', 'bar.foo_id')
    ->where('bar.baz', '=', 1)
    ->where('bar.foobaz', '=', 1);
    ->groupBy('foo.id')
    ->orderBy('bar_created_at', 'desc')
    ->get();

This should generate the folowing query:

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

Upvotes: 1

Related Questions