Getting data from foreign table if it exists in the parent table

[Amit_95]

I have two tables:

1. Events
2. Speakers

An event can have multiple speakers.

In my events table, I have a Foreign ID column, which looks like this:

The column name for the foreign ID is speakers.

My speakers table looks something like this:

What I'm trying to do:

• Find out what speaker is speaking at an event
• Query the foreign ID column to get the speakers name (and other details).
• Then, using the speaker name from the above step, get details of what event the speaker is speaking at.

This is what I currently have:

{% for row in hubdb_table_rows( events_table ) %}
  {% for speaker in row.speakers %}

    {% set speaker_name = speaker.speaker_name %}
    {% set query = hubdb_table_rows( events_table, '&speakers__contains='~speaker_name ) %}

    {% for item in query %}
      {{ item.date_and_time }}
    {% endfor %}
    
  {% endfor %}
{% endfor %}

The above does not do anything. {{ query }}  returns an empty array.

[alyssamwilie]

@Amit_95 I tested your code and it works fine on my end. Is your first loop returning anything?

{% for row in hubdb_table_rows( events_table ) %}

[Amit_95]

Hmm, weird. Yes, I can confirm the above loop does return data for me.

Here's the return for {{ row }}

{id=53028528158, createdAt=1629189261846, updatedAt=1629796328786, 2='{type=string, value=SEP 19  |  16:35 - 17:00 (CET)}', 3='{type=string, value=<p>Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat.<br><br>Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.</p>}', 4='{type=list, value=[{id=53227212682, name='null'}, {id=53227212844, name='null'}, {id=53234410087, name='null'}, {id=53534802068, name='null'}]}', 6='{type=string, value=Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor}'}

[Amit_95 · Accepted solution]

Managed to solve it!

I could see from my return that the list names are returning null (and my query was filtering based on speaker name). So I adjusted my query to the following:

{% set param = '&speakers__in='~speaker.hs_id %}
{% set query = hubdb_table_rows( speakers_table , param) %}

Although I'm unsure why name is returning null, but, got it working in the end 🙂

[Teun]

Awesome! Happy to hear that you got it working. So you did need the __in query in the end combined with the row ID? Makes sense tho, as the foreign ID stores the ID of the linked row.

[Teun]

Hi @Amit_95 , I see that your foreign ID is a multi select. Could you try refactoring your query like this:

{% set query = hubdb_table_rows( events_table, '&speakers__in='~speaker_name ) %}

Did you manage to query based on the foreign ID? I think you can use the hubdb_table_row function for this, as you allready managed to retrieve a specific speaker, you should also be able to retrieve the ID of this row.

[Amit_95]

Hi @Teun, refractoring my query to your suggestion yeilded the same results. Upon running the for loop, to get the data from the events table, my return is still:

[]

I believe the issue is because:

• My main loop starts off querying the events table.
• I then query the speakers table (via the foreign ID)
• Then, based on the speakers name (from that foreign table, the speakers table), I'm trying to go back into the events table to get data where that speaker name exists.