Introduction
Eloquent ORM is an elegant query builder for PHP and Laravel developers, providing a fluent interface to interact with databases. One common operation when dealing with databases is sorting records based on one or more columns. In this tutorial, we will explore how to sort records by multiple columns using Eloquent in Laravel, with plenty of code examples to clarify the concepts.
Getting Started
Sorting is a critical aspect of data presentation for end-users. It often reflects business priorities and user preferences. Eloquent makes sorting data simple, yet allows for complex sorting criteria to be applied with minimal code. To demonstrate this, we will use a fictitious posts table having columns: title, published_at, and views.
Basic Sorting with Eloquent
First, we will explore how to sort the records by a single column. To sort the posts by their published_at column:
$sortedPosts = Post::orderBy('published_at', 'desc')->get();
This code will fetch posts sorted in descending order of their publication date.
Complex Sorting by Multiple Columns
To sort records by more than one column, you chain multiple orderBy clauses:
$sortedPosts = Post::orderBy('views', 'desc')
->orderBy('published_at', 'desc')
->get();
Here, posts are first sorted by view counts in descending order and then by their publication dates.
Sorting Using Raw Expressions
Sometimes you may want to sort data using a raw database expression. With Eloquent, you can do this efficiently:
$sortedPosts = Post::orderByRaw('ISNULL(published_at), published_at DESC')
->get();
With orderByRaw, this query will sort the posts putting those with a null published_at value first.
Custom Sorting Logic
In some cases, you need more control over the sorting logic, for which you can use the usort() or collection’s sortBy() methods. Let’s say we want to prioritize sorting by status where posts with status 'published' come before 'draft':
$postsCollection = Post::all();
$sortedPosts = $postsCollection->sortBy(function ($post) {
return $post->status === 'published' ? 0 : 1;
});
Collection’s sortBy() leaves you with a Collection instance instead of a Builder instance, offering fluency to work further on the result set.
Ordering with Related Models
Often, related models also play a role in how we want to sort our main model. For instance, if posts have an author, and you want to sort them by the name of the author as well, you might use joins or nested Eloquent relationships, like so:
$sortedPosts = Post::select('posts.*')
->join('users', 'posts.user_id', '=', 'users.id')
->orderBy('users.name', 'asc')
->orderBy('posts.published_at', 'desc')
->get();
This join will sort our posts first by the authors’ names and then by the publication dates.
Sorting with Scope Queries
A neat way to reuse specific sorting logic is to encapsulate it in query scopes. For example:
class Post extends Model
{
//...
public function scopePopular($query)
{
return $query->orderBy('views', 'desc');
}
public function scopeRecent($query)
{
return $query->orderBy('published_at', 'desc');
}
}
// Usage
$sortedPosts = Post::popular()->recent()->get();
This allows for the elegant composition of sorting queries by chaining scopes.
Advanced Sorting Techniques
For advanced sorting like nested ordering with conditional clauses or database-specific features, you should refer to raw queries but ensure that these are safe from SQL injection.
Sorting with Pagination
When sorting large datasets, it’s essential to paginate results to improve performance. Here’s how you can sort and paginate:
$sortedPosts = Post::orderBy('published_at', 'desc')->paginate(10);
This will sort the posts by published_at while splitting the results into pages containing 10 records each.
Conclusion
In conclusion, Eloquent offers various ways to sort records by multiple columns, each applicable to different scenarios. The key to powerful database queries is understanding the appropriate methods for a given context and wielding them to create clean, maintainable, and efficient code.
