Order by belongsTo Relationship Column: Eloquent vs Query Builder

If you have a query with the belongsTo relationship and want to order the records by the column from that related table, the orderBy wouldn't work.

Example:

$products = Product::with('category')
    ->orderBy('category.name')
    ->get();

This code would throw an error: "Unknown column category.name in order clause"

To avoid such error, you could switch to using Query Builder with the join() method;

$products = Product::select(['products.*', 'categories.name as category_name'])
    ->join('categories', 'products.category_id', '=', 'categories.id')
    ->orderBy('categories.name')
    ->get();

The downside is more code to write. But this join() way is also faster because it runs ONE query to the database instead of two queries executed by with() during Eager Loading.

Alternative approaches

If you do want to use Eloquent, you may run the orderBy with a sub-select:

$products = Product::with('category')
    ->orderBy(Category::select('name')->whereColumn('categories.id', 'products.category_id'))
    ->get();

Also, if you have a really small amount of data and don't care that much about performance, you can get all the rows from the database and then use sortBy() on the collection.

$products = Product::with('category')
    ->get()
    ->sortBy('category.name');

Notice 1: the method name of Eloquent is called orderBy(), but the collection method is called differently sortBy()

Notice 2: use this sortBy() only if you are sure the DB table will not grow much bigger, otherwise downloading all the records will cause much bigger usage of the server RAM.