Ad

How Do You Order A Laravel Builder By Its One-to-one Relationship?

I have two models, Book and Author. Book belongsTo Author (I've trimmed some of the crud from other parts):

class Book extends Model {
  protected $table = 'books';
  protected $fillable = ['title'];

  public function author(){
    return $this->belongsTo('Author', 'author');
  }
}

class Author extends Model {
  protected $table = 'authors';
  protected $fillable = ['name'];
}

If I want to get all books ordered by their title, I would use this:

Books::with(['author'])->orderBy('title', 'asc')->get();

Is it possible to order those books by the author's name? I've tried many combinations:

Books::with(['author' => function($query){
  $query->orderBy('name', 'asc');
}])->get();

Books::with(['author'])->orderBy('name')->get();

Books::with(['author'])->orderBy('author.name')->get();

Books::with(['author'])->orderBy('authors.name')->get();

But none worked. The first, using the with() query, orders all authors before joining them into the books collection, I think. The others threw 500 errors.

If this were plain MySQL, I'd write something like this:

select * from books join authors on books.author_id = authors.id order by authors.name asc;

Is this possible using Builder/Eloquent in laravel 5.1? Do I need to use a DB query?

Ad

Answer

Enabling eager load by calling with('author') will load authors in a separate query, that's why author's columns are not available and sorting by them does not work. Explicit join is needed.

In order to sort by a relation you need to join your books with their authors:

Book::select('books.*')
->join('authors', 'authors.id', '=', 'books.author_id')
->orderBy('authors.name')
->get();
Ad
source: stackoverflow.com
Ad