SQL Like: Criteria

You can specify a number custom criteria to apply to the query that will be executed in a SQL-like way:

SELECT {propertyName}
WHERE {propertyName} = "somevalue"

SELECT orderNumber
WHERE orderNumber = "123ABC"

NOTE: See here for the complete list of criteria.

Multiple Criteria

To add multiple criteria, simply separate each criteria block with an "and":

SELECT orderNumber
WHERE orderNumber = "123ABC" AND orderId != 24

Association Criteria

If you want to add criteria based on association fields, you will need to add a join and use the join alias within the criteria:

SELECT orderNumber
WHERE orderNumber = "123ABC" AND u.firstName != "Matthew"

Custom Criteria

In addition to the criteria supported out of the box, it's also possible to define your own criteria by registering a custom function with CriteriaBuilder:

cb.customCriteria('myCustomCriteria', function(data) {
    return data['a.state'] != 'Iowa';
});

The custom function will return a single argument which is a map of the current row's data, keyed by join aliases/field names:

// data 
{
    a.address: "100 West Ontario",
    _this.orderNumber: 123234324,
    u.firstName: "Johnny",
    ...
}

Once you've registered the custom criteria function, you can use it in your sql string like so:

SELECT orderNumber
WHERE orderNumber = "123ABC" AND {{myCustomCriteria}}

Example

var cb = new CriteriaBuilder.Builder({
    store: mystore
});
var sql = 'select a.address addy, orderId, orderNumber num join user us join address a on user where a.address like "%Avenue%" and {{myCustomCriteria}}';
// register custom function
cb.customCriteria('myCustomCriteria', function(data) {
    return data['a.state'] != 'Iowa';
});
// run
cb.query({sql:sql});