Querying data

Working with `where` expressions

The where expression is a core component of building queries in Exposed, allowing you to filter data by specifying conditions. A where expression expects a boolean operator (Op<Boolean>), which evaluates to either true or false.

Pattern-matching conditions

Conditions that allow you to check for a match in a pattern using operators, such as LIKE.

like

Checks if an expression matches the specified pattern.

val allMoviesLike = StarWarsFilmsTable.selectAll()
    .where { StarWarsFilmsTable.name like "The %" }

notLike

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

val allMoviesNotLike = StarWarsFilmsTable.selectAll()
    .where { StarWarsFilmsTable.name notLike "The %" }

regexp

Checks if an expression matches a regular expression.

val allMatchingRegex = StarWarsFilmsTable.selectAll()
    .where { StarWarsFilmsTable.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.

Range conditions

It 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.

val allBetween = StarWarsFilmsTable.selectAll()
    .where { StarWarsFilmsTable.sequelId.between(MOVIE_ORIGINAL_ID, MOVIE_SEQUEL_2_ID) }

Collection conditions

To check if an expression is equal or not to any element from a list, use the inList or notInList operators.

Using `inList` with lists

The inList operator checks if an expression matches any value in a list. In the following example, the query selects all films with sequelId values of 6 or 4.

val allInList = StarWarsFilmsTable.selectAll()
    .where { StarWarsFilmsTable.sequelId inList listOf(MOVIE_SEQUEL_2_ID, MOVIE_ORIGINAL_ID) }

Using `inList` with Pairs or Triples

The inList operator can also handle multiple expressions, such as pairs or triples, to check for equality across multiple columns.

val topRated = listOf(MOVIE_SEQUEL_ID to "Empire Strikes Back", MOVIE_ORIGINAL_ID to "A New Hope")

val multipleInList = StarWarsFilmsTable.selectAll()
    .where {
        StarWarsFilmsTable.sequelId to StarWarsFilmsTable.name inList topRated
    }

Using `ANY` and `ALL`

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

val anyFromArray = StarWarsFilmsTable.selectAll()
    .where {
        StarWarsFilmsTable.sequelId eq anyFrom(arrayOf(MOVIE_SEQUEL_2_ID, MOVIE_ORIGINAL_ID))
    }

Conditional `WHERE`

When working with SQL databases, it is a rather common case to have a query with a WHERE clause that depends on certain conditions. These conditions often come from application logic or user input. Managing such conditions can become complex, especially with independent or nested conditions.

fun findWithConditionalWhere(directorName: String?, sequelId: Int?) {
    val query = StarWarsFilmsTable.selectAll()

    // Add conditions dynamically
    directorName?.let {
        query.andWhere { StarWarsFilmsTable.director eq it }
    }
    sequelId?.let {
        query.andWhere { StarWarsFilmsTable.sequelId eq it }
    }
}

In the above example, the query starts with selectAll(), which retrieves all rows from the StarWarsFilmsTable. Then, the let function is used to ensure that conditional WHERE clauses for the directorName and sequelId are applied only if values are provided.

Conditional joins

Sometimes, you may need to modify not just the WHERE clause but also the underlying table joins based on certain conditions. For example, filtering by an actor's name might require joining ActorsTable conditionally.

In these cases, you can use the .adjustColumnSet() and .adjustSelect() functions, which allow to extend and modify JOIN and SELECT parts of a query:

fun findWithConditionalJoin(actorName: String?) {
    transaction {
        val query = StarWarsFilmsTable.selectAll() // Base query

        // Conditionally adjust the query
        actorName?.let { name ->
            query.adjustColumnSet {
                innerJoin(ActorsTable, { StarWarsFilmsTable.sequelId }, { ActorsTable.sequelId })
            }
                .adjustSelect {
                    select(StarWarsFilmsTable.columns + ActorsTable.columns)
                }
                .andWhere { ActorsTable.name eq name }
        }
    }
}

Aggregating and sorting data

Count

The .count() method is used to determine the number of rows matching a query. It is called on a Query object and returns the total count of rows.

The following example counts all rows where sequelId equals 8:

val count = StarWarsFilmsTable.selectAll()
    .where {
        StarWarsFilmsTable.sequelId eq MOVIE_SEQUEL_3_ID
    }
    .count()

Order by

In the following example, StarWarsFilmsTable.sequelId specifies the column to sort by and SortOrder.ASC sets the sort order to be ascending:

val sortedFilms = StarWarsFilmsTable.selectAll()
    .orderBy(StarWarsFilmsTable.sequelId to SortOrder.ASC)

Group by

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

val groupedFilms = StarWarsFilmsTable
    .select(StarWarsFilmsTable.sequelId.count(), StarWarsFilmsTable.director)
    .groupBy(StarWarsFilmsTable.director)

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

Limiting result sets

val limitedFilms = StarWarsFilmsTable
    .selectAll()
    .where { StarWarsFilmsTable.sequelId eq ActorsTable.sequelId }
    .limit(2)
    .offset(1)

Mapping fields with alias

Aliases allow preventing ambiguity between field names and table names. To use the aliased var instead of the original one, use the .alias() function:

val filmTable1 = StarWarsFilmsTable.alias("ft1")

val allFilms = filmTable1.selectAll() // can be used in joins etc'

val sequelTable = StarWarsFilmsTable.alias("sql")

val originalAndSequelNames = StarWarsFilmsTable
    .join(sequelTable, JoinType.INNER, StarWarsFilmsTable.sequelId, sequelTable[StarWarsFilmsTable.id])
    .select(StarWarsFilmsTable.name, sequelTable[StarWarsFilmsTable.name])
    .map { it[StarWarsFilmsTable.name] to it[sequelTable[StarWarsFilmsTable.name]] }

val starWarsFilms = StarWarsFilmsTable
    .select(StarWarsFilmsTable.id, StarWarsFilmsTable.name)
    .alias("swf")

val id = starWarsFilms[StarWarsFilmsTable.id]

val name = starWarsFilms[StarWarsFilmsTable.name]

val allStarWarsFilms = 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:

import org.jetbrains.exposed.sql.Query
import org.jetbrains.exposed.sql.QueryBuilder
import org.jetbrains.exposed.sql.Table

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)
    }
}

val originalQuery = StarWarsFilmsIntIdTable
    .selectAll()
    .withDistinct()
    .where { StarWarsFilmsIntIdTable.sequelId less MOVIE_SEQUEL_ID }
    .groupBy(StarWarsFilmsIntIdTable.id)

originalQuery.indexHint("FORCE INDEX (PRIMARY)")
    .orderBy(StarWarsFilmsIntIdTable.sequelId)
    .forEach { println(it[StarWarsFilmsIntIdTable.name]) }

Querying data﻿

Working with where expressions﻿

Basic conditions﻿

Logical conditions﻿

Pattern-matching conditions﻿

Range conditions﻿

Collection conditions﻿

Using inList with lists﻿

Using inList with Pairs or Triples﻿

Using ANY and ALL﻿

Conditional WHERE﻿

Conditional joins﻿

Aggregating and sorting data﻿

Count﻿

Order by﻿

Group by﻿

Limiting result sets﻿

Mapping fields with alias﻿

Custom Select Queries﻿