topic Managing Multiple Filters in HubDB in CMS Development

Managing Multiple Filters in HubDB

ckingg — Tue, 13 Apr 2021 14:10:14 GMT

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.

{% set queryparam = "" %}  {% 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 %}  {% 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 %}  {% 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 %}  {% 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 %}   {% if partnerType %} {% set queryparam = partnerTypeQuery %} {% endif %}  {% if region %} {% set queryparam = partnerRegionQuery %} {% endif %}  {% if tier %} {% set queryparam = partnerTierQuery %} {% endif %}  {% if country %} {% set queryparam = partnerCountryQuery %} {% endif %}  {% if partnerType and region %} {% set queryparam = queryparam ~ partnerTypeQuery~partnerRegionQuery %} {% endif %}  {% if partnerType and tier %} {% set queryparam = queryparam ~ partnerTypeQuery~partnerTierQuery %} {% endif %}  {% if partnerType and country %} {% set queryparam = queryparam ~ partnerTypeQuery~partnerCountryQuery %} {% endif %}  {% if region and tier %} {% set queryparam = queryparam ~ partnerRegionQuery~partnerTierQuery %} {% endif %}  {% if region and country %} {% set queryparam = queryparam ~ partnerRegionQuery~partnerCountryQuery %} {% endif %}  {% if tier and country %} {% set queryparam = queryparam ~ partnerRegionQuery~partnerCountryQuery %} {% endif %}  {% 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 %}

Re: Managing Multiple Filters in HubDB

dennisedson — Tue, 13 Apr 2021 18:42:40 GMT

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

Re: Managing Multiple Filters in HubDB

ckingg — Tue, 13 Apr 2021 18:48:12 GMT

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