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

Amit_95
Member | Platinum Partner

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:

 

Screenshot 2021-08-24 at 10.12.30.png

 

The column name for the foreign ID is speakers.

 

My speakers table looks something like this:

 

Screenshot 2021-08-24 at 10.03.47.png

 

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.

0 Upvotes
6 Replies 6
Teun
Guide | Diamond Partner

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.

 

Did my answer solve your issue? Help the community by marking it as the solution.
Amit_95
Member | Platinum Partner

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.
0 Upvotes
amwilie
Key Advisor | Elite Partner

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

 

border

Alyssa Wilie

Web Developer at LyntonWeb

If this answer solved your question, please mark it as the solution!

Amit_95
Member | Platinum Partner

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

 

0 Upvotes
Amit_95
Member | Platinum Partner

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
Guide | Diamond Partner

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.

Did my answer solve your issue? Help the community by marking it as the solution.