HubDB Filtering

[cives]

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]

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]

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 · Accepted solution]

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.

[VOnah]

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>

[cives]

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]

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]

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]

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