Help building a filterQuery against a HubDB 'Foreign ID' column type

[jamesatkinetic]

I'm trying to figure out how to filter by a "Foreign ID" column type in HubDB.

For example, how would I say give me all rows in table '2678767' where the Category column (Foreign ID Column Type with id 'category') has the category 'beverage'?

Do I query with equals or contains (Foreign ID column types allow multiple relationships)?

Do I query using the id of the related tables row?

Do I query using the string literal value of the related tables row? If so, which column?

I've been trying 

{% hubdb_table_rows('2678767', 'category=beverage') %} (the value of the Select Column choosen)

{% hubdb_table_rows('2678767', 'category=Beverage') %} (the hs_name)

{% hubdb_table_rows('2678767', 'category=34712196359') %}. (he id of the related row)

... none seem to work!

[Kevin-C · Accepted solution]

Hey @jamesatkinetic 

Try something like this:

{% hubdb_table_rows('2678767', '&category__contains=beverage') %}

The reason for this structure is because hubdb_table_rows builds an api query for you in the background. So to filter the query you append search or query parameters which are documented here.  '&' is the syntax for appeding a query or additional info, 'category' is your column id, '__' syntax to append a filter, and finaly your filter and its parameter.

{% hubdb_table_rows('hubdb-id', '&column-to-query__filter=paramter') %}

[Kevin-C · Accepted solution]

Hey @jamesatkinetic 

Try something like this:

{% hubdb_table_rows('2678767', '&category__contains=beverage') %}

The reason for this structure is because hubdb_table_rows builds an api query for you in the background. So to filter the query you append search or query parameters which are documented here.  '&' is the syntax for appeding a query or additional info, 'category' is your column id, '__' syntax to append a filter, and finaly your filter and its parameter.

{% hubdb_table_rows('hubdb-id', '&column-to-query__filter=paramter') %}

[cooperelias]

Hey @Kevin-C - is there any way to take this a step farther and filter by the value of another column within that linked table? In this example, it only works if "beverage" is the name of the row in the linked table, correct? What if that table contained a column named "type" and you wanted to filter by rows where the category.type was a specific value?

[Kevin-C]

Hey @cooperelias 

I'm not 100% sure I follow the question, but I'll try to break it down a little more.

{% hubdb_table_rows('2678767', '&category__contains=beverage') %}

This says:

In the HubDB table "2678767", return the rows that contain the value "beverage" in column "category".

So if you're search for "type" you just replace the values with your search parameters.

See the generic structure layout out in the accepted answer.

[cooperelias]

@Kevin-C - Yeah I don't think I explained that well, so let me try again.

We have a table with a schedule of classes. The club_id column is a foreign id column that links our locations table. So if I want to filter that to only show rows from the schedule that are at a specific location, this works:

{% hubdb_table_rows('2638738', '&club_id__contains=Location+Name') %}

What I would like to do is filter the rows to return any with a club_id value from a shared area. Area is a column within that separate locations table. I know this doesn't work, but I essentially want to be able to say '&club_id.area=Area+Name'.

[Kevin-C · Accepted solution]

Hey @cooperelias 

I didn't get to spend much time with this article but it seems like the nested for loop maybe what you need.

[rohansdeeson]

You sir are a god

[rohansdeeson]

I am having the same issue is anyone able to help

[sharonlicari]

Hey @jamesatkinetic 

Thank you for sharing this information, I'll tag a few experts that can share their thoughts with you   

Hey @JanetArmstrong @alyssamwilie @Stephanie-OG   any advice you could give to @jamesatkinetic? 

Thanks

Sharon