### Exposed 0.56.0 Help

# 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: 30 October 2024