How to express "or" in a model Query?

Question

I would like to query one of my models in a server script. I'm trying to use the Query object for this, however, I don't see a way to formulate the conditions within the given API. What I need can be expressed in a query language like this:

(FirstValue = :FirstValue or FirstValue = null) and (SecondValue = :SecondValue or SecondValue = null) and (ThirdValue = :ThirdValue or ThirdValue = null)

(The goal is to query a lookup table based on several values. In the lookup table, null represents that this value isn't relevant and any value should be considered a match.)

The Query class appears to support only and by specifying more than one filter, but there's no clear way to express or, is there? Or maybe there's a way to use the query language within the server script?

Using query script to fetch records in a UI element, reacting to onLoad and processing them there is not an option.

Answer 1

I know some ways to achieve desired result:

1. Using filters with with _contains:

// Server Script
var query = app.models.MyModel.newQuery();

// should be resolved to true if 'someFilter' variable is null
query.filters.FirstField._in = [someFilter];

// all subsequent filters should be 'ANDed'
query.filters.SecondField._in = [someFilter];
query.filters.ThirdField._in = [someFilter];
...
var records = q.run();

2. Using 'where' query property:

// Server Script
var query = app.models.MyModel.newQuery();

query.where = 'FirstField =? :parameter1 AND SecondField =? :parameter2 AND ...';
query.parameters.parameter1 = 'some value';
query.parameters.parameter2 = null;
...

var records = q.run();

3. Just putting where condition from the second option to datasource's query builder expression:

// Query builder expression
FirstField =? :parameter1 AND
SecondField =? :parameter2 AND

...

then you can bind this query parameters to some widgets and load datasource on value change.

Important:

The "?" modifier for leaf operators considers a null value for the right-hand side of the expression to be true (Query Builder).