HubDB - Select with AND/OR

[diavolino]

hi all

it would be great if i could find help with my little project in hubdb here.  - greetings from switzerland and happy new year.

i query a hubdb table and do not understand how i can check 2 fields in it directly against a value-set.

here is what i want to do ...

{% set qPar = "" %}

{% set qPar = qPar ~ '&onoff==1' %}

- and - [[

{% set qPar = qPar ~ '&application_tags__contains=' ~ topic %}

- or -

{% set qPar = qPar ~ '&product_tags__contains=' ~ topic %}

]] - and - 

{% set qPar = qPar ~ '&article_year=' ~ topicYear %}

{% for row in hubdb_table_rows(ACTIVE_HUB_DB, qPar) %}

   {{ row.title }}

{% endfor %}

so the point is that i want to query if "topic" is in one of the two fields "application_tags" or "product_tags". 

i would appreciate some help here as my brain gets slowly to melt.

thanks

andy

[boulter]

Hi Andy.

HubDB filters always AND together the parameters. There's currently no way to do an OR. You can work around this by structuring your data differently or you can make two queries and combine the results, making sure to remove any duplicates by id. 

I hope that helps!

[BenSBM]

@boulter - has there been any updates to HubDB to use OR filters? Also, is there anywhere with more in-depth documentation on what is available to use with HubDB? Currently I am unable to find anything that goes into any real detail.

Thanks.

[boulter]

Hi Ben. There's no immediate plans to add ORs to HubDB queries. 

The authoritative documentation is at http://developers.hubspot.com/docs/methods/hubdb/get_table_rows

[diavolino]

hi boulter

thank you for your reply. sad to hear that "or" will not work directly.

how would i make this 2 queries and filter the result by id? could you give me a sample? that would be so great. 🙂

cheers

andy

[boulter]

I haven't tested this, but something like it should work.

{% set queryBase = 'onoff==1' %}
{% set appQuery = queryBase ~ '&application_tags__contains=' ~ topic %}
{% set productQuery = queryBase ~ '&product_tags__contains=' ~ topic %}
{% set appRows = hubdb_table_rows(ACTIVE_HUB_DB, appQuery) %}
{% set productRows = hubdb_table_rows(ACTIVE_HUB_DB, productQuery) %}
{% set appIds = appRows|map(attribute='hs_id') %}
{% for row in appRows %}
  {{ row.title }}
{% endif %}
{% for row in productRows %}
  {% if row.id not in appIds %}
    {{ row.title }}
  {% endif %}
{% endfor %} 

You might be able to combine the result sets first and iterate over the rows just once with some more advanced filtering.