JOQULAR Select & Joins

THIS PAGE IS FOR FUNCTIONALITY TO BE RELEASED MAY 5TH

Introduction

JOQULAR supports SQL like SELECT operations including the use of FIRST, LAST, WHERE and GROUP BY. JOQULAR also adds SAMPLE, which returns a random set of records from the results. The entire JOQULAR pattern language is supported and objects can be joined on property values using the WHERE clause. If no joins are specified, a cross-product of all instances is returned. Results are represented as either:

1. An array of arrays of instances of the specified class. Each sub-array is effectively a row, although these arrays are augmented with keys that refer to objects by their query aliases.
2. An array of rows of POJOs where each row is an array just one element long containing a POJO that is defined by a property projection specified by the SELECT clause of the query.

With a few exceptions queries can be turned into strings using JSON.stringify for transmission to a server or translation into a form consumable by another data query/storage mechanism, e.g. MongoDB, Google Cloud Datastore, CouchDB, etc.

For example, with the exception of leaving out the augmenting alias keys:

new Person('Joe',24,'male');
new Person('Jo',24,'female');

JOQULAR.select().from({p1: Person}).exec();
// returns [[{name:'Joe',age:24,gender:'male'}],[{name:'Jo',age:24,gender:'male'}]]
// with the objects being actual instances of Person

JOQULAR.select({p1:['name']}).from({p1: Person}).exec();
// returns [[{name:'Joe'}],[{name:'Jo'}]]
// with the objects being POJOs rather than Persons

var query = JOQULAR.select().from({p1: Person}).where({p1: {name: 'Joe'}});
query.exec();
// returns [[{name:'Joe',age:24,gender:'male'}]]]
JSON.stringify(query);
// returns "{"select":{"from":{"p1":"Person"},"where":{"p1":{"name":"Joe"}}}}"

JOQULAR.select().from({p1: Person, p2: Person}).where({p2: {gender: {neq: {p1: 'gender}}}}).exec();
// returns [
            [{name:'Joe',age:24,gender:'male'},{name:'Jo',age:24,gender:'female'}],
            [{name:'Jo',age:24,gender:'female'},{name:'Joe',age:24,gender:'male'}]
           ]

JOQULAR.select(projection)

Returns a Select instance configured to use the provided projection. The projection is an map of property references where each key is the desired property in the POJO and each value is an object keyed to a class alias (see from function below) and containing a . delimited property reference to any depth supported by the class being queried. For example,

// assume a Person with value {name:'Bill',address: {city: 'Seattle', state:'WA', zipcode: 98110}}
JOQULAR.select({name: {p1: "name"}, zipcode: {p1: "address.zipcode"}}).from({p1: Person});
// returns [[{name:'Bill',zipcode:98110}]]

Select.prototype.from(classAliases)

Returns the Select instance after configuring it to use the specified classAliases. The classAliases take the form: {: [, : , ...]}, for example:

JOQULAR.select().from({p1: Person,e1: Employer});

The use of these class aliases is similar to the SQL keyword AS.

SELECT * FROM Person AS p1, Employer AS e1

Although, in JOQULAR the use of aliases is required, where as in SQL it is optional.

Select.prototype.where(pattern)

Returns the Select instance after configuring it to use the provided pattern. The pattern is any legal JOQULAR pattern with the exception that top level keys must refer to a class alias specified in the .from call or be the special keys 'eq' or 'neq'.

JOQULAR.select().from({p1: Person}).where({p1: {name: 'Bill'}}); // legal
JOQULAR.select().from({p1: Person}).where({name: 'Bill'}); // not legal