Order Database queries by Relationship in Laravel

sometimes we need to order the records of a table based on a column from another table.

imagine we have a table called “authors” that has a relationship with a “books” table.

we might need to get books and sort them based on author’s name.

here I will explain ordering eloquent queries based on these relations:

• hasOne

• BelongsTo

• hasMany

hasOne Relationships

when we have a hasOne relationship like this:

//Book.php

class Book extends Model
{
    use HasFactory;


    public function author()
    {
        return $this->belongsTo(Author::class);
    }
}

we want to get the books and order them based on their author’s name

       $books = \App\Models\Book::select('books.*')->join('authors', 'books.author_id', '=', 'authors.id')->
    orderBy('authors.name')->get();

first with select method we get all the columns from the books. by default, all columns from “books” table will be selected.

belongTo Relationship

ordering based on belongsTo relationships is done just like hasOne.

for example a post belongs to a user:

public function post(){
     return $this->belongsTo(User::class);
}

we get posts and sort them by their user’s name.

$posts = \App\Models\Post::select('posts.*')->join('users', 'posts.user_id', '=', 'users.id')->
    orderBy('users.name')->get();

HasMany Relationships

let’s consider we have a hasMany relationship between the “authors” table and “books” table:

  public function books()
    {
        return $this->hasMany(Book::class);
    }

we want to get authors and order the result based on the books’ titles.

we can use this query:

$authors = \App\Models\Author::select('authors.*')
    ->join('books', 'authors.id', '=', 'books.author_id')
    ->orderBy('books.title')
    ->get();

in this query, we select all the columns from the “authors” table using select('authors.*').

then we join it with the books table where author’s id is equal to book’s author_id.

Finally, we order the result based on the book’s title.