Hubdb : how to use a module field variable as a column name in a filter query

[BernardG]

Hi There,

I try to make a custom module that will filter hubdb data based on the choice of a columnn by the end user (e.g. the end user can choose to filter by column 1 or 2 or 3,...).

The problem I encounter is here : 

{% set switchs = hubdb_table_column(1034717,"switch").options %} = > works fine (where "switch" is a column name)

But if I replace"switch" by a field variable "{{ item.column_name }} " => the selector does not get populated with the options from the "switch" column, while the variable has excatly the column name (e.g. "switch"). 

Could anybody help? Here is the code below

<div>
  <table style="width:90%";>
    <tr>
     <form  id="form_id" method="get">
       <div>
        <th style ="width: 20%"><h4>Filter by :</h4></th>   
        {% for item in module.columns %} 
         <th style ="width: 20%";><h6>{{ item.column_name }} : </h6>
         <select name="{{ item.column_name }}" form="form_id" >     
            <option value="show-all">Show All</option>  
            {% set switchs = hubdb_table_column(1034717,"switch").options %}
            {% for choice in switchs %}
               {% set switch_list = switch_list~choice.id|list%}
               {% if choice.id == request.query_dict.switch%}    
                  <option selected="selected" value="{{ choice.id }}">{{ choice.name }}</option>
               {% else %}
                  <option value="{{ choice.id }}">{{ choice.name }}</option>
               {% endif %}
            {% endfor %} 
        </select>
       </th>
      {% endfor %}  
      </div>
    </form>
   </tr>
 </table>
</div>

[Jon_McLaren · Accepted solution]

replace "switch" with your module.field_name

You don't need quotes nor {{}} because you're putting the variable inside of a {%%} already.

[BernardG]

Thank a lot that works fine...super ! I can populate the the form selectors with the options from the hubdb columns.

I do however struggle with the next problem: how do I store these choices for multiple filters by column. I tried the following, but it's not working. (I'm just a beginner trying to build from examples seen on the forum).  

<form  id="form_id" method="get">
       <div>
        <th style ="width: 20%"><h4>Filter by :</h4></th>   
       {% set item.query = "" %}
       {% set queryparam = "" %}
         {% for item in module.columns %} 
         <th ><h6>{{ item.column_name }} : </h6>
         <select name="{{ item.column_name }}" form="form_id" >     
            <option value="show-all">Show All</option>  
            {% set locvars = hubdb_table_column(1034717, item.column_name).options %}
            {% for choice in localvars %}
               {% set localvar_list = localvar_list~choice.id|list%}
               {% if choice.id == request.query_dict.item_column_name%}    
                  <option selected="selected" value="{{ choice.id }}">{{ choice.name }}</option>
               {% else %}
                  <option value="{{ choice.id }}">{{ choice.name }}</option>
               {% endif %}
               
               {% if request.query_dict.item.column_name in ["1", "2", "3", "4", "5"] %}
               {% set item.query = item.query ~ &item.column_name=~request.query_dict.item.column_name|urlencode %}
               {% endif %}
               {% if request.query_dict.item.column_name == "show-all" %}
              
               {% endif %}
               {% set queryparam = queryparam ~ item.query %}
            {% endfor %} 
        </select>
       </th>
      {% endfor %}  
    </form>

[Jon_McLaren]

`

{% if request.query_dict.item.column_name in ["1", "2", "3", "4", "5"] %}`

 item.column_name that's likely the source of your problem.

request.query_dict.variable_name 
grabs a query parameter that was added to the URL.
http://example.com/page?variable_name=2

putting multiple in the url you just need to use a separator of some kind like a "-" or something, then when getting the value split the value by that separator and voila.

Then for the filter query you actually can have multiple  filters, example columnname=1&columnname=2

[BernardG]

Hi Jon 

Thank you for your help and feedback again.

I've been trying for several hours now to find a solution, but it looks like the problem is not in the query, but more in the fact that the select-form has trouble storing more than one input choice when used in the for-loop and thus the query works just for the first item in the loop. 

I guess my project is a bit too ambitious for my competencies 🙂