SQL Like: Joins

If you wish to include fields from associated models in your store/collection result set, or if you want to create custom criteria based on these fields, you'll need to add a "join." This can be done in a very SQL-like way via the join syntax:

SELECT {associationAlias}.{propertyName}
JOIN {association} {associationAlias}

SELECT u.lastName
JOIN user u

When defining a join, you must use the "role" of the association as the name of the join. If you wish, you can additionally define an "alias" to use for the join (see example syntax above). If you don't specify a join alias, the name of the association's role will be used as the default, and any fields or criteria that you wish to include which involve the join should use the alias

Context

By default, all joins will be "joined" on the root entity, which is the model defined for the store. It is possible to join on associations as well. For example, consider that we have an "Order" entity, which has a "User", which in turn has an "Address". To include the address for fields and criteria, the special "join on" syntax should be used:

SELECT {associationAlias}.{propertyName}
JOIN {association} {associationAlias}
JOIN {association} {associationAlias} on {association}

SELECT u.lastName, a.address
JOIN user u
JOIN address a on user
WHERE a.address like "%Avenue%"

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%"';
// run
cb.query({sql:sql});