HubDB Filter and Results

[AAM]

Hi. I'm new to the CMS and am trying to create a filtered list from a HubDB table. I've been successful in creating the dropdown for the filter, however when a selection is made, the body of the table stays the same and doesn't show the results from the filter. I'm pretty sure I'm missing code somewhere, but unsure exactly what. Can anyone help? Thank you!

Here is my code: 

<div>
    <form id="form_id" method="get">
        <div>
            <h4>Publication Type: </h4>
            <select name="pubtype" form="form_id" onChange="this.form.submit()">
                <option value="show-all">Show All</option>
                {% set types = hubdb_table_column(5344131, "pubtype").options %} 
              {% for choice in types %} 
              {% set type_list = type_list~choice.id|list%} 
              {% if choice.id == request.query_dict.pubtype %}
                <option selected="selected" value="{{ choice.id }}">{{ choice.name }}</option>
                {% else %}
                <option value="{{ choice.id }}">{{ choice.name }}</option>
                {% endif %} {% endfor %}
            </select>
        </div>
<!-- sets the different query parameters using submitted input for hubdb query -->
{% set queryparam = "" %}
{% if request.query_dict.pubtype in ["1","2","3","4","5","6","7","8","9","10","11","12","13"] %}
{% set queryparam = queryparam ~ "&pubtype="~request.query_dict.pubtype|urlencode %}
{% endif %}
{% if request.query_dict.pubtype == "show-all" %}
{% set queryparam = queryparam %}
{% endif %}

{% set table_id = 5344131 %}
  
    <h3>
      Publications:
      </h3>
        {% for row in hubdb_table_rows(5344131) %}
               <p>
                 {{ row.publication }}
      </p>  
             
        {% endfor %}

[Indra · Accepted solution]

Hi @AAM,

I see there is still a lot of code that is missing. So to really understand the way a queryparam is added, you need to apply it to your hubdb_table_rows(). I recommand checking yout the documentation about HubDB at Getting rows. 

For your code, I have copied your code and made some adjustments to it. This is a working demo if you have the following fields inside your HubDB:

Column type: Text.

Column name: publication

Column type: Select

Column name: pubtype

{# Set your HubDB ID. There is a field (HubDB table) to make the user choose. If it's empty, it will display the default #}
{% set db = module.db || '5344131' %}

<!-- sets the different query parameters using submitted input for hubdb query -->
{% set queryparam = "" %}

{# Check if the query_dict of pubtype contains the existing pubtypes of the HubDB column options and append it to the queryparam #}
{% if request.query_dict.pubtype in types|map('id') %}
  {# Set new queryparam by adding the pubtype #}
  {% set queryparam = queryparam ~ "&pubtype="~request.query_dict.pubtype|urlencode %}
{% endif %}

{# Check if the query_dict of publication is not empty and append it to the queryparam #}
{% if request.query_dict.publication != "" %}
  {# Set new queryparam by adding the publication #}
  {% set queryparam = queryparam~"&publication__icontains="~request.query_dict.publication|urlencode %}
{% endif %}

{# Get all rows of your HubDB and apply your queryparam (filter) if it's set #}
{% set rows = hubdb_table_rows(db, queryparam) %}

<div>
  <form id="form_id" method="get">
    {# Choice filter #}
    <div>
      <h4>Publication Type:</h4>
      <select name="pubtype" form="form_id" onChange="this.form.submit()">
        <option value="show-all">Show All</option>
        {% set types = hubdb_table_column(db, "pubtype").options %} 
        {% for choice in types %} 
          {% set type_list = type_list~choice.id|list%} 
          <option {% if choice.id == request.query_dict.pubtype %}selected="selected"{% endif %} value="{{ choice.id }}">{{ choice.name }}</option>
        {% endfor %}
      </select>
    </div>
    {# Search filter #}
    <div>
      <input name="publication" type="text" id="search-by" class="autocomplete" value="{{ request.query_dict.publication }}" placeholder="Search by Title">
    </div>
    <input id="submit-button" type="submit" value="search">
  </form>
</div>

<h3>
  Publications:
</h3>
{# Loop through the rows that has the queryparam #}
{% for row in rows %}
<p>
  {{ row.publication }}
</p>
{% endfor %}

[AAM]

Thank you, Indra. That helps!

[AAM]

Hi Indra. Thank you for your reply. I have the test module embeded in a template on this page: http://hubspot-developers-v49uvr-21451423.hs-sites.com/hubdb-test

I tried following the real estate tutorial and was able to add a search bar, but the page is still not returning results on the filter or search.

Thanks,

Elise

[Indra]

Hi @AAM,

I see you use the field 'publication' for the search. Is this also your field for your title? Otherwise if won't search correctly. It also looks like the queryparam didn't trigger. So perhaps you haven't added this correctly.

Can you send your Hubl code?

[AAM]

Hi Indra,

Here is a snippet from the HubDB table:

And my hubl code:

<div>
    <form id="form_id" method="get">
        <div>
            <h4>Publication Type: </h4>
            <select name="pubtype" form="form_id" onChange="this.form.submit()">
                <option value="show-all">Show All</option>
                {% set types = hubdb_table_column(5344131, "pubtype").options %} 
              {% for choice in types %} 
              {% set type_list = type_list~choice.id|list%} 
              {% if choice.id == request.query_dict.pubtype %}
                <option selected="selected" value="{{ choice.id }}">{{ choice.name }}</option>
                {% else %}
                <option value="{{ choice.id }}">{{ choice.name }}</option>
                {% endif %} {% endfor %}
            </select>
        </div>
      
    <div>
        <input name="publication" type="text" id="search-by" class="autocomplete" placeholder="Search by Title">
    </div>
        <input id="submit-button" type="submit" value="search">
  </form>
</div>
<!-- sets the different query parameters using submitted input for hubdb query -->
{% set queryparam = "" %}
{% if request.query_dict.pubtype in types|map('5344131') and request.query_dict.publication == "" %}
{% set queryparam = queryparam ~ "&pubtype="~request.query_dict.pubtype|urlencode %}
{% endif %}
{% if request.query_dict.pubtype in types|map('5344131') and request.query_dict.publication != "" %}
    {% set queryparam = queryparam~"&type="~request.query_dict.pubtype|urlencode~"&publication__icontains="~request.query_dict.publication|urlencode %}
{% endif %}
{% if request.query_dict.pubtype == "show-all" and request.query_dict.publication != ""%}
{% set queryparam = queryparam~"publication__icontains="~request.query_dict.publication|urlencode %}
{% endif %}

{% set table_id = 5344131 %}
  
    <h3>
      Publications:
      </h3>
        {% for row in hubdb_table_rows(5344131) %}
               <p>
                 {{ row.publication }}
      </p>  
             
        {% endfor %}

Thank you

[Indra]

Hi @AAM,

Please try replace |map('5344131') for |map('id') in your hubl code.

types|maps('id') stands for hubdb_table_column(5344131, "pubtype").options|map('id')

The map function will only get the ID of your options. So it will give someting back similar as ["1","2","3","4","5","6","7","8","9","10","11","12","13"] Only, when editing the 'option', some values will get removed. So this is a more safe way to check on.

[AAM]

Thank you for the clarification. Here is what I have now:

{% set queryparam = "" %}
{% if request.query_dict.pubtype in types|map('id') and request.query_dict.publication == "" %}
{% set queryparam = queryparam ~ "&pubtype="~request.query_dict.pubtype|urlencode %}
{% endif %}
{% if request.query_dict.pubtype in types|map('id') and request.query_dict.publication != "" %}
    {% set queryparam = queryparam~"&type="~request.query_dict.pubtype|urlencode~"&publication__icontains="~request.query_dict.publication|urlencode %}
{% endif %}
{% if request.query_dict.pubtype == "show-all" and request.query_dict.publication != ""%}
{% set queryparam = queryparam~"publication__icontains="~request.query_dict.publication|urlencode %}
{% endif %}

Still doesn't seem to be working out for me, though.

[Indra]

Hi @AAM,

How are you testing your page? To test a HubDB filter you need to publish the module onto a published page.

Do you have a sample link for your module/page that you can share?

One tip:
You can replace ["1","2","3","4","5","6","7","8","9","10","11","12","13"] for types|map('id') to make it dynamic. 

HubSpot has a great tutorial about setting up filter for HubDB.

I would recommend checking out this tutorial: Let's Build A Real Estate Listing, using HubDB