Help sorting with a BelongsToMany relationship - on name field instead of id

I have a 'books' table and an 'authors' table. There can be multiple authors for a given book so I also have a table called 'authors_to_books' which has fields 'book_id' and 'author_id'.

In user's collections, I'd like to allow sorting by author name but I'm getting a little lost because of the pivot table (I think that's the correct term?). I can find similar questions with answers that lead to being able to sort by the pivot table / BelongsToMany relationship but they seem to be by the author_id on the authors_to_books table, not the author's name - which is on the 'authors' table.

I have these relationships on the 'books' model:

public function authorsToBook(): HasMany
    {
        return $this->hasMany(AuthorsToBook::class);
    }

public function authors(): BelongsToMany
    {
        return $this->belongsToMany(Author::class, ‘authors_to_books’);
    }

So how do I orderBy author name?

Snapey

2 years ago

Level 122

You don't need the authorsToBook function

Your authors can be sorted like;

    public function authors(): BelongsToMany
    {
        return $this->belongsToMany(Author::class, ‘authors_to_books’)->orderBy('name');
    }

adapted to whatever column you want to sort by.

Then you can load sorted authors as simply as

$books = Book::with('authors')->get();

or if you loaded a single book by route binding, to eager load;

$book->load('authors');

If you stick with convention and name your pivot table author_book then you don't need to hint the table in the above relationship

thebookcollector

2 years ago

Level 1

Hm, this isn't working. Do you know how this deals with a situation where there are multiple authors for a given book? For example:

Book A Author 1 - Ben Author 2 - Yager

Book B Author 1 - Carly Author 2 - Frank

Which book will be listed first alphabetically?

Aside from situations like above, this solution isn't even working for books with single authors for some reason. Currently investigating...

Snapey

2 years ago

Level 122

books are unsorted or according to the sort that you apply to books. the example is sorting related authors

You cant sort books by author when there are multiple, This is a simple logic issue, not laravel or eloquent related

thebookcollector

2 years ago

Level 1

@Snapey Ah, I can see now how I was unclear in my original post. Yes, what I'm looking to do is sort Books by Author name...which, as you pointed out, becomes difficult because in some cases there are multiple authors per book.

Is there a way to just use the first author (or really any single author) for a book for purposes of sorting?

Please or to participate in this conversation.