Expression builder¶

Basic usage¶

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

$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:

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

Junctions¶

• ->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:

<?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 a query builder with the connection pool. See available parameter types.

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() or $queryBuilder->quoteIdentifier().

  Warning

  Failing to quote will end up in SQL injections!

• ->like() and ->notLike() values must be additionally quoted with a call to $queryBuilder->escapeLikeWildcards($value) to suppress the special meaning of % characters from $value.

Examples:

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

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:

<?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 a query builder with the connection pool.

Various expressions¶

<?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;
    }
}

<?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;
    }
}