Answers.Body: <p>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...</p>
  
  <p>Set the query</p>
  
  <pre><code>{% set query = craft.entries.section('books').order('author.lastName').all()|group('author.lastName') %}
  </code></pre>
  
  <p>Loop and divide </p>
  
  <pre><code>{% paginate query as pageInfo, pageEntries %}
  
  {% for author, books in pageEntries %}
      &lt;h2&gt;{{ author.lastName }}&lt;/h2&gt;
      &lt;ul&gt;
          {% for book in books %}
              &lt;li&gt;{{ book.title }}&lt;/li&gt;
          {% endfor %}
      &lt;/ul&gt;
  {% endfor %}
  
  {% if pageInfo.prevUrl %}&lt;a href="{{ pageInfo.prevUrl }}"&gt;Previous Page&lt;/a&gt;{% endif %}
  {% if pageInfo.nextUrl %}&lt;a href="{{ pageInfo.nextUrl }}"&gt;Next Page&lt;/a&gt;{% endif %}
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-11-22T18:37:04.870Z
Answers.Text: Unfortunately that results in database errors.
Answers.UserDisplayName: CreateSean
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-11-23T20:10:34.163Z
Answers.Text: What’s the error?
Answers.UserDisplayName: dmatthams
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-11-25T14:06:17.597Z
Answers.Text: It's a database exception - have pasted full error here: https://pastebin.com/hfGqczD0
Answers.UserDisplayName: CreateSean
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-11-22T11:23:25.083Z
Answers.LastActivityDate: 2019-11-22T11:23:25.083Z
Answers.UserDisplayName: dmatthams
Answers.Body: <p>You would need one query instead a query within a loop. That would cause potential performance issues too.</p>
  
  <p>Let's work to the answer together because I think the learning process is just as important. </p>
  
  <ol>
  <li>What happens when you remove the first query that queries authors, and remove the relatedTo() from the second query?</li>
  </ol>
  
  <p>You probably only have a list of books, just not sorted by author.</p>
  
  <ol start="2">
  <li><p>Would you be able to paginate that query? I think you can work that out.</p></li>
  <li><p>Last step: order the books-query by author.</p></li>
  </ol>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-11-21T20:57:59.640Z
Answers.Text: Except you can't order the books query by the author entry field and then by the last name inside the author entry.
Answers.UserDisplayName: CreateSean
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-11-21T20:52:20.527Z
Answers.LastActivityDate: 2019-11-21T20:52:20.527Z
Answers.UserDisplayName: Niek
Body: <p>I have two channels <strong>books</strong> and <strong>authors</strong></p>
  
  <p>I need to list all books sorted alphabetically by author. I have this working with the following code:</p>
  
  <pre><code>&lt;ul&gt;
      {# 
      // 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 #}
          &lt;ul&gt;
              {% for entry in craft.entries.section('books').relatedTo(author).all() %}
                  &lt;li&gt;{{ entry.title }}&lt;/li&gt;
              {% endfor %}{# end books for each author #}
          &lt;/ul&gt;
  
      {% endfor %}{# end authors #}
  &lt;/ul&gt;
  </code></pre>
  
  <p>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. </p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>However the total number of books is 100+. How do I paginate the total number of book results.</p>
  
  <h2>--------------</h2>
  
  <p>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?</p>
  
  <p><strong>------------- UPDATE ----------------</strong></p>
  
  <p>add a new field to the books channel where the authors last name is manually added and now using <code>.order('authorLastName')</code> 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.</p>
  
Tags: <relations><pagination><relatedto>
Title: Sort entries by a related entry (lastName) and then paginate
ThreadText: # Sort entries by a related entry (lastName) and then paginate
  
  (Asked by: username_0 on 2019-11-21)
  
  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.
  
  **Tags:** relations, pagination, relatedto
  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_1 on 2019-11-22. Score: 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 %}
  
  ```
  
  > **Comments:**
  > **username_0**: Unfortunately that results in database errors.  
  > **username_1**: What’s the error?  
  > **username_0**: It's a database exception - have pasted full error here: https://pastebin.com/hfGqczD0  
  
  ---
  
  ### Answer by username_2 on 2019-11-21. Score: 0
  
  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.
  
  > **Comments:**
  > **username_0**: Except you can't order the books query by the author entry field and then by the last name inside the author entry.  
  
  ---
  
  