July, 2022

Eloquent, order by relationship and eager loading

This question comes up now and again. How to order a query results on a one-to-one relationship in Eloquent.

Since with() is creating another query Eloquent does not know about it’s contents. So this will not work.

    $pages = Page::with('content')
        ->published()
        ->orderBy('content.headline')
        ->paginate(20);

Sort the collection

If you’re looking to optimize performance or have a relatively small dataset, you can use the following code to quickly retrieve your data. However, be aware that this method is not suitable for large datasets because the results are not paginated, which can lead to problems.

    $pages = Page::with('contents')
        ->get()
        ->sortBy('contents.headline');

This code uses the Eloquent with method to eager load the related contents model, and then sorts the resulting collection by the headline attribute of the contents model. Keep in mind that the get method retrieves all records from the pages table, so if your dataset is too large, you may run into memory issues. In that case, consider using pagination or a more optimized query to retrieve your data.

Join (fastest)

    $pages = Page::select('pages.*')
    ->join('page_contents', function($join) {
      $join
        ->whereNotNull('published_at')
        ->on('page_contents.page_id', '=', 'pages.id');
    })
    ->orderBy('page_contents.headline', 'desc')
    ->paginate(10);

join('page_contents', function($join) {...}): Joins the page_contents table to the pages table, with a callback function that applies a whereNotNull clause to the query that retrieves the related PageContent models. This filters out PageContent models that do not have a published_at value. The on method specifies the join condition, which matches the page_id attribute of the PageContent model to the id attribute of the Page model.
orderBy('page_contents.headline', 'desc'): Sorts the resulting collection by the headline attribute of the related PageContent model in descending order.

Overall, this query retrieves a paginated list of Page models that have at least one published PageContent model related to them, sorted by the headline attribute of the related PageContent model in descending order. The resulting collection is also limited to display 10 items per page.

← → Top