Skip to content

JOQULAR Select & Joins

Jump to bottom Edit New page
Simon Y. Blackwell edited this page May 3, 2015 · 23 revisions

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 ORDER BY. JOQULAR also adds SAMPLE, which returns a random set of rows 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 classes. 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({name: {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'}]
           ]

With the augmenting keys on the results, the last example would actually result in the below:

[
    [0:{name:'Joe',age:24,gender:'male'},
     1:{name:'Jo',age:24,gender:'female'},
     p1:{name:'Joe',age:24,gender:'male'}, // except when stringified, this points to 0 above
     p2:{name:'Jo',age:24,gender:'female'}],// except when stringified, this points to 1 above
    [0:{name:'Jo',age:24,gender:'female'},
     1:{name:'Joe',age:24,gender:'male'},
     p1:{name:'Jo',age:24,gender:'female'},// except when stringified, this points to 0 above
     p2:{name:'Joe',age:24,gender:'male'}]// except when stringified, this points to 1 above
]

This is done for programmer convenience. When it comes time to iterate through result set rows, referring to instances by their alias instead of offset position can be quite useful.

JOQULAR.select(projection)

Returns a Select instance configured to use the provided projection. The projection is a 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}).exec();
// 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: {<anyvalidkey>: <constructor> [, <anyvalidkey>: <constructor>, ...]}, 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, whereas 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

The special top level keys eq and neq prevent objects from matching themselves, e.g.

JOQULAR.select().from({p1: Person, p2: Person}).where({p2: {neq: p1}}).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().from({p1: Person, p2: Person}).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'}],
            [{name:'Joe',age:24,gender:'male'},{name:'Joe',age:24,gender:'male'}],
            [{name:'Jo',age:24,gender:'female'},{name:'Jo',age:24,gender:'female'}]
           ]

Joins are created by using an alias reference inside what would otherwise be a literal predicate test. The example below finds partners of the opposite gender for Bill:

JOQULAR.select().from({p1: Person, p2: Person}).where({p1: {name: 'Bill'}, p2: {gender: {neq: {p1: 'gender'}}}}).exec();

Note, using patterns with aliases that are not qualified by literal matches are computationally expensive since they cause the equivalent of a table scan. Also, try not to use neq: null.

Select.prototype.first(number)

Configures a Select instance to return the first number rows ans returns the instance.

Select.prototype.last(number)

Configures a Select instance to return the last number rows and returns the instance.

Select.prototype.sample(sizeOrConfidenceLevel,randomizeOrMarginOfError,randomize)

Sampling assumes a random distribution so it is conducted after before orderBy, first, and last, regardless of the order they were specified in creating the Select instance. In terms underlying storage, JOQULAR stores object references in the order they are created. Hence, if you load data into JOQULAR in a pre-sorted order sampling may create skewed results. If you want to ensure a random distribution, then set randomizeOrMarginOfError or randomize to true. If this is done, underlying results will be sorted into a random order before sample selection. You can still return them in some ordered manner using orderBy.

If sizeOrConfidenceLevel is >= 1 and randomizeOrMarginOfError is undefined, null, true or false the results set will be a set of records randomly selected from the underlying results with maximum count of sizeOrConfidenceLevel. If sizeOrConfidenceLevel is greater than the underlying row count, then all records are returned.

If sizeOrConfidenceLevel is < 1 and > 0, i.e. a fraction, and randomizeOrMarginOfError is undefined, null, true or false the results set will be a set of records randomly selected from the underlying results that represent a percentage of the underlying results, e.g. .10 gives 10% of the underlying results.

If randomizeOrMarginOfError is a number, then sizeOrConfidenceLevel is assumed to be a confidence level expressed as a decimal. The third argument now becomes the one to direct randomization prior to sample selection. Sufficient rows will be returned to meet the confidence level and margin of error, if they exist. Otherwise, all rows will be returned.

Note: There is limited error handling on this polymorphic function at this time, so be careful with the arguments.

Select.prototype.orderBy(ordering)

Configures the underlying Select instance to use the specified ordering and returns the instance. The ordering takes the form, {<aliased path>: <direction> [,<aliased path>: <direction>...]}. aliased_path is a . delimited path starting with an alias reference. direction is either asc or desc. If a projection is specified, the projection keys are not used. All sorting must be done on direct paths to matched objects.

// valid
JOQULAR.select().from({p1: Person}).orderBy({'p1.address.zipcode':'asc','p1.name':'asc'}).exec();
// valid 
JOQULAR.select({name:'p1.name'}).from({p1: Person}).orderBy({'p1.address.zipcode':'asc','p1.name':'asc'}).exec();
// not valid
JOQULAR.select({name:'p1.name'}).from({p1: Person}).orderBy({'p1.address.zipcode':'asc','name':'asc'}).exec();

Select.prototype.exec(optionalBoolOrCallback)

Executes the Select and returns an Array of Arrays for the rows of the results. The call is synchronous unless optionalBoolOrCallback is provided. If optionalBoolOrCallback is true, a Promise is returned which will resolve to the results. If optionalBoolOrCallback is a function, it will be called with the results as the second argument when the query completes.

See the Introduction to this page for a detailed description of the results.

Add a custom footer
Add a custom sidebar
Clone this wiki locally