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')

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')

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) {
        ->on('page_contents.page_id', '=', 'pages.id');
    ->orderBy('page_contents.headline', 'desc')

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.