Architecture

QueryBuilder

QueryBuilder is the most central class of the whole library. One instance of QueryBuilder represents one query that is being generated. An SQL query consists of clauses, each of which corresponds to one field of the respective QueryBuilder instance: scope (for the SELECT clause), from, where, orderBy, limit, offset.

An instance of QueryBuilder is initialized by calling from(TableName) where TableName is an object that extends Table (for details see Database schema definition).

The most important methods that modify the query are listed below. Each of these methods takes a lambda function f: S => T as its only argument, where S <: Scope is a refined scope and T differs for each of the methods.

map (select)

The map method updates the scope of the query. The return type T of f is a subtype of Scope (i.e. the new scope itself). Alternatively, T can be a tuple of NamedExpressions, in which case a TupleScope is automatically created from the tuple.

filter (where)

The filter method updates the WHERE clause of the query. The return type T of f is Expression[Boolean]. If the filter method is called multiple times, the resulting filtering condition will be a conjunction of all the predicates.

sortBy

The sortBy method updates the ORDER BY clause of the query. The return type T of f is either OrderBy or a tuple of OrderBys. OrderBy is either Asc(expression) or Desc(expression).

Scope

Scope is a type that is used in the QueryBuilder to represent the SELECT clause of the query. It is one of:

• Expression for selecting one column or expression;
• TableScope for selecting all columns of a table (i.e. SELECT *);
• TupleScope for arbitrary SELECT clauses.

Besides denoting the selected values of a query, Scope is also used to construct expressions within methods of the QueryBuilder such as map, filter, sortBy.

If the scope is of type TableScope or TupleScope, it represents multiple expressions (NamedExpressions to be precise) and we can access them by their names. If the scope is an Expression, it can be accessed directly. For example:

from(Artists) // scope type is TableScope[Artists.type]
  .map{ a => (a.id, a.name) } // scope type is TupleScope
  .filter(_.name.contains("John")) // accessing a column by its name to construct an expression 
  .map(_.id) // scope type is Expression[Int]
  .filter(_ > 10) // constructing an expression without specifying the column name

TableScope

TableScope[T <: Table] represents all the columns of the respective table T. If there are relationships defined on T, it also enables accessing the corresponding tables. Referring to columns of these related tables implicitly constructs subqueries or joins.

TableScope implements the Selectable trait and it needs to be refined such that the scope members are accessible. Because the relationships are recursive by nature, the refinements must be generated lazily. For example, consider the table Releases and its corresponding TableScope[Releases.type]. The refinement of the scope will have a field tracks: QueryBuilder[TableScope[Tracks.type]] but TableScope[Tracks.type] will not be refined by default. The refinement happens within the methods of QueryBuilder before passing the scope to the modifying lambda function. To this end, we use the following trait:

trait RefinedScope[S <: Scope]:
  type Refined

For each table T <: Table, there is a given instance of RefinedScope[TableScope[T]] generated by a macro. For other scope types S we simply provide a given RefinedScope[S] whose Refined member is S itself. To refine a scope S, we can summon its RefinedScope[S] and cast the scope to the Refined member of the summoned object.

1:M relationships

Accessing the related table constructs a new QueryBuilder and filters on the primary key. For example, consider the following query:

from(Releases).filter(_.tracks.count > 5)

This is equivalent to

from(Releases)
  .filter{ release =>
    from(Tracks)
      .filter(_.releaseId === release.id)
      .count > 5
  }

M:1 relationships

Accessing the related table returns a TableScope[T2] of the target table T2. Here, the relation given to TableScope[T2] is a JoinRelation instead of TableRelation.

M:N relationships

This is a combination of a M:1 and a 1:M relationship. Accessing the related table constructs a new QueryBuilder and filters on the primary key of a joined table.

TupleScope

TupleScope represents an arbitrary selection of values. It is instantiated with a tuple whose values must be of type NamedExpression. It implements the Selectable trait and it must be refined in order for the values to be accessible.

Relation

Relation describes an SQL relation that we can select columns from.

SubqueryRelation

Enables selecting from a subquery. For example, consider the following query:

from(Artists)
  .map{ a => (a.id, a.name) }
  .filter(_.id < 1000) // subquery up until here
  .map(_.name) // main query

The result of the above is an instance of QueryBuilder that selects name from a SubqueryRelation. The query gets translated into the following SQL:

SELECT artists_2.name
FROM (
  SELECT artists_1.id, artists_1.name
  FROM artists artists_1
  WHERE artists_1.id < 1000
) artists_2

TableRelation

Enables accessing a specified table. This can be either through a FROM clause or through a JOIN clause.

FromRelation

The basic relation that corresponds to the FROM clause. For example the following code creates QueryBuilder that selects from FromRelation(Artists).

from(Artists)

JoinRelation

Corresponds to the JOIN clause. Describes both the type of the join (left/right/inner/outer) and the on-condition.