Skip to main content
Skip table of contents

Dynamic dropdown menu for query parameters

This is a simple example template to show how you can change query parameters of a PocketQuery macro dynamically with a dropdown.

Prerequisites: you have a PocketQuery macro with the Load macro dynamically checkbox checked.

Again, we are using sample WorldDB for illustration purposes. I'm querying the population numbers of different countries by the country's continent, where Continent is my only query parameters. This is what the result looks like:

This is the simplest template for achieving a dynamic dropdown. The PocketQuery macro must be added to a page with the Load macro dynamically and Enable dynamic parameters options checked. More advanced scenarios could, for example, populate the select options for a separate SQL query. If you have a specific scenario you can raise an issue in our support Jira

But back to the simple example. My query is a fairly simple SQL statement and looks like this (it works the same way for REST API queries):

CODE
SELECT Name, Population
FROM Country
WHERE Continent = :Continent

The template is a bit more complex, but with some rudimentary knowledge in HTML and JavaScript, it shouldn't be too hard to follow what's going on. I also added lots of comments in the code.

CODE
## I'm using a few hard-coded values in an array that will be used to populate the dropdown
## Queries can then be parameterized with these values. So the values will become the
## :Continent query parameter.
#set($pqContinents = ["Asia", "Europe", "North America", "South America", "Africa", "Oceania"])
 
## This select box will become the dropdown
<select class="select" id="select-continent" name="select-continent">
    ## We iterate through our array of continents and create an option for each continent
    #foreach($continent in $pqContinents)
        ## The $queryParameters object holds the current query parameters. We can just
        ## ask what's currently provided for :Continent. If the continent is the one
        ## for which the select option in this loop iteration is created, then we set the
        ## selected attribute on the option so it will be selected in the UI.
        <option value="$continent"
            #if($continent == $queryParameters.Continent)selected="selected"#end
            >$continent</option>
    #end
</select>
 
## We simply load the default template here. Of course we could also create a custom
## template instead.
$PocketQuery.template("default")
 
 
## Now we need to add the dynamic logic to our template. Whenever the dropdown value is
## changed, we need to reload the query. For this we use the PocketQuery JavaScript API
## and jQuery.
<script>
jQuery('#select-continent').change(function() {
    // This obtains the container element for the query result in the current DOM.
    // NOTE: YOUR_QUERY_NAME must be replaced with your query name!
    var container = jQuery('.pocketquery-dynamic-load[data-query="YOUR_QUERY_NAME"]');
    // This retrieves metadata data for this query and its current parameters.
    var data = PocketQuery.getDynamicLoadData(container);
    // We change the :Continent query parameter to the value that's currently selected
    // in the dropdown.
    data.queryParameters = { Continent: jQuery(this).val() };
    // This call triggers the reload of the container and will display the updated values.
    PocketQuery.load({
        container: container,
        data: data
    });
});
</script>

I admit that this template looks a bit bloated. But it's also because of the many comments. And after all PocketQuery templates will always need programming if the standard is not sufficient.

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.