Skip to content

JOQULAR Select & Joins

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

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 found 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 arrays 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. POJO key values can optionally be formatted using a built-in or developer specified format function.

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}]]

Coming Soon!

Projection keys can optionally be qualified by a format specificiation. By default, JOQULAR uses AnyWhichWay's stringformatter. This can be changed by simply setting JOQULAR.format to another formatting function that has the argument signature: (formatspecification,value). For example,

// assume a Person with value {name:'Bill',birthdate:'Thu May 21 1993 06:00:09 GMT-0700 (Pacific Daylight Time)'}
JOQULAR.select({name: {p1: 'name'}, birthdate: {p1: 'birthdate', format:'MMMM Do, YYYY'}}).from({p1: Person}).exec();
// returns [[{name:'Bill',birthdate:'May 21st, 1993'}]]

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.

When converted to JSON, the constructor reference are converted to the equivalent string names, so make sure you use named constructors and not anonymous ones.

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 and 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 sequence they were specified in creating the Select instance. Internally, 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.

// returns 10 randomly selected Persons based on the order they are retrieved
JOQULAR.select().sample(10).from({p1: Person}).exec();

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.

// return last a random 10% of records matched which are chosen after the retrieved records are randomized
JOQULAR.select().sample(.10,true).from({p1: Person}).exec();

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.

// return last a statistical sample of records that have a 95% probability of being representative +/- 3%
// randomize results before choosing the sample
JOQULAR.select().sample(.95,.03,true).from({p1: Person}).exec();

Note: There is no argument type 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. Unlike joqularFind, exec does not wait for currently running index efforts to complete. Just like joqularFind, exec is sufficiently fast that asynchronous processing is not required at this time. The calling flexibility is provided to future proof the software for delays that will undoubtedly occur once a remote back-end is attached to JOQULAR.

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

Select.prototype.toJSON()

See the JSON.stringify example in the Introduction to this page.

Currently, predicates that take functions as arguments, i.e. $$ and $, as well as the special values Infinity, -Infinity, NaN do not convert to JSON. We have a TypedJSON class we will release as a plugin in the future to address this and other JSON serialization/transport issues.

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