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

> [!WARNING]
> **Attention**
>
> TYPO3 v12 has reached end-of-life as of April 30th 2026 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 v12 here:
> [TYPO3 ELTS](https://typo3.com/services/extended-support-elts).

# Expression builder {#database-expression-builder}

****Table of Contents****

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

-   **class ExpressionBuilder**

    -   *Fully qualified name:* `\TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder`

    ExpressionBuilder class is responsible to dynamically create SQL query parts.

    It takes care building query conditions while ensuring table and column names
    are quoted within the created expressions / SQL fragments. It is a facade to
    the actual Doctrine ExpressionBuilder.

    The ExpressionBuilder is used within the context of the QueryBuilder to ensure
    queries are being build based on the requirements of the database platform in
    use.

## Basic usage {#database-expression-builder-basic-usage}

An instance of the [ExpressionBuilder](https://docs.typo3.org/permalink/t3coreapi:typo3-cms-core-database-query-expression-expressionbuilder@12.4) 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](https://docs.typo3.org/permalink/t3coreapi:typo3-cms-core-database-query-expression-expressionbuilder@12.4) 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';

    public function __construct(
        private readonly 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@12.4).

> [!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@12.4)
> for details.

## Junctions {#database-expression-builder-basic-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
<?php

declare(strict_types=1);

namespace MyVendor\MyExtension\Domain\Repository;

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

final class MyTableRepository
{
    private const TABLE_NAME = 'tt_content';
    public function __construct(private readonly ConnectionPool $connectionPool) {}

    public function findSomething(): QueryBuilder
    {
        // 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),
                ),
            ),
        );
        return $queryBuilder;
    }
}

```

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

## Comparisons {#database-expression-builder-basic-comparisons}

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

-   **class ExpressionBuilder**

    -   *Fully qualified name:* `\TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder`

    -   **eq(string $fieldName, ?mixed $value)**

        Creates an equality comparison expression with the given arguments.

        -   *param $fieldName:* The fieldname. Will be quoted according to database platform automatically.
        -   *param $value:* The value. No automatic quoting/escaping is done.

        *Returns:* `string`

    -   **neq(string $fieldName, ?mixed $value)**

        Creates a non equality comparison expression with the given arguments.

        First argument is considered the left expression and the second is the right expression.
        When converted to string, it will generated a \<left expr> <> \<right expr>. Example:

        > \[php\]
        > // u.id <> 1
        > $q->where($q->expr()->neq('u.id', '1'));

        -   *param $fieldName:* The fieldname. Will be quoted according to database platform automatically.
        -   *param $value:* The value. No automatic quoting/escaping is done.

        *Returns:* `string`

    -   **lt(?string $fieldName, ?mixed $value)**

        Creates a lower-than comparison expression with the given arguments.

        -   *param $fieldName:* The fieldname. Will be quoted according to database platform automatically.
        -   *param $value:* The value. No automatic quoting/escaping is done.

        *Returns:* `string`

    -   **lte(string $fieldName, ?mixed $value)**

        Creates a lower-than-equal comparison expression with the given arguments.

        -   *param $fieldName:* The fieldname. Will be quoted according to database platform automatically.
        -   *param $value:* The value. No automatic quoting/escaping is done.

        *Returns:* `string`

    -   **gt(string $fieldName, ?mixed $value)**

        Creates a greater-than comparison expression with the given arguments.

        -   *param $fieldName:* The fieldname. Will be quoted according to database platform automatically.
        -   *param $value:* The value. No automatic quoting/escaping is done.

        *Returns:* `string`

    -   **gte(string $fieldName, ?mixed $value)**

        Creates a greater-than-equal comparison expression with the given arguments.

        -   *param $fieldName:* The fieldname. Will be quoted according to database platform automatically.
        -   *param $value:* The value. No automatic quoting/escaping is done.

        *Returns:* `string`

    -   **isNull(string $fieldName)**

        Creates an IS NULL expression with the given arguments.

        -   *param $fieldName:* The fieldname. Will be quoted according to database platform automatically.

        *Returns:* `string`

    -   **isNotNull(string $fieldName)**

        Creates an IS NOT NULL expression with the given arguments.

        -   *param $fieldName:* The fieldname. Will be quoted according to database platform automatically.

        *Returns:* `string`

    -   **like(string $fieldName, ?mixed $value)**

        Creates a LIKE() comparison expression with the given arguments.

        -   *param $fieldName:* The fieldname. Will be quoted according to database platform automatically.
        -   *param $value:* Argument to be used in LIKE() comparison. No automatic quoting/escaping is done.

        *Returns:* `string`

    -   **notLike(string $fieldName, ?mixed $value)**

        Creates a NOT LIKE() comparison expression with the given arguments.

        -   *param $fieldName:* The fieldname. Will be quoted according to database platform automatically.
        -   *param $value:* Argument to be used in NOT LIKE() comparison. No automatic quoting/escaping is done.

        *Returns:* `string`

    -   **in(string $fieldName, ?string|array $value)**

        Creates an IN () comparison expression with the given arguments.

        -   *param $fieldName:* The fieldname. Will be quoted according to database platform automatically.
        -   *param $value:* The placeholder or the array of values to be used by IN() comparison.No automatic quoting/escaping is done.

        *Returns:* `string`

    -   **notIn(string $fieldName, ?string|array $value)**

        Creates a NOT IN () comparison expression with the given arguments.

        -   *param $fieldName:* The fieldname. Will be quoted according to database platform automatically.
        -   *param $value:* The placeholder or the array of values to be used by NOT IN() comparison.No automatic quoting/escaping is done.

        *Returns:* `string`

    -   **inSet(string $fieldName, string $value, bool $isColumn = false)**

        Returns a comparison that can find a value in a list field (CSV).

        -   *param $fieldName:* The field name. Will be quoted according to database platform automatically.
        -   *param $value:* Argument to be used in FIND_IN_SET() comparison. No automatic quoting/escaping is done.
        -   *param $isColumn:* Set when the value to compare is a column on a table to activate casting, default: false

        *Returns:* `string`

    -   **notInSet(string $fieldName, string $value, bool $isColumn = false)**

        Returns a comparison that can find a value in a list field (CSV) but is negated.

        -   *param $fieldName:* The field name. Will be quoted according to database platform automatically.
        -   *param $value:* Argument to be used in FIND_IN_SET() comparison. No automatic quoting/escaping is done.
        -   *param $isColumn:* Set when the value to compare is a column on a table to activate casting, default: false

        *Returns:* `string`

    -   **bitAnd(string $fieldName, int $value)**

        Creates a bitwise AND expression with the given arguments.

        -   *param $fieldName:* The fieldname. Will be quoted according to database platform automatically.
        -   *param $value:* Argument to be used in the bitwise AND operation

        *Returns:* `string`

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@12.4)
    or [$queryBuilder->quoteIdentifier()](https://docs.typo3.org/permalink/t3coreapi:database-query-builder-quote-identifier@12.4).

    > [!WARNING]
    > Failing to quote will end up in [SQL injections](https://docs.typo3.org/permalink/t3coreapi:security-sql-injection@12.4)!
-   `->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@12.4) 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@12.4).

## Aggregate functions {#database-expression-builder-basic-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.

-   **class ExpressionBuilder**

    -   *Fully qualified name:* `\TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder`

    -   **min(string $fieldName, ?string $alias = NULL)**

        Creates a MIN expression for the given field/alias.

        -   *param $fieldName:* the fieldName
        -   *param $alias:* the alias, default: NULL

        *Returns:* `string`

    -   **max(string $fieldName, ?string $alias = NULL)**

        Creates a MAX expression for the given field/alias.

        -   *param $fieldName:* the fieldName
        -   *param $alias:* the alias, default: NULL

        *Returns:* `string`

    -   **avg(string $fieldName, ?string $alias = NULL)**

        Creates an AVG expression for the given field/alias.

        -   *param $fieldName:* the fieldName
        -   *param $alias:* the alias, default: NULL

        *Returns:* `string`

    -   **sum(string $fieldName, ?string $alias = NULL)**

        Creates a SUM expression for the given field/alias.

        -   *param $fieldName:* the fieldName
        -   *param $alias:* the alias, default: NULL

        *Returns:* `string`

    -   **count(string $fieldName, ?string $alias = NULL)**

        Creates a COUNT expression for the given field/alias.

        -   *param $fieldName:* the fieldName
        -   *param $alias:* the alias, default: NULL

        *Returns:* `string`

Examples:

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

```php
<?php

declare(strict_types=1);

namespace MyVendor\MyExtension\Domain\Repository;

use Doctrine\DBAL\Exception;
use TYPO3\CMS\Core\Database\ConnectionPool;

final class MyTableRepository
{
    private const TABLE_NAME = 'tt_content';
    public function __construct(private readonly ConnectionPool $connectionPool) {}

    /**
     * Calculate the average creation timestamp of all rows from tt_content
     * SELECT AVG(`crdate`) AS `averagecreation` FROM `tt_content`
     * @return array<mixed>
     * @throws Exception
     */
    public function findAverageCreationTime(): array
    {
        $queryBuilder = $this->connectionPool->getQueryBuilderForTable(self::TABLE_NAME);
        $result = $queryBuilder
            ->addSelectLiteral(
                $queryBuilder->expr()->avg('crdate', 'averagecreation'),
            )
            ->from(self::TABLE_NAME)
            ->executeQuery()
            ->fetchAssociative();
        return $result;
    }

    /**
     * Distinct list of all existing endtime values from tt_content
     * SELECT `uid`, MAX(`endtime`) AS `maxendtime` FROM `tt_content` GROUP BY `endtime`
     * @return array<array<mixed>>
     * @throws Exception
     */
    public function findDistinctiveEndtimeValues(): array
    {
        $queryBuilder = $this->connectionPool->getQueryBuilderForTable(self::TABLE_NAME);
        $result = $queryBuilder
            ->select('uid')
            ->addSelectLiteral(
                $queryBuilder->expr()->max('endtime', 'maxendtime'),
            )
            ->from('tt_content')
            ->groupBy('endtime')
            ->executeQuery()
            ->fetchAllAssociative()
        ;
        return $result;
    }
}

```

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

## Various expressions {#database-expression-builder-basic-various-expressions}

### length() {#length}

-   **class ExpressionBuilder**

    -   *Fully qualified name:* `\TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder`

    -   **length(string $fieldName, ?string $alias = NULL)**

        Creates a LENGTH expression for the given field/alias.

        -   *param $fieldName:* the fieldName
        -   *param $alias:* the alias, default: NULL

        *Returns:* `string`

The `->length()` string function can be used to return the length of a
string in bytes.

**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;
use TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder;
use TYPO3\CMS\Core\Database\Query\QueryBuilder;

final class MyTableRepository
{
    private const TABLE_NAME = 'tt_content';
    public function __construct(private readonly ConnectionPool $connectionPool) {}

    public function findFieldLongerThenZero(string $fieldName): QueryBuilder
    {
        $queryBuilder = $this->connectionPool->getQueryBuilderForTable(self::TABLE_NAME);
        $queryBuilder->expr()->comparison(
            $queryBuilder->expr()->length($fieldName),
            ExpressionBuilder::GT,
            $queryBuilder->createNamedParameter(0, Connection::PARAM_INT),
        );
        return $queryBuilder;
    }
}

```

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

### trim() {#trim}

-   **class ExpressionBuilder**

    -   *Fully qualified name:* `\TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder`

    -   **trim(string $fieldName, int $position, ?string $char = NULL)**

        Creates a TRIM expression for the given field.

        -   *param $fieldName:* Field name to build expression for
        -   *param $position:* Either constant out of LEADING, TRAILING, BOTH
        -   *param $char:* Character to be trimmed (defaults to space), default: NULL

        *Returns:* `string`

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
<?php

declare(strict_types=1);

namespace MyVendor\MyExtension\Domain\Repository;

use TYPO3\CMS\Core\Database\Connection;
use TYPO3\CMS\Core\Database\ConnectionPool;
use TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder;
use TYPO3\CMS\Core\Database\Query\QueryBuilder;

final class MyTableRepository
{
    private const TABLE_NAME = 'tt_content';
    public function __construct(private readonly ConnectionPool $connectionPool) {}

    public function findFieldThatIsEmptyWhenTrimmed(string $fieldName): QueryBuilder
    {
        $queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
        $queryBuilder->expr()->comparison(
            $queryBuilder->expr()->trim($fieldName),
            ExpressionBuilder::EQ,
            $queryBuilder->createNamedParameter('', Connection::PARAM_STR),
        );
        return $queryBuilder;
    }
}

```

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

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")`