A few weeks ago, I had a scenario come up where I wanted to be able to filter an Ext.data.Store based on a set of criteria, part of which depended upon a value in one of my model’s associations. While this is easy enough to do via filterBy(), it can become a bit unwieldy to use, especially if you want/need the criteria to be dynamically constructed.

As I was thinking about how to best deal with this, I remembered my experiences of using ColdBox’s CriteriaBuilder, which is based on Hibernate’s API of the same name. Using CriteriaBuilder, you can–among myriad other things–produce complex queries and, because it is all DSL-based, criteria can be added in as dynamic a way as you’d like.

This inspired me to experiment with building a CriteriaBuilder for Ext JS.

CriteriaBuilder

CriteriaBuilder allows you to build complex, dynamic queries that can query any data within an Ext.data.Store, including association data. Since I was bored, I created two approaches for interfacing with CriteriaBuilder.

In the first approach, you can use a SQL-like syntax to craft your query. For example:

var cb = new CriteriaBuilder.Builder({
    store: mystore
});
var sql = 'join user u where u.lastName like "McD%" order by orderNumber ASC';
// run
cb.query({sql:sql, type:'filter'});

The second approach includes a DSL that allows you to build your queries programmatically, adding joins, criteria, orderings and even limits to create a custom query. Whether defining the criteria in one fell swoop, or building it dynamically based on other criteria, this is the power-user way to define queries.

var cb = new CriteriaBuilder.Builder({
    store: mystore
});
// add join
cb.join('user', 'u');
// add criteria
cb.like('u.lastName', 'McD%');
// add order
cb.order('orderNumber', 'ASC');
// run
cb.query({type:'filter'});

// OR JUST CHAIN IT
cb.join('user', 'u')
  .like('u.lastName', 'McD%')
  .order('orderNumber', 'ASC');

The above examples simply filter an existing store’s data based on the passed criteria. However, you can also configure the CriteriaBuilder to return an entirely new MixedCollection or Store, containing only the results from the CriteriaBuilder (as well as only the “columns” you specify, if desired).

Features

Here’s a quick list of the main features of CriteriaBuilder:

  • Use as stand-alone utility or as mixin to a store class
  • Mixin includes convenience methods:
    • newCriteriaBuilder()
    • queryBySql()
    • filterBySql()
  • SQL-like or DSL-based criteria definition
  • “Join” to association data by creating join aliases which can be used in “column” select, criteria, and orderings
  • Ability to sort by any property
  • Add limit/offset to reduce result set size
  • Several criteria options:
    • eq
    • neq
    • gt
    • gte
    • lt
    • lte
    • like
    • not like
    • is null
    • is not null
    • between

Examples

SQL-Like Syntax

This example demonstrates a simple SQL-like query of a data set. In this case, we have an Order, which is made by a User, who has an Address. We’d like to filter this data by Users who have a last name which begins with an “R” and whose Address has the fragment “Ave” in it. We also want to sort the result by the User’s last name.

var sql = 'join user u join address a on user where u.lastName like "R%" and a.address like "%Ave%" order by u.lastName DESC';
// run filterBySql
store.filterBySql(sql);

https://fiddle.sencha.com/#fiddle/n0d

DSL

This example demonstrates using CriteriaBuilder’s DSL to query of a data set. In this case, we have an Order, which is made by a User. We’d like to filter this data by orders placed between 2001 and 2012. We also want to sort the result by the User’s last name, and limit the result set to 5 records.

var cb = store.newCriteriaBuilder();
// use the DSL
cb.join('user', 'u')
  .between('orderDate', '2001-01-01', '2012-01-01')
  .limit(5)
  .order('u.lastName', 'ASC');
// run the query
var data = cb.query({type:'filter'});

https://fiddle.sencha.com/#fiddle/n0e

Try it Yourself

I’ve created CriteriaBuilder as an Ext JS package, so you should be able to simply download it and include it in your application by adding “criteria-builder” to the “requires” section of your app.json:

"requires": [ "criteria-builder" ]

The package includes an example application (under “examples”) that you can run if you want to experiment with some existing data and associations.

CriteriaBuilder Wiki

Caveats

  • Requires Ext JS 5+
  • Currently only works with stores that are locally filtered and sorted
  • Criteria currently only supports “AND” – there is no support for disjunctions yet
  • As this is an experiment, I haven’t tested it against all possible association configurations. So the more exotic associations are (especially in terms of nesting)…best of luck :)
  • As this is an experiment, I haven’t tested it against really big data sets.

Conclusion

Anyway, thus ends the experiment. This was really fun to build, and hopefully it demonstrates some cool stuff that can be done with association data in Ext JS.

Thoughts? Constructive feedback? Please leave a comment!