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.

We’ve categorized the available conditions into the following groups:

Basic conditions
Logical conditions
Pattern-matching conditions
Range conditions
Collection conditions

Basic conditions

Basic conditions are simple comparisons, such as equality (eq) and inequality (neq).

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

Logical conditions are those that use logical operators, such as AND, OR, and NOT.

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.
not: Returns the inverse of an expression.
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.

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

To check if a value lies within a specified range, use the between() function.

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

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

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.

Imagine a form on a website where a user can optionally filter "Star Wars" films by a director and/or a sequel. To construct such a query, you can use the andWhere function:

    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

The orderBy() function allows you to sort query results by specifying a list of columns and their sort order (ascending or descending).

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

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

        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. Use the aliased var instead of original one:

        val filmTable1 = StarWarsFilmsTable.alias("ft1")

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

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

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

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

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

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

            originalQuery.indexHint("FORCE INDEX (PRIMARY)")
                .orderBy(StarWarsFilmsTable.sequelId)
        }

Last modified: 05 December 2024