CMS Development

lsmith77
Contributor

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 %}
0 Upvotes
1 Accepted solution
lsmith77
Solution
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 %}

 

 

View solution in original post

0 Upvotes
4 Replies 4
BR0
Member

How to construct and IN query with hubdb_table_rows()

SOLVE

CSV format works.

{% set data = hubdb_table_rows(29602141, "hs_id__in=180689574509,180689574516") %}
0 Upvotes
lsmith77
Contributor

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.

0 Upvotes
lsmith77
Contributor

How to construct and IN query with hubdb_table_rows()

SOLVE

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

 

0 Upvotes
lsmith77
Solution
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 %}

 

 

0 Upvotes