How to construct and IN query with hubdb_table_rows()

Develop, Build & Integrate CMS Development
1

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 %}
2

Ok, I think I see the issue now.

According to Accounts Dashboard | HubSpot 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.

3

So I guess what would work is the following, it is just horribly inefficient (and verbose)

{% if dynamic_page_hubdb_row.categories %}
 {% for category in dynamic_page_hubdb_row.categories %}
 {% set filter = "hs_path=" + category.label + "&language=" + dynamic_page_hubdb_row.language.name %}
 <ul>
 {% for row in hubdb_table_rows("categories", filter) %}
 <li>{{ row.hs_name }}: {{ row.description }}</li>
 {% endfor %}
 {% endfor %}
 </ul>
{% endif %}
4

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 %}
5

CSV format works.

{% set data = hubdb_table_rows(29602141, "hs_id__in=180689574509,180689574516") %}