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.