# How to construct and IN query with hubdb\_table\_rows()

**URL:** <https://community.hubspot.com/t/how-to-construct-and-in-query-with-hubdb-table-rows/75652>\
**Category:** CMS Development\
**Tags:** hubdb\
**Created:** [February 21, 2023, 4:29pm UTC](https://community.hubspot.com/t/how-to-construct-and-in-query-with-hubdb-table-rows/75652 "2023-02-21T16:29:44Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![lsmith77](https://d10pqhm3lwygmk.cloudfront.net/user_avatar/community.hubspot.com/lsmith77/32/191541_2.png) [@lsmith77](https://community.hubspot.com/u/lsmith77)\
**Post date:** [February 21, 2023, 4:29pm UTC](https://community.hubspot.com/t/how-to-construct-and-in-query-with-hubdb-table-rows/75652/1 "2023-02-21T16:29:44Z")

</div>

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.

```lia-code-sample
{% 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 %}

```

---

<div class="post-metadata">

**Author:** ![lsmith77](https://d10pqhm3lwygmk.cloudfront.net/user_avatar/community.hubspot.com/lsmith77/32/191541_2.png) [@lsmith77](https://community.hubspot.com/u/lsmith77)\
**Post date:** [February 21, 2023, 4:38pm UTC](https://community.hubspot.com/t/how-to-construct-and-in-query-with-hubdb-table-rows/75652/2 "2023-02-21T16:38:55Z")

</div>

Ok, I think I see the issue now.

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

---

<div class="post-metadata">

**Author:** ![lsmith77](https://d10pqhm3lwygmk.cloudfront.net/user_avatar/community.hubspot.com/lsmith77/32/191541_2.png) [@lsmith77](https://community.hubspot.com/u/lsmith77)\
**Post date:** [February 21, 2023, 4:52pm UTC](https://community.hubspot.com/t/how-to-construct-and-in-query-with-hubdb-table-rows/75652/3 "2023-02-21T16:52:22Z")

</div>

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

```lia-code-sample
{% 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 %}

```

---

<div class="post-metadata">

**Author:** ![lsmith77](https://d10pqhm3lwygmk.cloudfront.net/user_avatar/community.hubspot.com/lsmith77/32/191541_2.png) [@lsmith77](https://community.hubspot.com/u/lsmith77)\
**Post date:** [February 21, 2023, 5:43pm UTC](https://community.hubspot.com/t/how-to-construct-and-in-query-with-hubdb-table-rows/75652/4 "2023-02-21T17:43:54Z")

</div>

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).

```lia-code-sample
{% 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 %}

```

---

<div class="post-metadata">

**Author:** ![BR0](https://d10pqhm3lwygmk.cloudfront.net/user_avatar/community.hubspot.com/br0/32/204293_2.png) [@BR0](https://community.hubspot.com/u/BR0)\
**Post date:** [October 29, 2024, 2:00pm UTC](https://community.hubspot.com/t/how-to-construct-and-in-query-with-hubdb-table-rows/75652/5 "2024-10-29T14:00:32Z")

</div>

CSV format works.

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