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

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.

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.

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

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

Well, I found what I wanted.

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