Filtering Query Results

How to: Filtering Query Results

Using a single or multiple filter fields, before the query is requested, will dramatically enhance the speed of your application in which result view will be delievered. Doing such is a simple experience once getting used to with several steps. Before building a large cluster of query script, remember to start with something small.

---

So to speak, making a filtered query should start with a basic query;

var queryText = "from Entity";

This is our base, the main part of the whole query script.

Next step involves in getting the values of our filter fields.

var textFilter = getComponent("textArea").value;  // This here is a simple text field or text area field.
var numFilter = getComponent("numArea").value; // This however is an integer, float, double, bigcurrency or so on field.
var comboboxFilter = getComponent("comboboxValue").selectedItem; // A single selection from multiple values, aka combobox field
var dateFilter = getComponent("dateArea").value; // Date field, consists of both date and time

Now we have all the information we need to filter our results.

After this step, we need a new variable.

var queryFilter = "where";

We have our variable, let's fill it up. But remember making a null check for every field to be added.

A simple textbox filter will look like this;

if(textFilter != null || textFilter.trim() != ""){
	if(queryFilter != "where") // This line will check if another parameter was added earlier, 
		queryFilter += " and"; // if true, we will add "and" between two different parameters. if false, no action will be taken.
	queryFilter += " metadataStringField = '" + textFilter + "'"; // Please note that we need "'" both before and after our filter variable
	}

And a simple integer filter will be like this;

if(numFilter != null){
	if(queryFilter != "where")
		queryFilter += " and";
	queryFilter += " metadataStringField = " + numFilter;  // Notice that we do not have to use "'" around the filter variable
	}

A more advanced field, combobox selectedItem filter will look similar to this;

if(comboboxFilter.selectedItem != null){
	if(queryFilter != "where")
		queryFilter += " and";
	queryFilter += " metadataAnyField = '" + comboboxFilter.selectedItem + "'";
	}

Remember that if you are using Entity type combobox fields, then the script will look like this;

if(comboboxFilter.selectedItem != null){
	if(queryFilter != "where")
		queryFilter += " and";
	queryFilter += " metadataAnyField.id = '" + comboboxFilter.value.id + "'";
	}

Also for list and list items, the filter will change like this

if(comboboxFilter.selectedItem != null){
	if(queryFilter != "where")
		queryFilter += " and";
	queryFilter += " metadataAnyField.key = '" + comboboxFilter.selectedItem.key + "'";
	}

Most complex of all, a date filter requires a little tweaking;

if(dateFilter != null){
	if(queryFilter != "where")
		queryFilter += " and";
	queryFilter += " DATE_FORMAT(metadataDateField, '%Y-%m-%d') >= '" + formatDate("yyyy-MM-dd", dateFilter) + "'";
	}