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 :