Laravel Eloquent order by using relation data

Question

I have the following 2 tables:

Cars
----
id | created_at

and

Repaired
id | cars_id | updated_at

I want to display all cars, ordered by created_at desc, BUT also ordered by updated_at from the Repaired pivot table. Repaired can have many entries of specific cars_id.

So if carA is created_at 2017-02-20, and carB is created_at 2016-01-10, but the updated_at in Repaired for carB is on 2017-02-22, i want to display carB BEFORE carA, because the relation is "newer".

I also don't want do load the data from the relation table.

I tried the follwing:

$cars = $model->load(['repaired' => function($query) {
    return $query->orderBy('updated_at', 'desc');
}])->orderBy('created_at', 'desc');

and

$model->join('repaired', 'repaired.cars_id', '=', 'cars.id')
      ->orderBy('repaired.updated_at', 'desc')
      ->orderBy('cars.created_at', 'desc')
      ->select('cars.*');

^ this query return all items from the Repaired table, even if the updated_at field is NULL. And i can't group them.

Have you tried something so far?

Sebastian
– Sebastian

2017-02-22 09:54:42 +00:00
Commented Feb 22, 2017 at 9:54 — Sebastian
– Sebastian, Commented Feb 22, 2017 at 9:54

lshas · Accepted Answer · 2017-02-22 10:05:43Z

3

You can use the touch parent timestamps feature in Eloquent, to update the updated_at for the car, every time Repaired is updated.

In your Repaired model just add this line:

protected $touches = ['car'];

This is assuming your relation method is named car() of course.

Then you can do this:

Cars::orderBy('updated_at');

Every time a row in the Repaired table is added or updated, the car specified in the car relation will have its updated_at set to the same time. That way you can order by it.

answered Feb 22, 2017 at 10:05

lshas

1,7211 gold badge20 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Md Azizur Rahman · Accepted Answer · 2020-02-09 18:10:18Z

0

 $popular_posts = Post::withCount('favorite_to_users')
            ->orderBy('view_count', 'desc')
            ->orderBy('favorite_to_users_count', 'desc')
            ->take(5)->get();

answered Feb 9, 2020 at 18:10

Md Azizur Rahman

3753 silver badges8 bronze badges

Collectives™ on Stack Overflow

Laravel Eloquent order by using relation data

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related