<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>

CC BY-SA 4.0
2019-12-04T14:59:05.660Z
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.
Timmy O'Mahony
CC BY-SA 4.0
2019-12-04T14:52:31.427Z
2019-12-04T14:59:23.897Z
2019-12-04T14:59:23.897Z
Timmy O'Mahony
<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>

CC BY-SA 4.0
2019-12-05T09:10:53.063Z
2019-12-05T09:10:53.063Z
Tom De Smet
<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>

CC BY-SA 4.0
2019-12-04T14:40:39.303Z
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?
Timmy O'Mahony
<templating><database><query><queries>
Querying across relationships in a template
# 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]) %}

    {% for country, alerts in groups | slice(0, 5) %}
        
            {{ country }} 
            
                {% for alert in alerts %}
                    {{ alert.title }}
                {% endfor %}
            
        
    {% endfor %}


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() %}


---

