Skip to content

Architecture

Jump to bottom
Martin Kučera edited this page Jan 6, 2023 · 11 revisions

The QueryBuilder class is used for building queries. Its fields contain intermediate representations of individual SQL clauses. Textual SQL is generated from a QueryBuilder instance by GenericSqlTranslator. Platform-specific nuances (e.g. using correct quotes for string literals vs. identifiers) are controlled by the Platform trait; the current implementation provides MySqlPlatform and PostgreSqlPlatform.

QueryBuilder

QueryBuilder[S] 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. The generic type S <: Scope is the type of the scope field and it needs to be known at compile time.

An instance of QueryBuilder is initialized by calling from(TableName) where TableName is an object that extends Table (for details see Database schema definition). This creates a fresh instance of QueryBuilder[TableScope[TableName.type]] which is equivalent to the query

SELECT *
FROM table_name

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 which corresponds to the SELECT clause. 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. Because the type of the scope is known at compile time, we can call map multiple times and each time we can access the members of the scope that is returned by the previous call:

from(Releases)
  .map{ r => (r.id, r.title) }
  .map(_.title) // this works but _.genre would fail at compile time

Similarly, we can access the mapped scope in other methods such as filter or sortBy.

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 table.*);
  • 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.

Expression

The Expression[T] class represents an SQL expression of type T. The generic type T is invariant which prevents generating queries in which we compare expressions of different types.

NamedExpression

NamedExpression[T, N] represents an expression that is being referred to by a name. The name is stored in the type-variable N and therefore known at compile time. It can be either ColumnValue which refers to a specific column by its name, or Alias which refers to an expression that is named using the AS SQL command.

from(Artists)
  .map{ _.name.as("artistName") } // _.name is an instance of ColumnValue[String]
  .filter{ _.artistName.contains("John") } // _.artistName is an instance of Alias[String, "artistName"]

CountAll

Represents the SQL expression COUNT(*)

LiteralExpression

Represents an SQL literal.

SubqueryExpression

Represents a subquery expression. For example:

from(Releases).filter(_.tracks.count > 5) // _.tracks.count is of type QueryBuilder, which has an implicit conversion to SubqueryExpression

Operators and functions

There are many algebraic operators (Plus, Minus, etc.), logical operators (And, Or, Not) available. Furthermore, there is a Function[T] which represents an arbitrary SQL function.

Clone this wiki locally