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

Let’s try the bad way. If you are now interested in performance or have a small dataset this can be a quick way of doing it. Since the result is not paginated this can cause problems on big datasets.

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

Subquery

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

Join (fastest)

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

Join