Querying across relationships in a template

Question

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?

Answer 1

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

Answer 2

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