CODEWITHSUNDEEP
laravelsql-order-by
nutzt
nutzt

Reputation: 2923

Laravel Eloquent order by using relation data

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.

Upvotes: 1

Views: 318

Answers (2)

Md Azizur Rahman
Md Azizur Rahman

Reputation: 375

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

Upvotes: 0

lshas
lshas

Reputation: 1731

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.

Upvotes: 3

Related Questions