Managing Multiple Filters in HubDB

[ckingg]

I might be overthinking/overlooking here, but I have 4 filters I'm using to filter my HubDB table. Country, Region, Partner Type, and Partner Tier.

It seems very excessive to create a query for every possible combination of filters a user could selected using these filters.

For example, a query for all filters selected, a query for only the Country and Region selected, a query for only the Country and Partner Type selected, a query for only the Partner Type, Country and Region selected, and so on.

Is there a concise way to handle this?

For reference, here's what I started to do and relized how excessive this could turn out. 

<!-- ##############################
Query Variables
############################## -->

{% set queryparam = "" %}

<!-- ##############################
Partner Type Variables
############################## -->

{% set partnerType = request.query_dict.partner_type in ["1", "2", "3", "4"] %}
{% set showAllPartnerType = request.query_dict.partner_type == "show-all" %}
{% set partnerTypeQuery = queryparam ~ "&partner_type="~request.query_dict.partner_type|urlencode %}

<!-- ##############################
Country/Multi-Select Variables
############################## -->

{% set country = request.query_dict.partner_country in ["1", "2", "3", "4", "5"] %}
{% set showAllCountry = request.query_dict.partner_country == "show-all" %}
{% set partnerCountryQuery = queryparam ~ "&partner_country__in="~request.query_dict.partner_country|urlencode %}

<!-- ##############################
Partner Region Variables
############################## -->

{% set region = request.query_dict.partner_region in ["1", "2", "3"] %}
{% set showAllRegion = request.query_dict.partner_region == "show-all" %}
{% set partnerRegionQuery = queryparam ~ "&partner_region="~request.query_dict.partner_region|urlencode  %}

<!-- ##############################
Partner Tier Variables
############################## -->

{% set tier = request.query_dict.partner_tier in ["1", "2"] %}
{% set showAllTier = request.query_dict.partner_tier == "show-all" %}
{% set partnerTierQuery = queryparam ~ "&partner_tier="~request.query_dict.partner_tier|urlencode  %}

<!-- ##############################
Filter Queries
############################## -->
<!-- Partner Type Only -->

{% if partnerType %}
    {% set queryparam = partnerTypeQuery %}
{% endif %}

<!-- Region Only -->
{% if region %}
    {% set queryparam = partnerRegionQuery %}
{% endif %}

<!-- Tier Only -->
{% if tier %}
    {% set queryparam = partnerTierQuery %}
{% endif %}

<!-- Country Only -->
{% if country %}
    {% set queryparam = partnerCountryQuery %}
{% endif %}


<!-- Partner Type + Partner Region -->
{% if partnerType and region %}
    {% set queryparam = queryparam ~ partnerTypeQuery~partnerRegionQuery %}
{% endif %}

<!-- Partner Type + Partner Tier -->
{% if partnerType and tier %}
    {% set queryparam = queryparam ~ partnerTypeQuery~partnerTierQuery %}
{% endif %}

<!-- Partner Type + Partner Country -->
{% if partnerType and country %}
    {% set queryparam = queryparam ~ partnerTypeQuery~partnerCountryQuery %}
{% endif %}

<!-- Partner Region + Partner Tier -->
{% if region and tier %}
    {% set queryparam = queryparam ~ partnerRegionQuery~partnerTierQuery %}
{% endif %}

<!-- Partner Region + Partner Country -->
{% if region and country %}
    {% set queryparam = queryparam ~ partnerRegionQuery~partnerCountryQuery %}
{% endif %}

<!-- Partner Tier + Partner Country -->
{% if tier and country %}
    {% set queryparam = queryparam ~ partnerRegionQuery~partnerCountryQuery %}
{% endif %}

<!-- Partner Region + Partner Tier + Partner Type -->
{% if region and tier and partnerType %}
    {% set queryparam = queryparam ~ partnerRegionQuery~partnerTierQuery~partnerTypeQuery %}
{% endif %}

I thought I might be able to get away with this but it doesn't retrun anything: 

{% if region or tier or partnerType or country %}
    {% set queryparam = queryparam ~ partnerRegionQuery~partnerTierQuery~partnerTypeQuery~partnerCountryQuery %}
{% endif %}

[dennisedson]

@Anton , @amwilie , do you have some ideas for @ckingg ? 🙏

[ckingg · Accepted solution]

@dennisedson thanks for the bump here. I think I got this sorted out with some help from the HubSpot Slack Room 

Here's the code in case any one else is having trouble. This also allows for filtering with checkboxes. 

{% set hubdb_id = YOUR TABLE ID %}
{% if request.query_dict.partner_country != '' %}
  {% set selected_countries = [] %}
  {% for item in request.query|split('&') %}
    {% set keyValue = item|split('=') %}
    {% if keyValue[0] == 'partner_country' %}
      {% do selected_countries.append(keyValue[1]) %}
    {% endif %}
  {% endfor %}
{% endif %}


    <div style="text-align: center; margin:0 auto;">
        <form id="form_id" method="get">
          <div>
            <h4>Partner Country</h4>
              {% set countries = hubdb_table_column(hubdb_id, "partner_country").options %}
              {% for choice in countries %}
                {% if choice.id in selected_countries %}
                  <input type="checkbox" id="{{ choice.id }}" name="partner_country" value="{{ choice.id }}" onChange="this.form.submit()" checked>
                  <label for="{{ choice.name }}">  {{ choice.name }}</label><br>
                {% else %}
                  <input type="checkbox" id="{{ choice.id }}" name="partner_country" value="{{ choice.id }}" onChange="this.form.submit()">
                  <label for="{{ choice.name }}">  {{ choice.name }}</label><br>
                {% endif %}
              {% endfor %}
          </div>
          <div>
            <h4> Partner Region</h4>
            <select name="partner_region" form="form_id" onChange="this.form.submit()">
              <option value="" disabled>- Partner Region -</option>
              <option value="show-all-destinations" {{ 'selected="selected"' if request.query_dict.partner_region == 'show-all-regions' }} >Show All</option>
              {% set regions = hubdb_table_column(hubdb_id, "partner_region").options %}
              {% for choice in regions %}
                {% set region_list =region_list~choice.id|list %}
                {% if choice.id == request.query_dict.partner_region %}
                  <option selected="selected" value="{{ choice.id }}">{{ choice.name }}</option>
                {% else %}
              <option value="{{ choice.id }}">{{ choice.name }}</option>
              {% endif %}
              {% endfor %}
            </select>
          </div>
          <div>
            <h4>Partner Type</h4>
            <select name="partner_type" form="form_id" onChange="this.form.submit()">
              <option value="" disabled>- Partner Type -</option>
              <option value="show-all-destinations" {{ 'selected="selected"' if request.query_dict.partner_type == 'show-all-types' }} >Show All</option>
              {% set types = hubdb_table_column(hubdb_id, "partner_type").options %}
              {% for choice in types %}
                {% set type_list = type_list~choice.id|list %}
                {% if choice.id == request.query_dict.partner_type %}
                  <option selected="selected" value="{{ choice.id }}">{{ choice.name }}</option>
                {% else %}
                  <option value="{{ choice.id }}">{{ choice.name }}</option>
                {% endif %}
              {% endfor %}
            </select>
          </div>
          <div>
            <h4>Partner Tier</h4>
            <select name="partner_tier" form="form_id" onChange="this.form.submit()" >
              <option value="" disabled>- Partner Tier -</option>
              <option value="show-all-destinations" {{ 'selected="selected"' if request.query_dict.partner_tier == 'show-all-tiers' }} >Show All</option>
              {% set tiers = hubdb_table_column(hubdb_id, "partner_tier").options %}
              {% for choice in tiers %}
                {% set tier_list = tier_list~choice.id|list %}
                {% if choice.id == request.query_dict.partner_tier %}
                  <option selected="selected" value="{{ choice.id }}">{{ choice.name }}</option>
                {% else %}
                  <option value="{{ choice.id }}">{{ choice.name }}</option>
                {% endif %}
              {% endfor %}
            </select>
          </div>
          <div style="margin-top: 20px">
            <input id="submit-button" type="submit" value="Enter">
            <div style="margin-top: 20px">
              <a href="/test-partner-locator-page">Reset Filters</a>
            </div>
          </div>
        </form>
      </div>

      
     
       
      {% set queryparam = "" %}
      
      {% if request.query_dict.partner_country in selected_countries %}
        {% set queryparam = queryparam ~ "&partner_country__in="~selected_countries|join(',')|urlencode %}
      {% endif %}

      {% if request.query_dict.partner_type in types|map('id','') %}
        {% set queryparam = queryparam ~ "&partner_type="~request.query_dict.partner_type|urlencode %}
      {% endif %}

      {% if request.query_dict.partner_tier in tiers|map('id','') %}
        {% set queryparam = queryparam ~ "&partner_tier="~request.query_dict.partner_tier|urlencode %}
      {% endif %}

      {% if request.query_dict.partner_region in regions|map('id','') %}
        {% set queryparam = queryparam ~ "&partner_region="~request.query_dict.partner_region|urlencode %}
      {% endif %}

      {# Listing #}

      {# Use this if you don't want automatic search before selecting fields #}
      {#
      {% if request.query_dict.warehouse_location && request.query_dict.destination_country %}
      #}
        {% set table = hubdb_table_rows(hubdb_id, queryparam) %}
        {% if table == [] %}
          <p class=‘align-center’>Sorry, no listings found for that Search. Try changing your filter and search again.</p>
        {% else %}
          {% for row in table %}
          <div>
          <div class="item">
            <h4>Partner:</h4>
            <p>{{ row.partner_name }}</p>
          </div>
    
          <div class="item">
            <h4>Partner Type:</h4>
            <p>{{ row.partner_type.name }}</p>
          </div>
    
          <div class="item">
            <h4>Partner region:</h4>
            <p>{{ row.partner_region.name}}</p>
          </div>
    
          <div class="item">
            <h4>Partner tier:</h4>
            <p>{{ row.partner_tier.name}}</p>
          </div>
    
          <div class="item">
            <h4>Partner Country:</h4>
            {% for product in row.partner_country %}
            {{ product.name }}{% if !loop.last %}, {% endif %}
            {% endfor %}
          </div>
    
          <hr>
        </div>
          {% endfor %}
        {% endif %}