Select

The variations on Select

Select statements return an immutable Query[T] that
is itself a Queryable[T] and a lazy Iterable[T].

The laziness here means that the query is sent to the database only
when starting iteration, or in other words, when Iterable.iterator is called.

The select function takes any legal Scala expression whose
type determines the generic parameter of the Query[R]

The select expression will be evaluated for every row returned by the query.

It is also possible to select with alternative (shorter but less generic) syntax :

Note that the .lookup[K](k: K) i.e. lookup by key method on a Table[T] is only
available for Table[T] that are of the form : Table[KeyedEntity[K]]

The classes Artist and Song in this example are part of a one to many relation
that can be accessed via the methods.

Nesting Sub Queries

For the next examples, the following query will be nested as an inner query
into other queries :

Sub Queries in the From clause :

The from clause takes Queryable[T]’s, a trait of Table[T] View[T] and Query[T].
Notice the Query[Song] funkAndLatinJazz.songsInPlaylistOrder in the from clause :

Sub Queries in the Where clause :

Joins can also be nested in the where clause just like in SQL :

The SQL generated for the above statement is :

Doing a 3 level nested join is by no means necessary and serves
no other purposes than demonstration.

In addition to the in() operator, exists() and notExists() can be used in a
where clause. They correspond to EXISTS and NOT EXISTS in SQL. Any query
nested with in(), exists(), or notExists() can also refer to queries in an
outer scope.

For example:

The SQL generated for the above statement is :

Select Distinct

Calling the .distinct method on a Query[] creates a copy of it that has a ‘distinct’ select clause :

For Update

Calling the .forUpdate method on a Query[] creates a copy of it that has a ‘forUpdate’ locking directive :