---
title: "Expression builder"
manual: "TYPO3 Explained"
version: "11.5"
permalink: "https://docs.typo3.org/permalink/t3coreapi:database-expression-builder@11.5"
source: "ApiOverview/Database/ExpressionBuilder/Index.rst"
rendered: "2026-10-21T15:30:00+00:00"
---

> [!WARNING]
> **Attention**
>
> TYPO3 v11 has reached end-of-life as of October 31th 2024 and
> is no longer being maintained. Use the version switcher on the top
> left of this page to select documentation for a supported version of TYPO3.
>
> Need more time before upgrading?
> You can purchase Extended Long Term Support (ELTS) for TYPO3 v11 here:
> [TYPO3 ELTS](https://typo3.com/services/extended-support-elts).

# Expression builder {#database-expression-builder}

****Table of Contents****

-   [Introduction](https://docs.typo3.org/permalink/t3coreapi:introduction@11.5)
-   [Basic usage](https://docs.typo3.org/permalink/t3coreapi:basic-usage@11.5)
-   [Junctions](https://docs.typo3.org/permalink/t3coreapi:junctions@11.5)
-   [Comparisons](https://docs.typo3.org/permalink/t3coreapi:comparisons@11.5)
-   [Aggregate Functions](https://docs.typo3.org/permalink/t3coreapi:aggregate-functions@11.5)
-   [Various Expressions](https://docs.typo3.org/permalink/t3coreapi:various-expressions@11.5)

## Introduction {#introduction}

The `\TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder` class is
responsible for dynamically creating SQL query parts for `WHERE` and
`JOIN ON` conditions. Functions like `->min()` may also be used in
`SELECT` parts.

It takes care of building query conditions and ensures that table and column
names are quoted within the created expressions / SQL fragments. It is a facade
to the actual Doctrine DBAL `ExpressionBuilder`.

The expression builder is used in the context of the [query builder](https://docs.typo3.org/permalink/t3coreapi:database-query-builder@11.5) to ensure that queries are built based on the
requirements of the database platform being used.

## Basic usage {#basic-usage}

An instance of the `ExpressionBuilder` is retrieved from the
`QueryBuilder` object:

**EXT:my_extension/Classes/Domain/Repository/MyTableRepository.php**

```php
$expressionBuilder = $queryBuilder->expr();
```

It is good practice not to assign an instance of the `ExpressionBuilder` to
a variable, but to use it directly within the code flow of the query builder
context:

**EXT:my_extension/Classes/Domain/Repository/MyTableRepository.php**

```php
<?php

declare(strict_types=1);

namespace MyVendor\MyExtension\Domain\Repository;

use TYPO3\CMS\Core\Database\Connection;
use TYPO3\CMS\Core\Database\ConnectionPool;

final class MyTableRepository
{
    private const TABLE_NAME = 'tt_content';

    private ConnectionPool $connectionPool;

    public function __construct(ConnectionPool $connectionPool)
    {
        $this->connectionPool = $connectionPool;
    }

    public function findSomething()
    {
        $queryBuilder = $this->connectionPool->getQueryBuilderForTable(self::TABLE_NAME);

        $rows = $queryBuilder
            ->select('uid', 'header', 'bodytext')
            ->from(self::TABLE_NAME)
            ->where(
                // `bodytext` = 'lorem' AND `header` = 'dolor'
                $queryBuilder->expr()->eq(
                    'bodytext',
                    $queryBuilder->createNamedParameter('lorem', Connection::PARAM_STR)
                ),
                $queryBuilder->expr()->eq(
                    'header',
                    $queryBuilder->createNamedParameter('dolor', Connection::PARAM_STR)
                ),
            )
            ->executeQuery()
            ->fetchAllAssociative();

        // ...
    }
}

```

See available [parameter types](https://docs.typo3.org/permalink/t3coreapi:database-connection-parameter-types@11.5).

> [!WARNING]
> It is of crucial importance to quote values correctly to not introduce SQL
> injection attack vectors into your application. See the [according
> section of the query builder](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-create-named-parameter@11.5)
> for details.

## Junctions {#junctions}

<!-- TODO: no Markdown rendering for "versionchanged" -->

The andX() and orX() methods are deprecated and replaced by
and() and or() to match with Doctrine DBAL, which deprecated
these methods.

-   `->and()` conjunction
-   `->or()` disjunction

Combine multiple single expressions with `AND` or `OR`. Nesting is
possible, both methods are variadic and accept any number of arguments, which
are all combined. However, it usually makes little sense to pass zero or only
one argument.

Example to find `tt_content` records:

**EXT:my_extension/Classes/Domain/Repository/MyTableRepository.php**

```php
// use TYPO3\CMS\Core\Database\Connection;

// WHERE
//     (`tt_content`.`CType` = 'list')
//     AND (
//        (`tt_content`.`list_type` = 'example_pi1')
//        OR
//        (`tt_content`.`list_type` = 'example_pi2')
//     )
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$queryBuilder->where(
    $queryBuilder->expr()->eq('CType', $queryBuilder->createNamedParameter('list')),
    $queryBuilder->expr()->or(
        $queryBuilder->expr()->eq(
            'list_type',
            $queryBuilder->createNamedParameter('example_pi1', Connection::PARAM_STR)
        ),
        $queryBuilder->expr()->eq(
            'list_type',
            $queryBuilder->createNamedParameter('example_pi2', Connection::PARAM_STR)
        )
    )
)
```

Read [how to correctly instantiate](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-instantiation@11.5)
a query builder with the connection pool.
See available [parameter types](https://docs.typo3.org/permalink/t3coreapi:database-connection-parameter-types@11.5).

## Comparisons {#comparisons}

A set of methods to create various comparison expressions or SQL functions:

-   `->eq($fieldName, $value)` "equal" comparison `=`
-   `->neq($fieldName, $value)` "not equal" comparison `!=`
-   `->lt($fieldName, $value)` "less than" comparison `<`
-   `->lte($fieldName, $value)` "less than or equal" comparison `<=`
-   `->gt($fieldName, $value)` "greater than" comparison `>`
-   `->gte($fieldName, $value)` "greater than or equal" comparison `>=`
-   `->isNull($fieldName)` "IS NULL" comparison
-   `->isNotNull($fieldName)` "IS NOT NULL" comparison
-   `->like($fieldName, $value)` "LIKE" comparison
-   `->notLike($fieldName, $value)` "NOT LIKE" comparison
-   `->in($fieldName, $valueArray)` "IN ()" comparison
-   `->notIn($fieldName, $valueArray)` "NOT IN ()" comparison
-   `->inSet($fieldName, $value)` "FIND_IN_SET('42', `aField`)"
    Find a value in a comma separated list of values
-   `->notInSet($fieldName, $value)` "NOT FIND_IN_SET('42', `aField`)"
    Find a value not in a comma separated list of values
-   `->bitAnd($fieldName, $value)` A bitwise AND operation `&`

Remarks and warnings:

-   The first argument `$fieldName` is always quoted automatically.
-   All methods that have a `$value` or `$valueList` as second
    argument **must** be quoted, usually by calling
    [$queryBuilder->createNamedParameter()](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-create-named-parameter@11.5)
    or [$queryBuilder->quoteIdentifier()](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-quote-identifier@11.5).

    > [!WARNING]
    > Failing to quote will end up in [SQL injections](https://docs.typo3.org/permalink/t3coreapi:security-sql-injection@11.5)!
-   `->like()` and `->notLike()` values **must** be **additionally**
    quoted with a call to [$queryBuilder->escapeLikeWildcards($value)](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-escape-like-wildcards@11.5) to suppress the special
    meaning of `%` characters from `$value`.

Examples:

**EXT:my_extension/Classes/Domain/Repository/MyTableRepository.php**

```php
// use TYPO3\CMS\Core\Database\Connection;

// `bodytext` = 'foo' - string comparison
->eq('bodytext', $queryBuilder->createNamedParameter('foo'))

// `tt_content`.`bodytext` = 'foo'
->eq('tt_content.bodytext', $queryBuilder->createNamedParameter('foo'))

// `aTableAlias`.`bodytext` = 'foo'
->eq('aTableAlias.bodytext', $queryBuilder->createNamedParameter('foo'))

// `uid` = 42 - integer comparison
->eq('uid', $queryBuilder->createNamedParameter(42, Connection::PARAM_INT))

// `uid` >= 42
->gte('uid', $queryBuilder->createNamedParameter(42, Connection::PARAM_INT))

// `bodytext` LIKE 'lorem'
->like(
    'bodytext',
    $queryBuilder->createNamedParameter(
        $queryBuilder->escapeLikeWildcards('lorem')
    )
)

// `bodytext` LIKE '%lorem%'
->like(
    'bodytext',
    $queryBuilder->createNamedParameter(
        '%' . $queryBuilder->escapeLikeWildcards('lorem') . '%'
    )
)

// usergroup does not contain 42
->notInSet('usergroup', $queryBuilder->createNamedParameter('42'))

// use TYPO3\CMS\Core\Database\Connection;
// `uid` IN (42, 0, 44) - properly sanitized, mind the intExplode and PARAM_INT_ARRAY
->in(
    'uid',
    $queryBuilder->createNamedParameter(
        GeneralUtility::intExplode(',', '42, karl, 44', true),
        Connection::PARAM_INT_ARRAY
    )
)

// use TYPO3\CMS\Core\Database\Connection;
// `CType` IN ('media', 'multimedia') - properly sanitized, mind the PARAM_STR_ARRAY
->in(
    'CType',
    $queryBuilder->createNamedParameter(
        ['media', 'multimedia'],
        Connection::PARAM_STR_ARRAY
    )
)
```

See available [parameter types](https://docs.typo3.org/permalink/t3coreapi:database-connection-parameter-types@11.5).

## Aggregate Functions {#aggregate-functions}

Aggregate functions used in `SELECT` parts, often combined with
`GROUP BY`. The first argument is the field name (or table name / alias
with field name), the second argument is an optional alias.

-   `->min($fieldName, $alias = NULL)` "MIN()" calculation
-   `->max($fieldName, $alias = NULL)` "MAX()" calculation
-   `->avg($fieldName, $alias = NULL)` "AVG()" calculation
-   `->sum($fieldName, $alias = NULL)` "SUM()" calculation
-   `->count($fieldName, $alias = NULL)` "COUNT()" calculation

Examples:

**EXT:my_extension/Classes/Domain/Repository/MyTableRepository.php**

```php
// Calculate the average creation timestamp of all rows from tt_content
// SELECT AVG(`crdate`) AS `averagecreation` FROM `tt_content`
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$result = $queryBuilder
    ->addSelectLiteral(
        $queryBuilder->expr()->avg('crdate', 'averagecreation')
    )
    ->from('tt_content')
    ->executeQuery()
    ->fetchAssociative();

// Distinct list of all existing endtime values from tt_content
// SELECT `uid`, MAX(`endtime`) AS `maxendtime` FROM `tt_content` GROUP BY `endtime`
$statement = $queryBuilder
    ->select('uid')
    ->addSelectLiteral(
        $queryBuilder->expr()->max('endtime', 'maxendtime')
    )
    ->from('tt_content')
    ->groupBy('endtime')
    ->executeQuery();
```

Read [how to correctly instantiate](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-instantiation@11.5)
a query builder with the connection pool.

## Various Expressions {#various-expressions}

### trim() {#trim}

Using the `->trim()` expression ensures that the fields are trimmed at the
database level. The following examples give a better idea of what is possible:

**EXT:my_extension/Classes/Domain/Repository/MyTableRepository.php**

```php
// use TYPO3\CMS\Core\Database\Connection
// use TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder;
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$queryBuilder->expr()->comparison(
    $queryBuilder->expr()->trim($fieldName),
    ExpressionBuilder::EQ,
    $queryBuilder->createNamedParameter('', Connection::PARAM_STR)
);
```

Read [how to correctly instantiate](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-instantiation@11.5)
a query builder with the connection pool.
See available [parameter types](https://docs.typo3.org/permalink/t3coreapi:database-connection-parameter-types@11.5).

The call to `$queryBuilder->expr()-trim()` can be one of the following:

-   `trim('fieldName')`
    results in `TRIM("tableName"."fieldName")`
-   `trim('fieldName', TrimMode::LEADING, 'x')`
    results in `TRIM(LEADING "x" FROM "tableName"."fieldName")`
-   `trim('fieldName', TrimMode::TRAILING, 'x')`
    results in `TRIM(TRAILING "x" FROM "tableName"."fieldName")`
-   `trim('fieldName', TrimMode::BOTH, 'x')`
    results in `TRIM(BOTH "x" FROM "tableName"."fieldName")`

### length() {#length}

The `->length()` string function can be used to return the length of a
string in bytes. The signature of the method signature is `$fieldName`
with an optional alias `->length(string $fieldName, string $alias = null)`:

**EXT:my_extension/Classes/Domain/Repository/MyTableRepository.php**

```php
// use TYPO3\CMS\Core\Database\Connection;
// use TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder;
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$queryBuilder->expr()->comparison(
    $queryBuilder->expr()->length($fieldName),
    ExpressionBuilder::GT,
    $queryBuilder->createNamedParameter(0, Connection::PARAM_INT)
);
```

Read [how to correctly instantiate](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-instantiation@11.5)
a query builder with the connection pool.
See available [parameter types](https://docs.typo3.org/permalink/t3coreapi:database-connection-parameter-types@11.5).