Managing Multiple Filters in HubDB

Develop, Build & Integrate CMS Development
1

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

@Anton , @alyssamwilie , do you have some ideas for @ckingg ? :folded_hands:

3

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