Answers.Body: <p>OK, here is one solution I've come up with, which seems to work and (I think) avoids 1+ queries:</p>
  
  <pre><code>{% set groups = craft.entries({
      section: 'alert',
      with: [
          'country',
      ]
  }).orderBy('postDate desc').status('live').all() | group(alert =&gt; alert.country[0]) %}
  &lt;ul&gt;
      {% for country, alerts in groups | slice(0, 5) %}
          &lt;li&gt;
              {{ country }} &lt;!-- Cannot use country Entry --&gt;
              &lt;ul&gt;
                  {% for alert in alerts %}
                      &lt;li&gt;{{ alert.title }}&lt;/li&gt;
                  {% endfor %}
              &lt;/ul&gt;
          &lt;/li&gt;
      {% endfor %}
  &lt;/ul&gt;
  </code></pre>
  
  <p>I would prefer being able to handle this entirely in queries though</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-12-04T14:59:05.660Z
Answers.Text: One problem here is that by using the `group` filter you lose access to the underlying Entry and can only make use of the string you group on.
Answers.UserDisplayName: Timmy O'Mahony
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-12-04T14:52:31.427Z
Answers.LastActivityDate: 2019-12-04T14:59:23.897Z
Answers.LastEditDate: 2019-12-04T14:59:23.897Z
Answers.UserDisplayName: Timmy O'Mahony
Answers.Body: <p>It's from the top of my head here so no idea if this is going to work, but my idea is:</p>
  
  <ul>
  <li>Get all active alerts order by postDate (just their ID's to make it faster)</li>
  <li>Retrieve all related countries and take the first 5 (You can do a relatedTo with more than 1 entry.)</li>
  </ul>
  
  <pre><code>{% set alerts = craft.entries({
      section:    'alert',
      orderBy:    'postDate desc',
      status:     'live'
  }).ids() %}
  
  {% set countries = craft.entries({
      section:    'countries',
      relatedTo:  alerts,
      limit:      5
  }).all() %}
  </code></pre>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2019-12-05T09:10:53.063Z
Answers.LastActivityDate: 2019-12-05T09:10:53.063Z
Answers.UserDisplayName: Tom De Smet
Body: <p>I have two Sections on my site: Country and Alert. The Alert section has a relation with Country via an "Entries" field. An Alert represents something that is happening in a particular country for a period of time so it will have a post date and an expiry date. </p>
  
  <p>In my template, I want to show a list of the 5 Countries that have currently active Alerts (ideally ordered by the country with the latest Alert).</p>
  
  <p>I'm not sure how query across relationships in Craft (outside of using the <code>relatedTo</code> query, but that only allows you to related to one entry, not a number of entries)? It seems all relationships are handled in a separate <code>relation</code> table in the DB.</p>
  
  <p>How can I achieve an query such as this within a Craft template?</p>
  
Comments.ContentLicense: CC BY-SA 4.0
Comments.CreationDate: 2019-12-04T14:40:39.303Z
Comments.Text: One option is to create a loop and start to assemble the logic there, but this requires introducing 1-query per iteration which can easily get out of hand. I notice Craft offers Eager-loading https://docs.craftcms.com/v2/templating/eager-loading-elements.html but does this extend to relationships between Sections?
Comments.UserDisplayName: Timmy O'Mahony
Tags: <templating><database><query><queries>
Title: Querying across relationships in a template
ThreadText: # Querying across relationships in a template
  
  (Asked by: username_0 on 2019-12-04)
  
  I have two Sections on my site: Country and Alert. The Alert section has a relation with Country via an "Entries" field. An Alert represents something that is happening in a particular country for a period of time so it will have a post date and an expiry date.
  
  In my template, I want to show a list of the 5 Countries that have currently active Alerts (ideally ordered by the country with the latest Alert).
  
  I'm not sure how query across relationships in Craft (outside of using the `relatedTo` query, but that only allows you to related to one entry, not a number of entries)? It seems all relationships are handled in a separate `relation` table in the DB.
  
  How can I achieve an query such as this within a Craft template?
  
  **Tags:** templating, database, query, queries
  
  > **Comments:**
  > **username_0**: One option is to create a loop and start to assemble the logic there, but this requires introducing 1-query per iteration which can easily get out of hand. I notice Craft offers Eager-loading https://docs.craftcms.com/v2/templating/eager-loading-elements.html but does this extend to relationships between Sections?  
  
  ---
  
  ## 2 answers
  
  ### Answer by username_0 on 2019-12-04. Score: 1 | Accepted answer
  
  OK, here is one solution I've come up with, which seems to work and (I think) avoids 1+ queries:
  
  ```
  {% set groups = craft.entries({
      section: 'alert',
      with: [
          'country',
      ]
  }).orderBy('postDate desc').status('live').all() | group(alert => alert.country[0]) %}
  <ul>
      {% for country, alerts in groups | slice(0, 5) %}
          <li>
              {{ country }} <!-- Cannot use country Entry -->
              <ul>
                  {% for alert in alerts %}
                      <li>{{ alert.title }}</li>
                  {% endfor %}
              </ul>
          </li>
      {% endfor %}
  </ul>
  
  ```
  
  I would prefer being able to handle this entirely in queries though
  
  > **Comments:**
  > **username_0**: One problem here is that by using the `group` filter you lose access to the underlying Entry and can only make use of the string you group on.  
  
  ---
  
  ### Answer by username_1 on 2019-12-05. Score: 1
  
  It's from the top of my head here so no idea if this is going to work, but my idea is:
  
  * Get all active alerts order by postDate (just their ID's to make it faster)
  * Retrieve all related countries and take the first 5 (You can do a relatedTo with more than 1 entry.)
  
  ```
  {% set alerts = craft.entries({
      section:    'alert',
      orderBy:    'postDate desc',
      status:     'live'
  }).ids() %}
  
  {% set countries = craft.entries({
      section:    'countries',
      relatedTo:  alerts,
      limit:      5
  }).all() %}
  
  ```
  
  
  ---
  
  