Laravel Eloquent sort by relation table column

Laravel Eloquent sort by relation table column

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

Why Eloquent Sorts Fail on Related Columns

You have a shop_products table and a related shop_products_options table linked by product_id. You want to fetch products, eager load their single option record, and sort the final result set so that pinned items appear first. The code you wrote looks syntactically correct:

$products = Shop\Product::with([
    'options' => function ($query) {
        $query->orderBy('pinned', 'desc');
    }
])->paginate(5);

But the query Laravel generates ignores your ordering intent. It produces this SQL:

SELECT * FROM `shop_products_options`
WHERE `shop_products_options`.`product_id` IN ('8','9','10','11','12')
ORDER BY `pinned` DESC;

That query only returns option rows, not product rows. The root cause is how Eloquent handles sorting when a relation is ordered inside the with() clause: the order modifier applies to the related table's cursor, not the main query that fetches the parent collection. Pagination compounds this because Laravel must construct one query to select parents and then apply eager loading separately.

Why the current approach fails

When you call ->paginate(), Eloquent builds a primary query against the shop_products table. It then runs a separate query (or queries) for each relation in the with() array. The closure you pass to 'options' => ... modifies that secondary query, which is why you see results limited to shop_products_options. The primary SELECT * FROM shop_products never receives an ORDER BY clause derived from the option table.

In short: eager-loading scopes do not propagate order constraints back to the main collection query. That's by design, and it becomes especially visible with pagination.

Correct patterns for sorting with relations

You have three practical paths, depending on your needs.

1. Sort on the parent side only

If your business logic requires sorting products based solely on fields that exist in shop_products, place the order clause on the main query:

$products = Shop\Product::with(['options' => function ($query) {
    $query->orderBy('pinned', 'desc');
}])
    ->orderBy('created_at', 'desc') // or any parent column you care about
    ->paginate(5);

Here, the orderBy on created_at applies to the main query. The eager-loading closure still runs and ensures each product has its latest option row attached, but it does not affect the sort order of the result set. This is safe when the parent column is sufficient for your ranking needs.

2. Sort by a relation field using explicit joins

If you truly need to rank products by pinned from the options table, you must join the tables and order on the joined column in the main query:

use Illuminate\Support\Facades\DB;

$products = Shop\Product::with('options')
    ->join('shop_products_options', function ($join) {
        $join->on('shop_products.id', '=', 'shop_products_options.product_id');
    })
    ->groupBy('shop_products.id')
    ->orderBy('shop_products_options.pinned', 'desc')
    ->paginate(5);

This query explicitly joins shop_products_options, groups by the parent primary key to avoid duplication, and orders on the related column. The eager-loading closure is no longer needed for ordering; you can still use it if you need additional filtering or scope logic on the relation. Be careful with groupBy when your join introduces multiple rows per parent—only one row per group survives after grouping, which may not represent a specific option record unless that's your intent.

3. Fetch and sort in PHP

When you need complex ranking (e.g., descending pinned, then created_at) and want to avoid joins or grouping pitfalls, fetch unsorted products with eager loading and sort the collection in memory:

$products = Shop\Product::with('options')
    ->get() // or paginate without order constraints if size is small
    ->sortByDesc(fn ($p) => $p->options?->pinned ?? 0);

If you're using pagination with large datasets, this approach trades CPU for SQL complexity. For typical e-commerce sites where the product count per page is modest (e.g., 20–50), in-memory sorting is often acceptable and keeps your queries simpler.

Common pitfalls to avoid

  • Assuming eager-loading scopes affect ordering: They do not. Scopes inside with() only constrain or order the related query, not the main one.
  • Using paginate() on a joined query without groupBy: Without grouping by the parent primary key, you risk inflated counts and duplicate results.
  • Relying on ->first() with eager-loading scopes for ordering: Even non-paginated single-record queries can be affected if the main table isn't explicitly ordered; always order the primary query when it matters.

If your team maintains many custom scopes and you find yourself repeating join-based patterns, consider encapsulating the logic in a scope method or a dedicated service class. This keeps controllers clean and makes trade-offs explicit.

For more advanced querying needs, including relation-aware sorting across multiple tables, LaravelCompany offers resources that walk through these patterns with real-world examples tailored to shop systems.

Our network