topic How to filter a HubDB query with a column value in CMS Development

How to filter a HubDB query with a column value

subin — Wed, 15 Mar 2017 13:31:05 GMT

Hi developers

I am developing a listing page using HubDB,

Here how i can fetch the rows with popularity "popular" with limit 5.

I have tried some queries but none of them are working, can anyone help me with some queries

Thanks

Re: How to filter a HubDB query with a column value

boulter — Fri, 22 Jan 2021 21:19:53 GMT

{{ hubdb_table_rows(<tableId>, ‘popularity=<optionid>&limit=5’) }}

where <optionid> is the id of the "popular" or "not popular" option.

See https://developers.hubspot.com/docs/cms/hubl/functions#hubdb-table-rows for thorough documentation.

Re: How to filter a HubDB query with a column value

subin — Tue, 09 May 2017 08:48:13 GMT

Thanks @boulter this solved my problem, i have directly used the dropdown value

Thanks

Re: How to filter a HubDB query with a column value

leckerman — Mon, 15 Jan 2018 17:50:42 GMT

How do you determine what the <optionid> is for the "popular" or "not popular" dropdown options in the table?

I'm trying to create a custom module with “Choice options” that allow you to filter and show options on a page based on what is selected in the HubDB table's column. For example, I want all of our upcoming “Essentials” classes to be listed on a page using a custom module. Not sure I am doing this correctly...

I believe the value in the custom module would be something like…

Re: How to filter a HubDB query with a column value

boulter — Tue, 16 Jan 2018 16:13:11 GMT

Sure, you can use the hubdb_table_column hubl tag to get the list of options and then the getOptionByName method to get the option value. See http://designers.hubspot.com/docs/tools/hubdb

Re: How to filter a HubDB query with a column value

leckerman — Tue, 16 Jan 2018 17:47:19 GMT

Can you tell me what I am doing wrong with these custom module values? How/where do I add what other columns in the table I want to be shown based on the course selected? Is this close? Not working as is...

Value 1:

{{ hubdb_table_column (674971, select_course=getOptionByName("Essentials") }}<h3>{{ row.course_name }}</h3><p><strong>Location:</strong> {{ row.class_location }}<br><strong>Date:</strong> {{row.date|datetimeformat('%A, %B %e, %Y') }}</p>{% endfor %}

Value 2:

{{ hubdb_table_column (674971, select_course=getOptionByName("Drawings") }}<h3>{{ row.course_name }}</h3><p><strong>Location:</strong> {{ row.class_location }}<br><strong>Date:</strong> {{row.date|datetimeformat('%A, %B %e, %Y') }}</p>{% endfor %}

Value 3:

{{ hubdb_table_column (674971, select_course=getOptionByName("Composer") }}<h3>{{ row.course_name }}</h3><p><strong>Location:</strong> {{ row.class_location }}<br><strong>Date:</strong> {{row.date|datetimeformat('%A, %B %e, %Y') }}</p>{% endfor %}

Re: How to filter a HubDB query with a column value

boulter — Tue, 16 Jan 2018 18:21:02 GMT

Try something like this:

{% set col = hubdb_table_column (674971, <columnId>) %} 

{% set selected_course = col.getOptionByName("Essentials") %}

{{ selected_course }}

Re: How to filter a HubDB query with a column value

leckerman — Tue, 16 Jan 2018 19:45:50 GMT

Is the <columnId> just the name of the column select_course? I can't find id numbers for the table columns. Tried inserting the following into the custom module value choices and then then name of the widget {{ widget.select_course }} into the "Edit" field. Nothing shows up in preview.

Value 1:
{% set col = hubdb_table_column (674971, select_course) %} {% set select_course = col.getOptionByName("Essentials") %}{{ select_course }}<h3>{{ row.course_name }}</h3><p><strong>Location:</strong> {{ row.class_location }}<br><strong>Date:</strong> {{row.date|datetimeformat('%A, %B %e, %Y') }}</p>

Value 2:
{% set col = hubdb_table_column (674971, select_course) %} {% set select_course = col.getOptionByName("Drawings") %}{{ select_course }}<h3>{{ row.course_name }}</h3><p><strong>Location:</strong> {{ row.class_location }}<br><strong>Date:</strong> {{row.date|datetimeformat('%A, %B %e, %Y') }}</p>

Value 3:
{% set col = hubdb_table_column (674971, select_course) %} {% set select_course = col.getOptionByName("Composer") %}{{ select_course }}<h3>{{ row.course_name }}</h3><p><strong>Location:</strong> {{ row.class_location }}<br><strong>Date:</strong> {{row.date|datetimeformat('%A, %B %e, %Y') }}</p>