HubDB: orderBy various columns using a select menu

hubdb

#1

I have a page set up to show results from a HubDB. It is already set up with a select field to filter the results. I'd also like to let the user sort the results by different columns — deadline or scholarship_name.

So far I have the following code, but can't figure out how to attach the selected option to my queryparameter.

Code to capture the user's sort selection:

Featured Deadline Name

Current QueryParameter

{% set queryparam = "" %}
{% if request.query_dict.field_of_study in ["1", "2", "3", "4", "5", "6", "7"] %}
{% set queryparam = queryparam ~ "&field_of_study__contains="~request.query_dict.field_of_study|urlencode %}
{% endif %}


#2

Hi Barbara, you should be able to just tack that onto the end of your query string.

    {% set queryparam = queryparam ~ "&field_of_study__contains="~request.query_dict.field_of_study|urlencode ~ "&orderBy=deadline" %}

Does that work?


#3

Sorry, I didn't realize the editor stripped out of some my code. The problem is that I want the user to be able to change the sort order using a select menu. So, when the results first display, the are ordered naturally, but the user can change the option and have them re-sort by date.

Here is the code I'm using for the select menu:

<div class="span6 text-right"> 
    Sort by: 
<select class="sortorder" name="orderBy" form="form_id">
    <option selected="selected" value="featured">Featured</option>
    {% set deadline = hubdb_table_column(677507, "deadline") %}
    {% for choice in deadline %}
        {% if choice.name == request.query_dict.deadline%}
            <option selected="selected" value="{{ choice.name }}">{{ choice.name }}</option>
        {% else %}
            <option value="{{ choice.name }}">{{ choice.name }}</option>
        {% endif %}
    {% endfor %}
</select>
</div>

And here is the code I'm using to try and add the orderBy selection to the query parameter:

        {% if request.query_dict.deadline %}
        {% set queryparam = queryparam ~ "&orderBy="~request.query_dict.deadline|urlencode %}
        {% endif %}

#4

Hmm, I think what you're trying to do is order by a particular value of a column. What's possible is ordering by all values of the column.

The value of the orderBy parameter is simply the name of a column in the table which can be prefixed with '-' for a reverse order.

Am I understanding this correctly? If not, what are the names of the choices in the "deadline" column?


#5

I'm trying to use the titles of the columns to alter the orderBy parameter. So, I have a column named "deadline" and a column named "scholarship_names".

Ideally, when the page loads the table items would load with the default sort order — I'm calling this "featured". Then, when the user chooses a selection from the menu, the items would sort by either "deadline" or "scholarship_names".

I set up my example code with just the "deadline" option for simplicity's sake.


#6

If the names in your select field match your column names, then this should work. You can verify the results by using the same parameters on a request to the HubDB API like

https://api.hubapi.com/hubdb/api/v1/tables<YOUR_TABLE_ID>/rows?portalId=<YOUR_PORTAL_ID>&orderBy=deadline

If that works, but your page does not, then I find it's helpful to confirm the constructed query matches what I intended by printing out the queryparam in the page.


#7

When I test the orderBy options using the api URL, it works as expected.

If append the orderBy parameter manually using the code below, it works as expected.
{% set queryparam = queryparam ~ "&orderBy=deadline" %}

If I switch back to the original code
{% if request.query_dict.deadline %}
{% set queryparam = queryparam ~ "&orderBy="~request.query_dict.deadline|urlencode %}
{% endif %}
and print the queryparam, it shows all the parameters except for orderBy.

So, it seems that the select menu is not correctly setting the orderBy option.