Sort entries by a related entry (lastName) and then paginate

Question

I have two channels books and authors

I need to list all books sorted alphabetically by author. I have this working with the following code:

<ul>
    {# 
    // loop through authors so we can sort books 
    // by author last name
    #}
    {% for author in craft.entries.section('authors').order('lastName').all() %}

        {# find all books related to the author #}
        <ul>
            {% for entry in craft.entries.section('books').relatedTo(author).all() %}
                <li>{{ entry.title }}</li>
            {% endfor %}{# end books for each author #}
        </ul>

    {% endfor %}{# end authors #}
</ul>

This works fine, but I"m not sure how to paginate this. I want to paginate the books which is the nested loop and will likely only contain 2-4 books per author.

If I paginate the authors loop I'll have 5 authors per page but each author will have a different number of books so each page will have different number of books. Not what I want.

If I paginate the book loop it's not going to see all the books only the ones from the author of the current author loop.

However the total number of books is 100+. How do I paginate the total number of book results.

--------------

If it were possible to do the alpha sort based on the related field without nesting for loops that would be ideal? Is this possible?

------------- UPDATE ----------------

add a new field to the books channel where the authors last name is manually added and now using .order('authorLastName') which works but it clunky becaue they have to select the author from the authors channel and add in their last name in a second field.

Answer 1

If I understand correctly, then it may be better to loop through the books rather than the authors, then group by author. I'm not 100% on this syntax but may send you down a good path...

Set the query

{% set query = craft.entries.section('books').order('author.lastName').all()|group('author.lastName') %}

Loop and divide

{% paginate query as pageInfo, pageEntries %}

{% for author, books in pageEntries %}
    <h2>{{ author.lastName }}</h2>
    <ul>
        {% for book in books %}
            <li>{{ book.title }}</li>
        {% endfor %}
    </ul>
{% endfor %}

{% if pageInfo.prevUrl %}<a href="{{ pageInfo.prevUrl }}">Previous Page</a>{% endif %}
{% if pageInfo.nextUrl %}<a href="{{ pageInfo.nextUrl }}">Next Page</a>{% endif %}

Answer 2

You would need one query instead a query within a loop. That would cause potential performance issues too.

Let's work to the answer together because I think the learning process is just as important.

1. What happens when you remove the first query that queries authors, and remove the relatedTo() from the second query?

You probably only have a list of books, just not sorted by author.

2. Would you be able to paginate that query? I think you can work that out.

3. Last step: order the books-query by author.