Query depending on a single select box

In this example we want to build a Query that does the following:

Display a select box that dynamically pulls in options (via a Query).
Depending on what is selected, execute another Query that uses the value as a parameter.

This is what the result could look like:

To set this up, we obviously need two Queries.

Example Query: SelectOptions

SQL

SELECT DISTINCT Continent
FROM Country

Example Query: NestedQuery

SQL

SELECT Name, Code, Population
FROM Country
WHERE Continent = :Continent
LIMIT 5

Notice that this Query has a parameter called "Continent".

Via this parameter it will receive the value from the other Query.

We now just need to connect the two Queries using a Template.

Using the $PocketQuery Velocity API we can render our nested Query with a default parameter.

We then bind a change listener to our select, that reloads the nested Query using the PocketQuery JavaScript API, whenever a new value is selected.

Example Template for SelectOptions Query

CODE

## Key of the nested Query that we want to execute. You will find this 
## by clicking on the nested query in the line below its name
#set($nestedQuery = "284126ab-570d-48b1-97a5-79083dc1d15e")
 
<form method="get" class="aui pq-dynamic-parameter-form" action="">
  <select class="select" id="select-continent">
    ## This will render all the continents from our outer Query into the select box
    ## We give it a default value of "Europe"
    #foreach($row in $result)
      <option value="$row.Continent"
        #if($row.Continent == "Europe")selected="selected"#end
      >
        $row.Continent
      </option>
    #end
  </select>
</form>
 
## This Query will render the table below, taking the parameter from our select.
## We pass on the default values we chose above.
<div id="nested-query">
$PocketQuery.renderPocketQueryMacro($nestedQuery, {
  "page": $page,
  "parameters": { "Continent":"Europe" },
  "dynamicload": "true"
})
</div>
 
<script>
 
  // Whenever the continent select value changes, we want to execute this!
  jQuery("#select-continent").change(function() {
    var container = jQuery('#nested-query .pocketquery-dynamic-load');
    var data = PocketQuery.getDynamicLoadData(container);

    data.queryParameters = { Continent: jQuery(this).val() };

    PocketQuery.load({
      container: container,
      data: data
    });
  });
   
</script>