Dynamic object-table Filtering

General Q&A
1

I have a view with an object-table. The objects in this table have 3 boolean fields that users should be able to filter on and we may add more filters in the future. So above the table we have 3 Yes/No questions (is_x, is_y, is_z) for these properties and then an “Apply Filters” button. My questions is on building the where clause for backing these filters as it’s looking to be quite ugly.

My first instinct was to build a query like:

var whereClause = "";
if (view.filter_x) {
    whereClause += "is_x = 'true'";
}
... // etc including logic for adding 'and'

But this throws an error about needing a question mark in the filter, “Error: Expected ?”

Another option is 7 different ifs for each combination of filters (x; y; z; x && y; x &&z; y &&z; x && y && z) which looks really ugly from a code perspective and makes adding other filters in the future very difficult.

Another option is to stack wheres e.g.

.where("is_x = ?", true).where("is_y = ?", true)

I haven’t actually tested that this works and I assume it’s inefficient since I believe this would re-execute the query for each where clause (if it even works).

Anyone have a nicer recommendation here?

2

The best solution to this would be to use the .apply function on a .where clause. This will allow you to build a query dynamically and pass the query string and params to the DB in one function.

Here is an example of this:

// Contains all the fields you want to query on
var queryString = "";

// Contains all the argument values for the above
var params = [];

// Example fields on the model
var expressions = ["is_x", "is_y", "is_z"];

for(var i = 0; i < expressions.length; i++) {
   if(i === (expressions.length -1)) {
       // The last item should not include the 'and'
       queryString += expressions[i] + " = ?"
   }  else {
       queryString += expressions[i] + " = ? and "      
   }
   // Harcoded for the example, but this can be based on user input, etc.
   params.push(true);
}

var args = [];
args.push(queryString);
args = args.concat(params);

// args === ["is_x = ? and is_y = ? and is_z = ?", true, true, true];
var queryResult = DB.model.where.apply(DB.model, args);
1 Like
3

Thanks Mike, we got this working. But important to note that true.toString() causes no results to be returned because of how booleans are stored in the DB. It should be just

params.push(true); // remove .toString()
1 Like
4

Thanks for the note there @fthomas I’ve updated the snippet.

1 Like