HubDB Filtering

cives · April 20, 2020, 4:11pm

Hi all — I followed along with this tutorial over the weekend, trying to apply some of the filtering aspects to a table I use on my site. I was able to get the search bar to work, though it was very buggy. I removed my code for the meantime. I’m trying to add the ability to search by city or filter by some of the yes or no columns to the right: https://www.nhada.com/covid19/inspection-survey

Here is my markup:

<!-- set the filter by drop down, search bar, and submit button -->
<div>
 <form id="form_id" method="get">
 <div>
 <h4>FILTER BY LISTING TYPE: </h4>
 <select name="type" form="form_id" onChange="this.form.submit()">
 <option value="show-all">Show All</option>
 {% set contact = hubdb_table_column(2618384, "contact_free_service").options %} {% for choice in contact %} {% set type_list = type_list~choice.id|list%} {% if choice.id == request.query_dict.contact%}
 <option selected="selected" value="{{ choice.id }}">{{ choice.name }}</option>
 {% else %}
 <option value="{{ choice.id }}">{{ choice.name }}</option>
 {% endif %} {% endfor %}
 </select>
 </div>
 <div>
 <input name="city" type="text" id="search-by" class="autocomplete" placeholder="Search by City">
 </div>
 <input id="submit-button" type="submit" value="search">
 </form>
 <!-- sets the different query parameters using submitted input for hubdb query -->
 ... {% set queryparam = "" %} {% if request.query_dict.contact in ["1", "2", "3", "4"] and request.query_dict.city == "" %} {% set queryparam = queryparam ~ "&type="~request.query_dict.contact|urlencode %} {% endif %} {% if request.query_dict.contact in ["1", "2", "3", "4"] and request.query_dict.city != "" %} {% set queryparam = queryparam~"&type="~request.query_dict.contact|urlencode~"&city__icontains="~request.query_dict.city|urlencode %} {% endif %} {% if request.query_dict.contact == "show-all" and request.query_dict.city != "" %} {% set queryparam = queryparam~"&city__icontains="~request.query_dict.city|urlencode %} {% endif %} ...
</div>
<div class="mt-20" style="overflow-x:auto;">
 <h4 class="u-styled-heading">COVID-Safe Inspection/Service Stations</h4>
 <table class="responstable mt-10">
 <thead>
 <tr>
 <th>Business Name</th>
 <th>City</th>
 <th style="width:10em !important;">Phone Number</th>
 <th>Website URL</th>
 <th style="width:8em !important;">Pick Up/Drop Off Service?</th>
 <th style="width:6em !important;">Contact Free Service?</th>
 </tr>
 </thead>
 <tbody>
 {% for row in hubdb_table_rows(2618384, 'orderBy=city') %}
 <tr>
 <td>{{ row.business_name }}</td>
 <td>{{ row.city }}</td>
 <td style="width:120px !important;">{{ row.phone_number }}</td>
 <td><a href="https://www.{{ row.website_url }}">{{ row.website_url }}</a></td>
 {% for name in row.pickup_dropoff %}
 <td>{{ name.name }}</td>
 {% endfor %} {% for name in row.contact_free_service %}
 <td>{{ name.name}}</td>
 {% endfor %}
 </tr>
 {% endfor %}
 </tbody>
 </table>
</div>

Can someone indicate where I went wrong? Thanks!

alyssamwilie · April 20, 2020, 8:18pm

Hey @cives ,

From your code it doesn’t look like you’ve actually added the queryparam varaible, that’s housing the search output info, to your HubDB call. In the code you posted it shows -

{% for row in hubdb_table_rows(2618384, 'orderBy=city') %}

But should be -

{% for row in hubdb_table_rows(2618384, queryparam) %}

And then you can add your orderBy to the default queryparam variable.

{% set queryparam = "orderBy=city" %}

Hope that helps!

cives · April 20, 2020, 10:14pm

Hey. Thanks for the reply! I altered those calls as you suggested. I’m still having difficulty with the drop-down selector.

My code now looks like:

<div class="mt-20">

 <h4 class="u-styled-heading">COVID-Safe Inspection/Service Stations</h4>

 <!-- set the filter by drop down, search bar, and submit button -->

 <div>
 <form id="form_id" method="get">

 <div>
 <h4>FILTER BY LISTING TYPE: </h4>
 <select name="type" form="form_id" onChange="this.form.submit()">
 <option value="show-all">Show All</option>
 {% set contact = hubdb_table_column(2618384, "contact_free_service").options %} {% for choice in contact %} {% set type_list = type_list~choice.id|list%} {% if choice.id == request.query_dict.contact%}
 <option selected="selected" value="{{ choice.id }}">{{ choice.name }}</option>
 {% else %}
 <option value="{{ choice.id }}">{{ choice.name }}</option>
 {% endif %} {% endfor %}
 </select>
 </div>

 <div>
 <input name="city" type="text" id="search-by" class="autocomplete" placeholder="Search by City">
 </div>
 <input id="submit-button" type="submit" value="search">

 </form>

 <!-- sets the different query parameters using submitted input for hubdb query -->
 ... {% set queryparam = "" %} {% if request.query_dict.type in ["1", "2", "3", "4"] and request.query_dict.city == "" %} {% set queryparam = queryparam ~ "&type="~request.query_dict.type|urlencode %} {% endif %} {% if request.query_dict.type in ["1", "2", "3", "4"] and request.query_dict.city != "" %} {% set queryparam = queryparam~"&type="~request.query_dict.type|urlencode~"&city__icontains="~request.query_dict.city|urlencode %} {% endif %} {% if request.query_dict.type == "show-all" and request.query_dict.city != "" %} {% set queryparam = queryparam~"&city__icontains="~request.query_dict.city|urlencode %} {% endif %} ...

 </div>

 {% set table = hubdb_table_rows(2618384, queryparam) %}

 <table class="responstable mt-10">
 <thead>
 <tr>
 <th>Business Name</th>
 <th>City</th>
 <th style="width:10em !important;">Phone Number</th>
 <th>Website URL</th>
 <th style="width:8em !important;">Pick Up/Drop Off Service?</th>
 <th style="width:6em !important;">Contact Free Service?</th>
 </tr>
 </thead>
 <tbody>
 {% 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 %}
 <tr>
 <td>{{ row.business_name }}</td>
 <td>{{ row.city }}</td>
 <td style="width:120px !important;">{{ row.phone_number }}</td>
 <td><a href="https://www.{{ row.website_url }}">{{ row.website_url }}</a></td>
 {% for name in row.pickup_dropoff %}
 <td>{{ name.name }}</td>
 {% endfor %} {% for name in row.contact_free_service %}
 <td>{{ name.name}}</td>
 {% endfor %}
 </tr>
 {% endfor %} {% endif %}
 </tbody>
 </table>
</div>

As it stands the search bar for city is working, but the drop-down selector is not functioning.

My guess is I’ve gone somewhere wrong in the initial setup of the “contact” variable

 <select name="type" form="form_id" onChange="this.form.submit()">
 <option value="show-all">Show All</option>
 {% set contact = hubdb_table_column(2618384, "contact_free_service").options %}
 {% for choice in contact %}
 {% set type_list = type_list~choice.id|list%}
 {% if choice.id == request.query_dict.contact%}
 <option selected="selected" value="{{ choice.id }}">{{ choice.name }}</option>
 {% else %}
 <option value="{{ choice.id }}">{{ choice.name }}</option>
 {% endif %}
 {% endfor %}
 </select>

That block is referenced again in the conditional calls below:

{% set queryparam = "" %}
{% if request.query_dict.type in ["1", "2", "3", "4"] and request.query_dict.city == "" %}
 {% set queryparam = queryparam ~ "&type="~request.query_dict.type|urlencode %}
{% endif %}
{% if request.query_dict.type in ["1", "2", "3", "4"] and request.query_dict.city != "" %}
 {% set queryparam = queryparam~"&type="~request.query_dict.type|urlencode~"&city__icontains="~request.query_dict.city|urlencode %}
{% endif %}
{% if request.query_dict.type == "show-all" and request.query_dict.city != "" %}
 {% set queryparam = queryparam~"&city__icontains="~request.query_dict.city|urlencode %}
{% endif %}

alyssamwilie · April 20, 2020, 10:42pm

You’ll want to change &type to the label for the column your wanting to filter by which I think is “contact_free_service” (?).

{% set queryparam = "" %}
{% if request.query_dict.type in ["1", "2", "3", "4"] and request.query_dict.city == "" %}
 {% set queryparam = queryparam ~ "&contact_free_service="~request.query_dict.type|urlencode %}
{% endif %}
{% if request.query_dict.type in ["1", "2", "3", "4"] and request.query_dict.city != "" %}
 {% set queryparam = queryparam~"&contact_free_service="~request.query_dict.type|urlencode~"&city__icontains="~request.query_dict.city|urlencode %}
{% endif %}
{% if request.query_dict.type == "show-all" and request.query_dict.city != "" %}
 {% set queryparam = queryparam~"&city__icontains="~request.query_dict.city|urlencode %}
{% endif %}

Also in some places you’re using request.query_dict.type but in others request.query_dict.contact. You’ll want to change contact to type if you’re going to be using type as your query param in the url.

cives · April 21, 2020, 11:23am

That worked! Thank you.

If I wanted to simply eliminate the dropdown filter and just use the search bar, what would the conditional logic look like? It’s currently:

{% set queryparam = "" %}
{% if request.query_dict.type in ["1", "2", "3", "4"] and request.query_dict.city == "" %}
 {% set queryparam = queryparam ~ "&contact_free_service="~request.query_dict.type|urlencode %}
{% endif %}
{% if request.query_dict.type in ["1", "2", "3", "4"] and request.query_dict.city != "" %}
 {% set queryparam = queryparam~"&contact_free_service="~request.query_dict.type|urlencode~"&city__icontains="~request.query_dict.city|urlencode %}
{% endif %}
{% if request.query_dict.type == "show-all" and request.query_dict.city != "" %}
 {% set queryparam = queryparam~"&city__icontains="~request.query_dict.city|urlencode %}
{% endif %}

alyssamwilie · April 21, 2020, 1:59pm

If you only want the search -

{% set queryparam = "" %}
{% if request.query_dict.city != "" %}
 {% set queryparam = queryparam ~ "&city__icontains="~request.query_dict.city|urlencode %}
{% endif %}

cives · May 1, 2020, 12:48pm

Thanks for your continued help with this. Is there a way to ignore spaces in a search query? A space adds a “+” to the URL and it seems to throw the results off. Thanks in advance,

sandipan_saha · September 1, 2021, 9:00pm

Hi @cives Were you able to figure out the issue about ignoring spaces in a search query ?

VOnah · November 27, 2021, 7:16pm

hello @alyssamwilie i’m having similer issue, see my code so far, the filter and search isn’t working. Could you please help out or anyone please? thanks in advance.

<div>
<form id=“form_id” method=“get”>
<div>
<h4>FILTER BY LISTING TYPE: </h4>
<select name=“type” form=“form_id” onChange=“this.form.submit()”>
<option value=“show-all”>Show All</option>
{% set type = hubdb_table_column(<HubDB_Table_Id>, “country”).options %}
{% for choice in type %}
{% set type_list = type_list~choice.id|list%}
{% if choice.id == request.query_dict.country%}
<option selected=“selected” value=“{{ choice.id }}”>{{ choice.name }}</option>
{% else %}
<option value=“{{ choice.id }}”>{{ choice.name }}</option>
{% endif %}
{% endfor %}
</select>
</div>
<div>
<input name=“event_title” type=“text” id=“search-by” class=“autocomplete” placeholder=“Search by City”>
</div>
<input id=“submit-button” type=“submit” value=“search”>
</form>
{% set queryparam = “” %}
{% if request.query_dict.type in [“1”, “2”, “3”, “4”] and request.query_dict.event_title == “” %}
{% set queryparam = queryparam ~ “&country=”~request.query_dict.type|urlencode %}
{% endif %}
{% if request.query_dict.type in [“1”, “2”, “3”, “4”] and request.query_dict.event_title != “” %}
{% set queryparam = queryparam~“&country=”~request.query_dict.type|urlencode~“&event_title__icontains=”~request.query_dict.event_title|urlencode %}
{% endif %}
{% if request.query_dict.type == “show-all” and request.query_dict.event_title != “” %}
{% set queryparam = queryparam~“&event_title__icontains=”~request.query_dict.event_title|urlencode %}
{% endif %}
<div>
<div class=“container FilterContainer mt-5”>
{% set current_dt = unixtimestamp( local_dt ) %}
{% set query = “date__lt=”~current_dt~“&orderBy=-date” %}
{% set table = hubdb_table_rows(<HubDB_Table_Id>, query) %}
{% for row in table %}
<div class=“card column”>
<div class=“row no-gutters”>
<div class=“col-md-4”>
<img src=“{{ row.banner.url }}” alt=“Banner” class=“img-fluid h-100” />
</div>
<div class=“col-md-6”>
<div class=“card-body”>
<p class=“card-text”>
<span class=“badge badge-pill”>{{ row[“country”].name }} </span>
<span class=“badge badge-pill”> <i class=“fa fa-calendar”></i> {{ row.date|datetimeformat((‘%B %e, %Y’)) }}</span>
<span class=“badge”>{{ row.location }}</span>
<span class=“badge badge-pill”>{{ row.language }}</span>
</p>
<h5 class=“card-title”><strong>
{{ row.event_title }}</strong></h5>
<p><strong>Decription: </strong>{{ row.description }}</p>
</div>
</div>
<div class=“event-detail-border”></div>
<div class=“col-md-2 btn-body”>
<a href=“{{row.url }}” target=“_blank”>Read More</a>
</div>
</div>
</div>
{% endfor %}
</div>

Topic		Replies	Views
HubDB Filter and Results CMS Development	8	12489	March 14, 2022
HubDB Filters CMS Development hubdb	2	2256	December 29, 2021
HubDB Filter and sort as a module CMS Development hubdb , javascript	10	5244	March 14, 2024
Hubdb Filter module category + partitial filtering CMS Development hubdb	1	1217	July 25, 2023
HubDB filter breaks after 5th entry? CMS Development hubdb	1	2441	April 22, 2020

HubDB Filtering

Related topics