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

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();

CodePudding user response：

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.

CodePudding user response：

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

CodePudding user response：

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.