SuperTable queries on fields in fields

Question

I have Event entries which contain (among other things) a SuperTable holding the schedule for that event. Each row of the SuperTable contains (among other things) a field for a Session entry, in a column called sessions. A Session entry contains (among other things) a field for a Person entry, called sessionSpeaker, who is the speaker for that session. I also have a list of (usually) the same speakers in an Entries field outside the SuperTable; this is called eventSpeakerList.

What I’m trying to do is, for each speaker, find the ID of their session so I can output it as part of a hyperlink in a preview grid of speaker cards which points to the session in the detailed agenda. So…

{% set speakers = entry.eventSpeakerList.all() %}
{% for speaker in speakers %}
[something something something]
<a href="#s{{ sessionID }}">
{% endfor %}

The problem is, I can’t figure out how to do the [something something something] to get the ID of a session based on the ID of the speaker. When I say ID here, I meant the Craft ID, not a bespoke ID text field or whatever.

I’ve tried relatedTo() queries on the table, but never get anything back, probably because I didn’t structure the queries correctly. I’ve tried search() queries; again, nothing. Or, in some cases, massive object structures that didn’t contain anything I could figure out how to use. I also tried looping through rows and trying to peer into the fields within fields, but couldn’t figure out how to make that work either. I’ve searched this SE for similar questions, but the posts I found that seemed similar either had answers I couldn’t make work (again, quite possibly due to inexperience), or had no answers at all.

Maybe there’s a really simple way to do this and I can’t see it, or maybe it requires relation queries and I’m too inexperienced to understand how to structure them, or who knows. But I could really use some help. Let me know if you need clarifying details on the above, and I’ll edit them in.

--

EDIT: here’s a screenshot from the admin interface showing the supertable and the fields within fields.

Answer 1

I think I followed this breakdown, but a screenshot of the admin could be helpful as well if this answer isn't accurate...

{% set speakers = entry.eventSpeakerList.all() %}
{% for speaker in speakers %}
    {# Get the session entries for each speaker row #}
    {% for session in speaker.sessions.all() %}
    {# Get the person entries for each session #}
    {% for person in session.sessionSpeaker.all() %}
        <a href="#s{{ person.id }}">
    {% endfor %}
{% endfor %}

{% endfor %}

This is likely the first step to confirm it's getting the value you need. There are a couple of optimizations that we can make depending upon if there can only ever be a single session related to a speaker and if only a single person can be related to a session.

{% set speakers = entry.eventSpeakerList.all() %}
{% for speaker in speakers %}
    {% set session = speaker.sessions.one() %}
    {% set person = session.sessionSpeaker.one() %}
<a href="#s{{ person.id }}">

{% endfor %}

The next step would be to optimize the performance, by taking advantage of Eager Loading.

{% set speakers = entry.eventSpeakerList.with([
    'sessions',
    'sessions.sessionSpeaker',
]).all() %}

Then, that means you are getting arrays back instead of Element Queries. So something like this should work (against assuming only one session and one person is allowed in those respective entries fields)

{% set speakers = entry.eventSpeakerList.with([
    'sessions',
    'sessions.sessionSpeaker',
]).all() %}
{% for speaker in speakers %}
    {% set session = speaker.sessions[0] ?? null %}
    {% set person = session.sessionSpeaker[0] ?? null %}
<a href="#s{{ person.id }}">

{% endfor %}

---

Ok, here's an updated version after seeing the Admin UI, not sure if those field names are exactly right, since I can't tell from that UI:

{# Get all the super table rows and eager load the sessions and speakers #}
{% set scheduleSuperTable = entry.scheduleSuperTable.with([
    'sessions',
    'sessions.sessionSpeaker',
]).all() %}
{# Loop through the rows #}
{% for row in scheduleSuperTable %}
    {{ row.eventDay }}
    {{ row.timeSlot }}
{# Assuming there can only be one session #}
{% set session = row.sessions[0] ?? null %}

{% if session %}
    {{ session.title }}

    {# Loop through all of the speakers in the session #}
    {% for speaker in session.sessionSpeaker %}
        {{ speaker.id }}
        {{ speaker.title }}
    {% endif %}
{% endif %}

{% endfor %}