Query

Queries

.all()

Every entity defined in SQLstore has one method to retrieve all persisted instances:

store.defineEntity("Author", {
    "properties": {
        "name": "string"
    }
});
// ... create and persist instances
var authors = Author.all();

.all() is a convenient equivalent of store.query("from Author") (see below).

SQLstore query language

SQLstore's query language is very similar to plain SQL, the only differences are:

• Instead of referencing tables and columns, entities and properties are used
• Values inside a query are referenced by name, not by position
• Queries can return entities and/or entity values

Entity and property names are case sensitive, everything else in a query is case insensitive.

Querying

All queries are issued by calling the store's instance method query(str). In difference to plain SQL the select part of a query can be omitted when retrieving entity instances:

store.query("from Author")

is equivalent to

store.query("select Author from Author")

and will return all persisted instances of the Author entity (in fact, this is the same as calling Author.all()).

Query results

The result of a query is always an Array, even if just one entity or value has been found in the underlying database.

If a single entity or value has been selected (or if the select clause has been omitted as in the first example above), the Array contains the selected entities or values:

store.query("from Author");
// => [[Author#1], [Author#2], [Author#3]]
store.query("select id from Author")
// => [1, 2, 3]

If more than one entity or value has been selected, the result array contains an object for each result, where each selected entity/value is stored in a property whose name equals the part of the select clause:

store.query("select id, name from Author");

returns

[
	{
		"id": 1,
		"name": "John Doe"
	},
	{
		"id": 2,
		"name": "Jane Foo"
	}
]

The same is used when selecting multiple entities at once. In this case the property value is the selected entity instance:

store.query("select Author, Book from Author, Book where Book.author = Author.id");

returns

[
	{
		"Author": [Author#1],
		"Book": [Book#1]
	},
	{
		"Author": [Author#2],
		"Book": [Book#2]
	}
]

Aggressive/eager entity queries

Normally SQLstore loads entity values in a lazy manner, meaning that the resulting entity values are populated on first access. Of course this means that for every entity a separate SQL statement is issued to retrieve the property values. Since this can be quite expensive when eg. looping over large results and accessing a property other than _id of all entities, SQLstore supports a different way of selecting entities:

store.query("select * from Author")

Using a query like the above will immediately populate all entities, so no more queries are needed to retrieve the property values of the resulting entity instances.

Aliasing entities

SQLstore's query language supports aliases in the same manner as used in SQL:

store.query("select a from Author as a")
// the 'as' keyword is optional
store.query("select a from Author a")

Entity aliases should start with a lowercase letter, since this follows the Javascript convention for property names.

The where clause

where-clauses have the same form as in SQL (with the exception that expressions contain property names instead of columns):

// select the author with ID 1:
store.query("from Author where id = 1")

// select all authors whose name starts with "John":
store.query("from Author where name like 'John%'")

// aliasing the entity in the from clause:
store.query("select a.id from Author a where a.name like 'John%'")

SQLstore's query language doesn't support referencing properties of 1:1 mappings of an entity, so the following won't work (assuming that the Book entity has a reference to the appropriate author):

store.query("select b.author.id from Book b")

The where clause can be as simple or complex as known in SQL, including nested parenthesis, arithmetic operations, subqueries, in (…) expressions and the like.

Other clauses

SQLstore's query language supports the following SQL clauses:

• join (inner and left/right outer joins)
• order by
• group by
• having

The syntax for these is the same as in SQL.

Example

let result = store.query("select day, category, min(start) as start " +
        "from Entry " +
        "group by day, category " +
        "having min(start) >= :minDate " +
        "order by min(start) desc", {
    "minDate": someDateObject
});

Offsets and limits

SQLstore provides a uniform way of specifying result ranges, which are internally converted into the database specific form:

 // retrieve the last 10 author instances
 store.query("from Author order by id desc limit 10")
 // retrieve the next 10 instances
 store.query("from Author order by id desc limit 10 offset 10")

Literal values in queries

Literal values (strings, numbers, booleans) in a query can be specified as in Javascript. When parsing queries SQLstore will extract these literals into a parameter array and use this within a Java "prepared statement". The benefit of this is that SQL injection isn't possible (however this shouldn't be an excuse for not sanitizing these values beforehand, think of it as an additional security mechanism).

Named parameters in queries

In contrast to SQL, where values in a prepared statement are referenced by their position in an array of values, SQLstore supports named parameters. These named parameters start with a colon followed by the name of a parameter object's property containing the value to use. This parameter object must be passed as second argument to the store's query() method.

var queryStr = "from Book b where b.releaseDate < :timestamp";
var params = {"timestamp": new Date()}
store.query(queryStr, params)

Using named parameters is especially useful when dealing with values that don't have a literal form (like the Date instance in the example above). As with literal values defined in query statements SQLstore will use a prepared statement in conjuction with a parameter value array when issueing the statement to the underlying database.

Agregation functions

SQLstore supports aggregations using the following functions:

• MAX(…)
• MIN(…)
• SUM(…)
• AVG(…)
• COUNT(…)
• COUNT(DISTINCT …)

Aggregation functions can be used in conjunction with arithmetic operators like

select max(id) + 1 from Author

Aggregations can also be used in having clauses, again in conjunction with arithmetic operators if needed.