CMS Development

cives
Contributor

HubDB Filtering

SOLVE

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!

0 Upvotes
1 Accepted solution
alyssamwilie
Solution
Recognized Expert | Elite Partner
Recognized Expert | Elite Partner

HubDB Filtering

SOLVE

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.

If this answer solved your question, please mark it as the solution.

Alyssa Wilie Profile Image

Alyssa Wilie

Web Developerat Lynton

Learn HubL | Get Marketing Insights

HubSpot Elite Solutions Partner
Lynton's HubSpot theme Rubric now available. Click to download.

View solution in original post

8 Replies 8
alyssamwilie
Recognized Expert | Elite Partner
Recognized Expert | Elite Partner

HubDB Filtering

SOLVE

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!

If this answer solved your question, please mark it as the solution.

Alyssa Wilie Profile Image

Alyssa Wilie

Web Developerat Lynton

Learn HubL | Get Marketing Insights

HubSpot Elite Solutions Partner
Lynton's HubSpot theme Rubric now available. Click to download.
cives
Contributor

HubDB Filtering

SOLVE

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

 

 

0 Upvotes
alyssamwilie
Solution
Recognized Expert | Elite Partner
Recognized Expert | Elite Partner

HubDB Filtering

SOLVE

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.

If this answer solved your question, please mark it as the solution.

Alyssa Wilie Profile Image

Alyssa Wilie

Web Developerat Lynton

Learn HubL | Get Marketing Insights

HubSpot Elite Solutions Partner
Lynton's HubSpot theme Rubric now available. Click to download.
VOnah
Member

HubDB Filtering

SOLVE

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>

0 Upvotes
cives
Contributor

HubDB Filtering

SOLVE

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 %}
0 Upvotes
alyssamwilie
Recognized Expert | Elite Partner
Recognized Expert | Elite Partner

HubDB Filtering

SOLVE

If you only want the search -

 

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

If this answer solved your question, please mark it as the solution.

Alyssa Wilie Profile Image

Alyssa Wilie

Web Developerat Lynton

Learn HubL | Get Marketing Insights

HubSpot Elite Solutions Partner
Lynton's HubSpot theme Rubric now available. Click to download.
0 Upvotes
cives
Contributor

HubDB Filtering

SOLVE

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
Participant | Partner
Participant | Partner

HubDB Filtering

SOLVE

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

0 Upvotes