How to construct and IN query with hubdb_table_rows()
SOLVE
I am creating a multilingual page, as such each table contains a "language" single select column. In one table I am referencing another table. Since it is not possible to filter a Foreign ID column type via the language property, I have decided to use a multi-select column type instead. In the multi-select column type I store the same values as I have set as the "hs_path" of the referenced table.
So what I want to do now is construct a query that will allow me to fetch all the referenced rows.
Below is my attempt at doing this. I find the code for building the comma separated list very verbose but I don't know a more elegant solution. Also the "in" operator doesn't seem to work.
{% if dynamic_page_hubdb_row.categories %}
{% set categories = [] %}
{% for category in dynamic_page_hubdb_row.categories %}
{% do labels.append(category.label) %}
{% endfor %}
{% set filter = "hs_path__in=" + labels|join(',') + "&language=" + dynamic_page_hubdb_row.language.name %}
<ul>
{% for row in hubdb_table_rows("categories", filter) %}
<li>{{ row.hs_name }}: {{ row.description }}</li>
{% endfor %}
</ul>
{% endif %}
Feb 21, 202312:43 PM - edited Feb 22, 20234:20 AM
Contributor
How to construct and IN query with hubdb_table_rows()
SOLVE
OK found a better but still not ideal solution.
I now created a separate table "category_translations" and moved all the fields that require translation over there. I also added a Foreign ID column in "categories" that references "category_translations" and I select for each row in "categories" the relevant translation rows in "category_translations".
This way I can reference the "categories" table and use the "id" to build an IN filter and then use "row.foreign_table" to get to "category_translations". Finally I use an if inside HubL to then filter out the relevant language. So fetching a lot of additional data. The main advantage is that at least it is all actual DB references so that no data could be missing (in the above solution using a multi-select there is a chance the relevant entry in the "categories" table is missing).
{% if dynamic_page_hubdb_row.categories %}
{% set ids = [] %}
{% for category in dynamic_page_hubdb_row.categories %}
{% do labels.append(category.hs_id) %}
{% endfor %}
{% set filter = "hs_id__in=" + ids|join(',') %}
<ul>
{% for row in hubdb_table_rows("categories", filter) %}
{% for translation in row.translation %}
{% if translation.language.name == dynamic_page_hubdb_row.language.name %}
<li>{{ translation.hs_name }}: {{ translation.description }}</li>
{% endif %}
{% endfor %}
{% endfor %}
</ul>
{% endif %}
How to construct and IN query with hubdb_table_rows()
SOLVE
Ok, I think I see the issue now.
According to https://developers.hubspot.com/docs/api/cms/hubdb the "in" operator only works for number, select, multi-select fields. Which means my strategy isn't possible since there also doesn't seem to be a way to do an OR filter, correct? So if at all I could do one query per lookup row.
Feb 21, 202312:43 PM - edited Feb 22, 20234:20 AM
Contributor
How to construct and IN query with hubdb_table_rows()
SOLVE
OK found a better but still not ideal solution.
I now created a separate table "category_translations" and moved all the fields that require translation over there. I also added a Foreign ID column in "categories" that references "category_translations" and I select for each row in "categories" the relevant translation rows in "category_translations".
This way I can reference the "categories" table and use the "id" to build an IN filter and then use "row.foreign_table" to get to "category_translations". Finally I use an if inside HubL to then filter out the relevant language. So fetching a lot of additional data. The main advantage is that at least it is all actual DB references so that no data could be missing (in the above solution using a multi-select there is a chance the relevant entry in the "categories" table is missing).
{% if dynamic_page_hubdb_row.categories %}
{% set ids = [] %}
{% for category in dynamic_page_hubdb_row.categories %}
{% do labels.append(category.hs_id) %}
{% endfor %}
{% set filter = "hs_id__in=" + ids|join(',') %}
<ul>
{% for row in hubdb_table_rows("categories", filter) %}
{% for translation in row.translation %}
{% if translation.language.name == dynamic_page_hubdb_row.language.name %}
<li>{{ translation.hs_name }}: {{ translation.description }}</li>
{% endif %}
{% endfor %}
{% endfor %}
</ul>
{% endif %}