Querying data

Working with `where` expressions

The query expression where expects a boolean operator (Op<Boolean>). We've split the allowed conditions in different categories:

Basic conditions.
Logical conditions.
Conditions that check for a match in a pattern.
Conditions that check for a match in a range.
Conditions that check for a match in a collection.

Basic conditions

eq: Checks if an expression is equal to some value.
neq: Checks if an expression is not equal to some value.
isNull(): Returns true if this expression is null, false otherwise.
isNotNull(): Returns false if this expression is null, true otherwise.
less: Checks if an expression is less than some value.
lessEq: Checks if an expression is less than or equal to some value.
greater: Checks if an expression is greater than some value.
greaterEq: Checks if an expression is greater than or equal to some value.
exists: Checks if a subquery returns at least one row.
notExists: Checks if a subquery does not return any row.
isDistinctFrom: Checks if this expression is not equal to another value, with null treated as a comparable value.
isNotDistinctFrom: Checks if an expression is equal to another value, with null treated as a comparable value.

Logical conditions

not: Returns the inverse of an expression.
and: Returns the result of performing a logical and operation between two expressions.
or: Returns the result of performing a logical or operation between two expressions.
andIfNotNull: Returns the result of performing a logical and operation between two expressions if the second one is not null.
orIfNotNull: Returns the result of performing a logical or operation between two expressions if the second is not null.
compoundAnd(): Reduces a list to a single expression by performing an and operation between all the expressions in the list.
compoundOr(): Reduces a list to a single expression by performing an or operation between all the expressions in the list.

Check for a match in a pattern

like

Checks if an expression matches the specified pattern.

StarWarsFilms.selectAll().where { StarWarsFilms.name like "The %" }

notLike

Checks if an expression doesn't match the specified pattern.

regexp

Checks if an expression matches a regular expression.

                        StarWarsFilms.selectAll().where { StarWarsFilms.name regexp "^The(\\s\\w+){2}\$" }
                    

match

Checks whether an expression matches a given pattern based on a specific mode.

Supported only on MySQL and MariaDB.

Check for a match in a range

between(from: T, to: T): Checks if an expression is between the values from and to. Returns true if the expression is between the lower and upper range values (inclusive). Date and time values are also supported as arguments.
StarWarsFilms.selectAll().where { StarWarsFilms.sequelId.between(4, 6) }

Check for a match in a collection

inList

Checks if an expression is equal to any element from list.

StarWarsFilms.selectAll().where { StarWarsFilms.sequelId inList listOf(6, 4) }

inList also accepts multiple expressions to check for equality, either as a Pair or a Triple:

                        val topRated = listOf(5 to "Empire Strikes Back", 4 to "A New Hope")
                        StarWarsFilms.selectAll().where {
                            StarWarsFilms.sequelId to StarWarsFilms.name inList topRated
                        }
                    

notInList

Checks if an expression is not equal to any element in the provided collection.

In addition to the IN operator, the ANY and ALL operators are available with any preceding comparison operator:

                StarWarsFilms.selectAll().where { StarWarsFilms.sequelId eq anyFrom(arrayOf(6, 4)) }
            

anyFrom() and allFrom() also accept subqueries, tables, and array expressions as arguments.

Conditional `where`

It is a rather common case to have a query with a where clause that depends on some other code's conditions. Moreover, independent or nested conditions could make it more complicated to prepare such where clauses.

Let's imagine that we have a form on a website where a user can optionally filter "Star Wars" films by a director and/or a sequel.

            val query = StarWarsFilms.selectAll()
            directorName?.let {
                query.andWhere { StarWarsFilms.director eq it }
            }
            sequelId?.let {
                query.andWhere { StarWarsFilms.sequelId eq it }
            }
        

But what if we want to conditionally select from another table and join it only when a condition is true? You have to use adjustColumnSet and adjustSelect functions, which allow to extend and modify join and select parts of a query (see kdoc on that functions):

            actorName?.let {
                query.adjustColumnSet { innerJoin(Actors, { StarWarsFilms.sequelId }, { Actors.sequelId }) }
                    .adjustSelect { select(fields + Actors.columns).set }
                    .andWhere { Actors.name eq actorName }
            }
        

Aggregating and sorting data

Count

count() is a method of Query that is used like in the example below:

                val count = StarWarsFilms.selectAll().where { StarWarsFilms.sequelId eq 8 }.count()
            

Order by

orderBy() accepts a list of columns mapped to boolean indicates if sorting should be ascending or descending. Example:

StarWarsFilms.selectAll().orderBy(StarWarsFilms.sequelId to SortOrder.ASC)

Group by

In groupBy, define fields and their functions by the select() method.

                StarWarsFilms
                    .select(StarWarsFilms.sequelId.count(), StarWarsFilms.director)
                    .groupBy(StarWarsFilms.director)
            

Available field functions are: count, sum, average, min and max.

Limiting result sets

You can use the limit function to prevent loading large data sets or to accomplish pagination by using the offset function.

            // Take 2 films after the first one.
            StarWarsFilms
                .selectAll()
                .where { StarWarsFilms.sequelId eq Actors.sequelId }
                .limit(2)
                .offset(1)
        

Joining tables

Join

For the join examples below, consider the following tables:

                object StarWarsFilms : IntIdTable() {
                    val sequelId: Column<Int> = integer("sequel_id").uniqueIndex()
                    val name: Column<String> = varchar("name", 50)
                    val director: Column<String> = varchar("director", 50)
                }
                object Actors : IntIdTable() {
                    val sequelId: Column<Int> = integer("sequel_id").uniqueIndex()
                    val name: Column<String> = varchar("name", 50)
                }
                object Roles : Table() {
                    val sequelId: Column<Int> = integer("sequel_id")
                    val actorId: Column<EntityID<Int>> = reference("actor_id", Actors)
                    val characterName: Column<String> = varchar("name", 50)
                }
            

Join to count how many actors star in each movie:

                Actors.join(StarWarsFilms, JoinType.INNER, onColumn = Actors.sequelId, otherColumn = StarWarsFilms.sequelId)
                    .select(Actors.name.count(), StarWarsFilms.name)
                    .groupBy(StarWarsFilms.name)
            

Instead of specifying onColumn and otherColumn, additionalConstraint can be used (and allows specifying other types of join conditions).

                Actors.join(
                    StarWarsFilms,
                    JoinType.INNER,
                    additionalConstraint = { StarWarsFilms.sequelId eq Actors.sequelId })
                    .select(Actors.name.count(), StarWarsFilms.name)
                    .groupBy(StarWarsFilms.name)
            

When joining on a foreign key, the more concise innerJoin can be used:

                (Actors innerJoin Roles)
                    .select(Roles.characterName.count(), Actors.name)
                    .groupBy(Actors.name)
                    .toList()
            

This is equivalent to the following:

                Actors.join(Roles, JoinType.INNER, onColumn = Actors.id, otherColumn = Roles.actorId)
                    .select(Roles.characterName.count(), Actors.name)
                    .groupBy(Actors.name)
                    .toList()
            

Union

You can combine the results of multiple queries using .union(...). Per the SQL specification, the queries must have the same number of columns, and not be marked for update. Subqueries may be combined when supported by the database.

                val lucasDirectedQuery =
                    StarWarsFilms.select(StarWarsFilms.name).where { StarWarsFilms.director eq "George Lucas" }
                val abramsDirectedQuery =
                    StarWarsFilms.select(StarWarsFilms.name).where { StarWarsFilms.director eq "J.J. Abrams" }
                val filmNames = lucasDirectedQuery.union(abramsDirectedQuery).map { it[StarWarsFilms.name] }
            

Only unique rows are returned by default. Duplicates may be returned using .unionAll().

                val lucasDirectedQuery =
                    StarWarsFilms.select(StarWarsFilms.name).where { StarWarsFilms.director eq "George Lucas" }
                val originalTrilogyQuery =
                    StarWarsFilms.select(StarWarsFilms.name).where { StarWarsFilms.sequelId inList (3..5) }
                val filmNames = lucasDirectedQuery.unionAll(originalTrilogyQuery).map { it[StarWarsFilms.name] }
            

Mapping fields with alias

Aliases allow preventing ambiguity between field names and table names. Use the aliased var instead of original one:

            val filmTable1 = StarWarsFilms.alias("ft1")
            filmTable1.selectAll() // can be used in joins etc'
        

Also, aliases allow you to use the same table in a join multiple times:

            val sequelTable = StarWarsFilms.alias("sql")
            val originalAndSequelNames = StarWarsFilms
                .innerJoin(sequelTable, { StarWarsFilms.sequelId }, { sequelTable[StarWarsFilms.id] })
                .select(StarWarsFilms.name, sequelTable[StarWarsFilms.name])
                .map { it[StarWarsFilms.name] to it[sequelTable[StarWarsFilms.name]] }
        

And they can be used when selecting from sub-queries:

            val starWarsFilms = StarWarsFilms
                .select(StarWarsFilms.id, StarWarsFilms.name)
                .alias("swf")
            val id = starWarsFilms[StarWarsFilms.id]
            val name = starWarsFilms[StarWarsFilms.name]
            starWarsFilms
                .select(id, name)
                .map { it[id] to it[name] }
        

Custom Select Queries

A Query instance, which can be instantiated by calling selectAll() or select() on a Table or Join, has many extension functions for building complex queries. Some of these have already been mentioned above, like where(), groupBy(), and orderBy().

If a SELECT query with a special clause is required, a custom extension function can be implemented to enable its use with other standard queries.

For example, MySQL index hints, which follow the table name in SQL, can be implemented on a SELECT query by using the following custom function and class:

            fun Query.indexHint(hint: String) = IndexHintQuery(this, hint)

            class IndexHintQuery(
                val source: Query,
                val indexHint: String
            ) : Query(source.set, source.where) {

                init {
                    // copies any stored properties from the original query
                    source.copyTo(this)
                }

                override fun prepareSQL(builder: QueryBuilder): String {
                    val originalSql = super.prepareSQL(builder)
                    val fromTableSql = " FROM ${transaction.identity(set.source as Table)} "
                    return originalSql.replace(fromTableSql, "$fromTableSql$indexHint ")
                }

                override fun copy(): IndexHintQuery = IndexHintQuery(source.copy(), indexHint).also { copy ->
                    copyTo(copy)
                }
            }

            transaction {
                val originalQuery = StarWarsFilms
                    .selectAll()
                    .withDistinct()
                    .where { StarWarsFilms.sequelId less 5 }
                    .groupBy(StarWarsFilms.director)

                val queryWithHint = originalQuery
                    .indexHint("FORCE INDEX (PRIMARY)")
                    .orderBy(StarWarsFilms.sequelId)
            }
        

Last modified: 25 September 2024