How to do order by the eloquent query without using join relationship in laravel?

Question

How to order laravel eloquent query using parent model? I mean I have an eloquent query where I want to order the query by its parent without using join relationship? I used whereHas and order by on it, but did not work.

Here is a sample of my code:

$query = Post::whereHas('users')->orderBy('users.created_at')->get();

afaik the `whereHas` adds a `where exists` clause in the where without using joins. you could replace the `->get()` to `->toSql()` to see the generated sql query. alternatively, you could use the [`DB` logging function](https://stackoverflow.com/questions/27753868/how-to-get-the-query-executed-in-laravel-5-dbgetquerylog-returning-empty-ar). — Bagus Tesa, Jun 18 '22 at 06:47

score 2 · Answer 1 · answered Jun 18 '22 at 06:37

If you want to order Post by a column in user you have to do a join in some way unless you sort after you retrieve the result so either:

$query = Post::select('posts.*')
             ->join('users', 'users.id', 'posts.user_id')
             ->orderBy('users.created_at')->get();

Note that whereHas is not needed anymore because the join (which is an inner join by default) will only result in posts that have a user.

Alternatively you can do:

$query = Post::has('users')
    ->with('users')
    ->get()
    ->sortBy(function ($post) { return $post->users->created_at; });

The reason is that eloquent relationships are queried in a separate query from the one that gets the parent model so you can't use relationship columns during that query.

score 1 · Answer 2 · answered Jun 18 '22 at 04:10

1

You can simply orderBy in your Post model.

public function users(){
    return $this->belongsTo(User::class, "user_id")->orderByDesc('created_at');
}

I hope this helps you.

answered Jun 18 '22 at 04:10

Rohullah Muhammadee

301
1
8

score 1 · Accepted Answer · answered Jun 18 '22 at 07:46

I have no clue why you wanted to order Posts based on their User's created_at field. Perhaps, a different angle to the problem is needed - like accessing the Post from User instead.

That being said, an orderBy() can accept a closure as parameter which will create a subquery then, you can pair it with whereRaw() to somewhat circumvent Eloquent and QueryBuilder limitation*.

Post::orderBy(function($q) {
    return $q->from('users')
      ->whereRaw('`users`.id = `posts`.id')
      ->select('created_at');
})
->get();

It should generate the following query:

select *
  from `posts`
  order by (
            select `created_at`
            from `users`
            where `users`.id = `posts`.id
           ) asc

A join might serve you better, but there are many ways to build queries.

*As far as I know, the subquery can't be made to be aware of the parent query fields

score 0 · Answer 4 · answered Jun 18 '22 at 17:01

You can try

Post::query()
  ->has('users')
  ->orderBy(
    User::select('created_at')
        ->whereColumn('id', 'posts.user_id')
        ->orderBy('created_at')
  )
  ->get();

The sql generated would be like

select * from `posts` 
    where exists (select * from `users` where `posts`.`user_id` = `users`.`id`) 
    order by (select `created_at` from `users` where `id` = `posts`.`user_id` order by `created_at` asc) asc

But I guess join would be a simpler approach for this use case.

Laravel Docs - Eloquent - Subquery Ordering

How to do order by the eloquent query without using join relationship in laravel?

4 Answers4