← Data Definition Circumflex ORM Documentation Data Manipulation →

Querying

A precise request for information retrieval from database is often refered to as query. There are various ways you can query your data with Circumflex ORM:

All data retrieval queries derive from the SQLQuery[T] class. It defines following methods for query execution:

Select Queries

Select queries are used to retrieve records or arbitrary projections with neat object-oriented DSL which closely resembles SQL syntax:

// prepare relation nodes which will participate in query:
val co = Country AS "co"
val ci = City AS "ci"
// prepare a query:
val q = SELECT (co.*) FROM (co JOIN ci) WHERE (ci.name LIKE "Lausanne") ORDER_BY (co.name ASC)
// execute a query:
q.list    // returns Seq[Country]

The Select class provides functionality for select queries. It has following structure:

Relation Nodes

RelationNode wraps a Relation with an alias so that it can be a part of FROM clause of database query.

Relation nodes are represented by the RelationNode class, they are created by calling the AS method of Relation:

val co = Country AS "co"
// fetch all countries
SELECT (co.*) FROM (co) list

A handy map method can be used to make code a bit clearer:

// fetch all countries
(Country AS "CO").map(co => SELECT (co.*) FROM (co) list)

Relation nodes can be organized into query trees using joins.

Projections

Projection reflects the type of data returned by query. Generally, it consists of expression which can be understood in the SELECT clause of database and a logic to translate the corresponding part of result set into specific type.

Projections are represented by the Projection[T] trait, where T denotes to the type of objects which should be read from result set. Projections which only read from single database column are refered to as atomic projections, they are subclassed from the AtomicProjection trait. Projections which span across multiple database columns are refered to as composite projections, they are subclassed from the CompositeProjection trait and consist of one or more subProjections.

The most popular projection is RecordProjection, it is designed to retrieve records. The * method of RelationNode returns a corresponding RecordProjection for relation.

You can also query single fields, Field is converted to FieldProjection implicitly when called against RelationNode:

val ci = City AS "ci"
(SELECT (ci.id) FROM ci).list      // returns Seq[Long]
(SELECT (ci.name) FROM ci).list    // returns Seq[String]

You can also query a pair of two projections with following syntax:

val co = Country AS "co"
val ci = City AS "ci"
SELECT (ci.* -> co.*) FROM (co JOIN ci) list    // returns Seq[(Option[City], Option[Country])]

Another useful projection is AliasMapProjection:

val co = Country AS "co"
val ci = City AS "ci"
SELECT(ci.* AS "city", co.* AS "country").FROM(co JOIN ci).list    // returns Seq[Map[String, Any]]

In this example the query returns a set of maps. Each map contains a City record under city key and a Country record under the country key. The SELECT clause accepts arbitrary quantity of projections.

You can even use arbitrary expression which your database understands as long as you specify the expected type:

SELECT(expr[java.util.Date]("current_timestamp")).unique   // returns Option[java.util.Date]

There are also some predefined projection helpers for your convenience:

For example, following snippet will return the count of records in the City table:

(City AS "ci").map(ci => SELECT(COUNT(ci.id)).FROM(ci).unique)

You can easily implement your own projection helper. For example, if you use SQL substring function frequently, you can «teach» Circumflex ORM to select substrings.

Here's the code you should place somewhere in your library (or utility singleton):

object MyOrmUtils {
  def SUBSTR(f: TextField, from: Int = 0, length: Int = 0) = {
    var sql = "substring(" + f.name
    if (from > 0) sql += " from " + from
    if (length > 0) sql += " for " + length
    sql += ")"
    new ExpressionProjection[String](sql)
  }
}

And here's the code to use it:

import MyOrmUtils._
(Country AS "co")
    .map(co => SELECT(SUBSTR(co.code, 1, 1)).FROM(co).list)   // returns Seq[String]

Predicates

Predicate is a parameterized expression which is resolved by database into a boolean-value function. Generally, predicates are used inside WHERE or HAVING clauses of SQL queries to filter the rows in result set.

Predicates are represented by the Predicate class. The easiest way to compose a Predicate instance is to use implicit conversion from String or Field to SimpleExpressionHelper and call one of it's methods:

SELECT (co.*) FROM (co) WHERE (co.name LIKE "Switz%")

Following helper methods are available in SimpleExpressionHelper:

Group Method SQL equivalent
Comparison operators EQ(value: Any) = ?
NE(value: Any) <> ?
GT(value: Any) > ?
GE(value: Any) >= ?
LT(value: Any) < ?
LE(value: Any) <= ?
BETWEEN(lower: Any, upper: Any) BETWEEN ? AND ?
Null handling IS_NULL IS NULL
IS_NOT_NULL IS NOT NULL
Subqueries IN(query: SQLQuery[_]) IN (SELECT ...)
NOT_IN(query: SQLQuery[_]) NOT IN (SELECT ...)
EQ_ALL(query: SQLQuery[_]) = ALL (SELECT ...)
NE_ALL(query: SQLQuery[_]) <> ALL (SELECT ...)
GT_ALL(query: SQLQuery[_]) > ALL (SELECT ...)
GE_ALL(query: SQLQuery[_]) >= ALL (SELECT ...)
LT_ALL(query: SQLQuery[_]) < ALL (SELECT ...)
LE_ALL(query: SQLQuery[_]) <= ALL (SELECT ...)
EQ_SOME(query: SQLQuery[_]) = SOME (SELECT ...)
NE_SOME(query: SQLQuery[_]) <> SOME (SELECT ...)
GT_SOME(query: SQLQuery[_]) > SOME (SELECT ...)
GE_SOME(query: SQLQuery[_]) >= SOME (SELECT ...)
LT_SOME(query: SQLQuery[_]) < SOME (SELECT ...)
LE_SOME(query: SQLQuery[_]) <= SOME (SELECT ...)
Miscellaneous LIKE(value: Any) LIKE ?
ILIKE(value: Any) ILIKE ?
IN(params: Any*) IN (?, ?, ...)

You can combine several predicates into AggregatePredicate using either OR or AND methods:

AND(co.name LIKE "Switz%", co.code EQ "ch")
// or in infix notation:
(co.name LIKE "Switz%") OR (co.code EQ "ch")

You can negotiate a predicate using the NOT method:

NOT(co.name LIKE "Switz%")

String values are implicitly converted into SimpleExpression predicate without parameters:

SELECT (co.*) FROM (co) WHERE ("co.code like 'ch'"))

You can also use prepareExpr to compose a custom expression with parameters:

prepareExpr("co.name like :name or co.code like :code", "name" -> "Switz%", "code" -> "ch")

Ordering

Ordering expressions appear in ORDER_BY clause of Select, they determine how rows in result set will be sorted. The easiest way to specify ordering expressions is to use implicit convertions from String or Field into Order:

SELECT (co.*) FROM (co) ORDER_BY (co.name)

You can also add either ASC or DESC ordering specificator to explicitly set the direction of sorting:

SELECT (co.*) FROM (co) ORDER_BY (co.name ASC)

If no specificator given, ascending sorting is assumed by default.

Joins

Joins are used to combine records from two or more relations within a query.

Joins concept is a part of [relational algebra][rel-algebra-wiki]. If you are not familiar with joins in relational databases, consider spending some time to learn a bit about them. A good place to start will be the Join_(SQL) article on Wikipedia.

Joins allow you to build queries which span across several associated relations:

val co = Country AS "co"
val ci = City AS "ci"
// find cities by the name of their corresponding countries:
SELECT (ci.*) FROM (ci JOIN co) WHERE (co.name LIKE 'Switz%')

As the example above shows, joins are intended to be used in the FROM clause of query. The result of calling the JOIN method is an instance of JoinNode class:

val co2ci = (Country AS "co") JOIN (City AS "ci")   // JoinNode[Country, City]

Every JoinNode has it's left side and right side (co JOIN ci is not equivalent to ci JOIN co).

Left Associativity

An important thing to know is that the join operation is left-associative: if join is applied to JoinNode instance, the operation will be delegated to the left side of JoinNode.

To illustrate this, let's take three associated tables, Country, City and Street:

val co = Country AS "co"
val ci = City AS "ci"
val st = Street AS "st"

We want to join them in following order: Country → (City → Street). Since join operation is left-associative, we need extra parentheses:

co JOIN (ci JOIN st)

Now let's join the same tables in following order: (City → Street) → Country. In this case the parentheses can be omitted:

ci JOIN st JOIN co

Joining Predicate

By default Circumflex ORM will try to determine joining predicate (the ON subclause) by searching the associations between relations.

Let's say we have two associated relations, Country and City. We can use implicit joins between Country and City:

Country AS "co" JOIN (City AS "ci")
// country AS co LEFT JOIN city AS ci ON ci.country_code = co.code
City AS "ci" JOIN (Country AS "co")
// city AS ci LEFT JOIN country AS co ON ci.country_code = co.code

However, if no explicit association exist between relations (or if they are ambiguous), you may need to specify the join predicate explicitly:

ci.JOIN(co).ON("ci.country_code = co.code")

Join Types

Like in SQL, joins can be of several types. Depending on the type of join, rows which do not match the joining predicate will be eliminated from one of the sides of join. Following join types are available:

If no join type specified explicitly, LEFT join is assumed by default.

You can specify the type of join by passing an argument to the JOIN method:

(Country AS "co").JOIN(City AS "ci", INNER)

Or you may call one of specific methods instead:

Country AS "co" INNER_JOIN (City AS "ci")
Country AS "co" LEFT_JOIN (City AS "ci")
Country AS "co" RIGHT_JOIN (City AS "ci")
Country AS "co" FULL_JOIN (City AS "ci")

Grouping & Having

A query can optionally condense into a single row all selected rows that share the same value for a subset of query projections. Such queries are often refered to as grouping queries and the projections are usually refered to as grouping projections.

Grouping queries are built using the GROUP_BY clause:

SELECT (co.*) FROM co GROUP_BY (co.*)

As the example above shows, grouping projections are specified as arguments to the GROUP_BY method.

Grouping queries are often used in conjunction with aggregate functions. If aggregate functions are used, they are computed across all rows making up each group, producing separate value for each group, whereas without GROUP_BY an aggregate produces a single value computed across all the selected rows:

val co = Country AS "co"
val ci = City AS "ci"
// how many cities correspond to each selected country?
SELECT (co.* -> COUNT(ci.id)) FROM (co JOIN ci) GROUP_BY (co.*)

Groups can be optionally filtered using the HAVING clause. It accepts a predicate:

SELECT (co.* -> COUNT(ci.id)) FROM (co JOIN ci) GROUP_BY (co.*) HAVING (co.code LIKE "c_")

Note that HAVING is different from WHERE: WHERE filters individual rows before the application of GROUP_BY, while HAVING filters group rows created by GROUP_BY.

Limit & Offset

The LIMIT clause specifies the maximum number of rows a query will return:

// select 10 first countries:
SELECT (co.*) FROM co LIMIT 10

The OFFSET clause specifies the number of rows to skip before starting to return results. When both are specified, the amount of rows specified in the OFFSET clause is skipped before starting to count the maximum amount of returned rows specified in the LIMIT clause:

// select 5 countries starting from 10th:
SELECT (co.*) FROM co LIMIT 5 OFFSET 10

Note that query planners in database engines often take LIMIT and OFFSET into account when generating a query plan, so you are very likely to get different row orders for different LIMIT/OFFSET values. Thus, you should use explicit ordering to achieve consistent and predictable results when selecting different subsets of a query result with LIMIT/OFFSET.

Union, Intersect & Except

Most database engines allow to comine the results of two queries using the set operations. Following set operations are available:

The syntax for using set operations is:

// select the names of both countries and cities in a single result set:
SELECT (co.name) FROM co UNION (SELECT (ci.name) FROM ci)

Set operations can also be nested and chained:

q1 INTERSECT q2 EXCEPT q3
(q1 UNION q2) INTERSECT q3

The queries combined using set operations should have matching projections. Following will not compile:

SELECT (co.*) FROM co UNION (SELECT (ci.*) FROM ci)

Reusing Query Objects

When working with data-centric applications, you often need the same query to be executed with different parameters. The most obvious solution is to build Query objects dynamically:

object Country extends Table[String, Country] {
  def findByCode(code: String): Option[Country] = (this AS "co").map(co =>
      SELECT (co.*) FROM co WHERE (co.code LIKE code) unique)
}

However, you can use named parameters to reuse the same Query object:

object Country extends Table[String, Country] {
  val co = AS("co")
  val byCode = SELECT (co.*) FROM co WHERE (co.code LIKE ":code")
  def findByCode(c: String): Option[Country] = byCode.set("code", c).unique
}

Criteria API

Most (if not all) of your data retrieval queries will be focused to retrieve only one type of records. Criteria API aims to minimize your effort on writing such queries. Following snippet shows three equivalents of the same query:

// Select query:
(Country AS "co").map(co => SELECT (co.*) FROM (co) WHERE (co.name LIKE "Sw%") list)
// Criteria query:
Country.criteria.add(Country.name LIKE "Sw%").list
// or with RelationNode:
co.criteria.add(co.name LIKE "Sw%").list

As you can see, Criteria queries are more compact because boilerplate SELECT and FROM clauses are omitted.

But aside from shortening the syntax, Criteria API offers unique functionality — associations prefetching, which can greatly speed up your application when working with graphs of associated objects.

The Criteria[R] object has following methods for execution:

You can use predicates to narrow the result set. Unlike Select queries, predicates are added to Criteria object using the add method and then are assembled into the conjunction:

co.criteria
    .add(co.name LIKE "Sw%")
    .add(co.code LIKE "ch")
    .list

You can apply ordering using the addOrder method:

co.criteria.addOrder(co.name).addOrder(co.code).list

Also you can add one or more associated relations to the query plan using the addJoin method so that you can specify constraints upon them:

val co = Country AS "co"
val ci = City AS "ci"
co.criteria.addJoin(ci).add(ci.name LIKE "Lausanne").list

Automatic joins are used to update query plan properly. There is no limitation on quantity or depth of joined relations. However, some database vendors have limitations on maximum size of queries or maximum amount of relations participating in a single query.

One serious limitation of Criteria API is that it does not support LIMIT and OFFSET clauses due to the fact that association prefetching normally causes result set to yield more than one row per record. You can still use LIMIT and OFFSET with SQL queries;

Prefetching Associations

When working with associated records you often need a whole graph of associations to be fetched.

Normally associations are fetched eagerly first time they are accessed, but when it is done for every record in a possibly big result set, it would result in significant performance degradation (see the [n + 1 selects problem explained][n+1] blogpost).

With Criteria API you have an option to fetch as many associations as you want in a single query. This technique is refered to as associations prefetching or eager fetching.

To understand how associations prefetching works, let's take a look at the following domain model sample:

class Country extends Record[String, Country] {
  def PRIMARY_KEY = code
  def relation = Country
  val code = "code" VARCHAR(2) DEFAULT("'ch'")
  val name = "name" TEXT
  def cities = inverseMany(City.country)
}

object Country extends Country with Table[String, Country]

class City extends Record[Long, City] with IdentityGenerator[Long, City] {
  def PRIMARY_KEY = id
  def relation = City
  val id = "id".LONG.NOT_NULL.AUTO_INCREMENT
  val name = "name" TEXT
  val country = "country_code".VARCHAR(2).NOT_NULL
      .REFERENCES(Country).ON_DELETE(CASCADE).ON_UPDATE(CASCADE)
}

object City extends City with Table[Long, City]

You see two relations, Country and City. Each city has one associated country, and, conversely, each country has a list of corresponding cities.

Now you wish to fetch all cities with their corresponding countries in a single query:

val cities = City.criteria.prefetch(City.country).list
cities.foreach(c => println(c.country()))   // no selects issued

The example above shows the prefetching for straight associations. Same logic applies to inverse associations prefetching, for example, fetching all countries with their corresponding cities:

val countries = Country.criteria.prefetch(City.country).list
countries.foreach(c => println(c.cities()))   // no selects issued

Okay. Now we totally hear you saying: “How is that really possible?” — so let's explain a bit. Each Criteria object maintains it's own tree of associations, which is used to form the FROM clause of the query (using automatic left-joins) and, eventually, to parse the result set. The data from result set is parsed into chunks and loaded into transaction-scoped cache, which is subsequently used by associations and inverse associations to avoid unnecessary selects.

There is no limitation on quantity or depth of prefetches. However, some database vendors have limitations on maximum size of queries or maximum amount of relations participating in a single query.

← Data Definition Circumflex ORM Documentation Data Manipulation →