Query builder

The query builder provides a set of methods to create queries programmatically.

This chapter provides examples of the most common queries.

Warning

From a security point of view, the documentation of ->createNamedParameter() and ->quoteIdentifier() are an absolute must read and follow section. Make very sure you understand this and use it for each and every query to prevent SQL injections!

The query builder comes with a happy little list of small methods:

  • Set type of query: ->select(), ->count(), ->update(), ->insert() and ->delete()

  • Prepare WHERE conditions

  • Manipulate default WHERE restrictions added by TYPO3 for ->select()

  • Add LIMIT, GROUP BY and other SQL functions

  • executeQuery() executes a SELECT query and returns a result, a \Doctrine\DBAL\Result object

  • executeStatement() executes an INSERT, UPDATE or DELETE statement and returns the number of affected rows.

Most of the query builder methods provide a fluent interface, return an instance of the current query builder itself, and can be chained:

$queryBuilder
    ->select('uid')
    ->from('pages');

Instantiation

To create an instance of the query builder, call ConnectionPool::getQueryBuilderForTable() and pass the table as an argument. The ConnectionPool object can be injected via dependency injection.

EXT:my_extension/Classes/Domain/Repository/MyRepository.php
<?php

declare(strict_types=1);

namespace MyVendor\MyExtension\Domain\Repository;

use TYPO3\CMS\Core\Database\ConnectionPool;

final class MyRepository
{
    public function __construct(
        private readonly ConnectionPool $connectionPool
    ) {
    }

    public function findSomething()
    {
        $queryBuilder = $this->connectionPool
            ->getQueryBuilderForTable('aTable');
    }
}

Attention

Never instantiate and initialize the query builder manually using dependency injection or GeneralUtility::makeInstance(), otherwise you will miss essential dependencies and runtime setup.

Note

The QueryBuilder holds internal state and should not be reused for different queries: Use one query builder per query. Get a fresh one by calling $connection->createQueryBuilder() if the same table is involved, or use $connectionPool->getQueryBuilderForTable() for a query to a different table. Don't worry, creating those object instances is quite fast.

select() and addSelect()

Create a SELECT query.

Select all fields:

EXT:my_extension/Classes/Domain/Repository/MyRepository.php
// SELECT *
$queryBuilder->select('*')

->select() and a number of other methods of the query builder are variadic and can handle any number of arguments. In ->select() each argument is interpreted as a single field name to be selected:

EXT:my_extension/Classes/Domain/Repository/MyRepository.php
// SELECT `uid`, `pid`, `aField`
$queryBuilder->select('uid', 'pid', 'aField');

Argument unpacking can be used if the list of fields already is available as array:

EXT:my_extension/Classes/Domain/Repository/MyRepository.php
// SELECT `uid`, `pid`, `aField`, `anotherField`
$fields = ['uid', 'pid', 'aField', 'anotherField'];
$queryBuilder->select(...$fields);

->select() automatically supports AS and quotes identifiers. This can be especially useful for join() operations:

EXT:my_extension/Classes/Domain/Repository/MyRepository.php
// SELECT `tt_content`.`bodytext` AS `t1`.`text`
$queryBuilder->select('tt_content.bodytext AS t1.text')

With ->select() the list of fields to be selected is specified, and with ->addSelect() further elements can be added to an existing list.

Mind that ->select() replaces any formerly registered list instead of appending it. Thus, it is not very usefule to call select() twice in a code flow or after an ->addSelect(). The methods ->where() and ->andWhere() share the same behavior: ->where() replaces all formerly registered constraints, ->andWhere() appends additional constraints.

A useful combination of ->select() and ->addSelect() can be:

EXT:my_extension/Classes/Domain/Repository/MyRepository.php
$queryBuilder->select(...$defaultList);
if ($needAdditionalFields) {
    $queryBuilder->addSelect(...$additionalFields);
}

Calling the executeQuery() function on a ->select() query returns a result object of type \Doctrine\DBAL\Result. To receive single rows, a ->fetchAssociative() loop is used on that object, or ->fetchAllAssociative() to return a single array with all rows. A typical code flow of a SELECT query looks like this:

EXT:my_extension/Classes/Domain/Repository/MyRepository.php
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$result = $queryBuilder
    ->select('uid', 'header', 'bodytext')
    ->from('tt_content')
    ->where(
        $queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter('lorem'))
    )
    ->executeQuery();

while ($row = $result->fetchAssociative()) {
    // Do something with that single row
    debug($row);
}

Read how to correctly instantiate a query builder with the connection pool.

Default Restrictions

Note

->select() and ->count() queries trigger TYPO3 magic that adds further default where clauses if the queried table is also registered via $GLOBALS['TCA']. See the RestrictionBuilder section for details on that topic.

count()

Create a COUNT query, a typical usage:

EXT:my_extension/Classes/Domain/Repository/MyRepository.php
// SELECT COUNT(`uid`) FROM `tt_content` WHERE (`bodytext` = 'lorem')
//     AND ((`tt_content`.`deleted` = 0) AND (`tt_content`.`hidden` = 0)
//     AND (`tt_content`.`starttime` <= 1669885410)
//     AND ((`tt_content`.`endtime` = 0) OR (`tt_content`.`endtime` > 1669885410)))
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$count = $queryBuilder
    ->count('uid')
    ->from('tt_content')
    ->where(
        $queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter('lorem'))
    )
    ->executeQuery()
    ->fetchOne();

Read how to correctly instantiate a query builder with the connection pool.

Remarks:

  • Similar to the ->select() query type, ->count() automatically triggers the magic of the RestrictionBuilder that adds default restrictions such as deleted, hidden, starttime and endtime when defined in TCA.

  • Similar to ->select() query types, ->executeQuery() with ->count() returns a result object of type \Doctrine\DBAL\Result. To fetch the number of rows directly, use ->fetchOne().

  • The first argument to ->count() is required, typically ->count(*) or ->count('uid') is used, the field name is automatically quoted.

  • There is no support for DISTINCT, instead a ->groupBy() has to be used, for example:

    // Equivalent to:
// SELECT DISTINCT some_field, another_field FROM my_table

$queryBuilder
    ->select('some_field', 'another_field')
    ->from('my_table')
    ->groupBy('some_field')
    ->addGroupBy('another_field');

  • If ->count() is combined with ->groupBy(), the result may return multiple rows. The order of those rows depends on the used DBMS. Therefore, to ensure the same order of result rows on multiple different databases, a ->groupBy() should always be combined with an ->orderBy().

delete()

Create a DELETE FROM query. The method requires the table name from which data is to be deleted. Classic usage:

EXT:my_extension/Classes/Domain/Repository/MyRepository.php
// DELETE FROM `tt_content` WHERE `bodytext` = 'lorem'
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$affectedRows = $queryBuilder
    ->delete('tt_content')
    ->where(
        $queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter('lorem'))
    )
    ->executeStatement();

Read how to correctly instantiate a query builder with the connection pool.

Remarks:

  • For simple cases it is often easier to write and read using the ->delete() method of the Connection object.

  • In contrast to ->select(), ->delete() does not automatically add WHERE restrictions like AND `deleted` = 0.

  • ->delete() does not magically transform a DELETE FROM `tt_content` WHERE `uid` = 4711 into something like UPDATE `tt_content` SET `deleted` = 1 WHERE `uid` = 4711 internally. A soft-delete must be handled at application level with a dedicated lookup in $GLOBALS['TCA']['theTable']['ctrl']['delete'] to check if a specific table can handle the soft-delete, together with an ->update() instead.

  • Deleting from multiple tables at once is not supported: DELETE FROM `table1`, `table2` can not be created.

  • ->delete() ignores ->join()

  • ->delete() ignores setMaxResults(): DELETE with LIMIT does not work.

update() and set()

Create an UPDATE query. Typical usage:

EXT:my_extension/Classes/Domain/Repository/MyRepository.php
 // UPDATE `tt_content` SET `bodytext` = 'dolor' WHERE `bodytext` = 'lorem'
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$queryBuilder
    ->update('tt_content')
    ->where(
        $queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter('lorem'))
    )
    ->set('bodytext', 'dolor')
    ->executeStatement();

Read how to correctly instantiate a query builder with the connection pool.

->update() requires the table to update as the first argument and a table alias (for example, t) as optional second argument. The table alias can then be used in ->set() and ->where() expressions:

EXT:my_extension/Classes/Domain/Repository/MyRepository.php
// UPDATE `tt_content` `t` SET `t`.`bodytext` = 'dolor' WHERE `t`.`bodytext` = 'lorem'
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$queryBuilder
    ->update('tt_content', 't')
    ->where(
        $queryBuilder->expr()->eq('t.bodytext', $queryBuilder->createNamedParameter('lorem'))
    )
    ->set('t.bodytext', 'dolor')
    ->executeStatement();

Read how to correctly instantiate a query builder with the connection pool.

->set() requires a field name as the first argument and automatically quotes it internally. The second mandatory argument is the value to set a field to. The value is automatically transformed to a named parameter of a prepared statement. This way, ->set() key/value pairs are automatically SQL protected from injection by default.

If a field should be set to the value of another field from the row, quoting must be turned off and ->quoteIdentifier() and false have to be used:

EXT:my_extension/Classes/Domain/Repository/MyRepository.php
// UPDATE `tt_content` SET `bodytext` = `header` WHERE `bodytext` = 'lorem'
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$queryBuilder
    ->update('tt_content')
    ->where(
        $queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter('lorem'))
    )
    ->set('bodytext', $queryBuilder->quoteIdentifier('header'), false)
    ->executeStatement();

Read how to correctly instantiate a query builder with the connection pool.

Remarks:

  • For simple cases it is often easier to use the ->update() method of the Connection object.

  • ->set() can be called multiple times if multiple fields should be updated.

  • ->set() requires a field name as the first argument and automatically quotes it internally.

  • ->set() requires the value to which a field is to be set as the second parameter.

  • ->update() ignores ->join() and ->setMaxResults().

  • The API does not magically add deleted = 0 or other restrictions, as is currently the case with select, for example. (See also RestrictionBuilder).

insert() and values()

Create an INSERT query. Typical usage:

EXT:my_extension/Classes/Domain/Repository/MyRepository.php
// INSERT INTO `tt_content` (`bodytext`, `header`) VALUES(`lorem`, `dolor`)
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$affectedRows = $queryBuilder
    ->insert('tt_content')
    ->values([
        'bodytext' => 'lorem',
        'header' => 'dolor',
    ])
    ->executeStatement();

Read how to correctly instantiate a query builder with the connection pool.

The uid of the created database row can be fetched from the connection by using $queryBuilder->getConnection()->lastInsertId().

Remarks:

  • Often it is often to use ->insert() or ->bulkInsert() of the Connection object.

  • ->values() expects an array of key/value pairs. Both keys (field names / identifiers) and values are automatically quoted. In rare cases, quoting of values can be turned off by setting the second argument to false. In those cases, quoting has must done manually, typically by using ->createNamedParameter() on the values, use with care ...

  • ->executeStatement() after ->insert() returns the number of inserted rows, which is typically 1.

  • The query builder does not contain a method for inserting multiple rows at once, use ->bulkInsert() of the Connection object instead to achieve that.

from()

->from() is essential for ->select() and ->count() query types. ->from() requires a table name and an optional alias name. The method is usually called once per query creation and the table name is usually the same as the one passed to ->getQueryBuilderForTable(). If the query joins multiple tables, the argument should be the name of the first table within the ->join() chain:

EXT:my_extension/Classes/Domain/Repository/MyRepository.php
// FROM `myTable`
$queryBuilder->from('myTable');

// FROM `myTable` AS `anAlias`
$queryBuilder->from('myTable', 'anAlias');

->from() can be called multiple times and will create the Cartesian product of tables if not constrained by a respective ->where() or ->andWhere() expression. In general, it is a good idea to use ->from() only once per query and instead model the selection of multiple tables with an explicit ->join().

where(), andWhere() and orWhere()

The three methods are used to create WHERE restrictions for SELECT, COUNT, UPDATE and DELETE query types. Each argument is usually an ExpressionBuilder object that is converted to a string on ->executeQuery() or ->executeStatement():

EXT:my_extension/Classes/Domain/Repository/MyRepository.php
// use TYPO3\CMS\Core\Database\Connection;
// SELECT `uid`, `header`, `bodytext`
// FROM `tt_content`
// WHERE
//    (
//       ((`bodytext` = 'lorem') AND (`header` = 'a name'))
//       OR (`bodytext` = 'dolor') OR (`bodytext` = 'hans')
//    )
//    AND (`pid` = 42)
//    AND ... RestrictionBuilder TCA restrictions ...
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$result = $queryBuilder
    ->select('uid', 'header', 'bodytext')
    ->from('tt_content')
    ->where(
        $queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter('lorem')),
        $queryBuilder->expr()->eq('header', $queryBuilder->createNamedParameter('a name'))
    )
    ->orWhere(
        $queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter('dolor')),
        $queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter('hans'))
    )
    ->andWhere(
        $queryBuilder->expr()->eq('pid', $queryBuilder->createNamedParameter(42, Connection::PARAM_INT))
    )
    ->executeQuery();

Read how to correctly instantiate a query builder with the connection pool.

Note the parenthesis of the above example: ->andWhere() encapsulates both ->where() and ->orWhere() with an additional restriction.

Argument unpacking can become handy with these methods:

EXT:my_extension/Classes/Domain/Repository/MyRepository.php
$whereExpressions = [
    $queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter('lorem')),
    $queryBuilder->expr()->eq('header', $queryBuilder->createNamedParameter('a name'))
];
if ($needsAdditionalExpression) {
    $whereExpressions[] = $someAdditionalExpression;
}
$queryBuilder->where(...$whereExpressions);

Remarks:

  • The three methods are variadic. They can handle any number of arguments. For instance, if ->where() receives four arguments, they are handled as single expressions, all combined with AND.

  • createNamedParameter is used to create a placeholder for a field value of a prepared statement. Always use this when dealing with user input in expressions to protect the statement from SQL injections.

  • ->where() should be called only once per query and resets all previously set ->where(), ->andWhere() and ->orWhere() expressions. A ->where() call after a previous ->where(), ->andWhere() or ->orWhere() usually indicates a bug or a rather weird code flow. Doing so is discouraged.

  • When creating complex WHERE restrictions, ->getSQL() and ->getParameters() are helpful debugging tools to verify parenthesis and single query parts.

  • If only ->eq() expressions are used, it is often easier to switch to the according method of the Connection object to simplify quoting and improve readability.

  • It is possible to feed the methods directly with strings, but this is discouraged and usually used only in rare cases where expression strings are created in a different place that can not be easily resolved.

join(), innerJoin(), rightJoin() and leftJoin()

Joining multiple tables in a ->select() or ->count() query is done with one of these methods. Multiple joins are supported by calling the methods more than once. All methods require four arguments: The name of the table on the left (or its alias), the name of the table on the right, an alias for the name of the table on the right, and the join restriction as fourth argument:

EXT:my_extension/Classes/Domain/Repository/MyRepository.php
// SELECT `sys_language`.`uid`, `sys_language`.`title`
// FROM `sys_language`
// INNER JOIN `pages` `p`
//     ON `p`.`sys_language_uid` = `sys_language`.`uid`
// WHERE
//     (`p`.`uid` = 42)
//     AND (
//          (`p`.`deleted` = 0)
//          AND (
//              (`sys_language`.`hidden` = 0) AND (`overlay`.`hidden` = 0)
//          )
//          AND (`p`.`starttime` <= 1475591280)
//          AND ((`p`.`endtime` = 0) OR (`overlay`.`endtime` > 1475591280))
//     )
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('sys_language')
$result = $queryBuilder
   ->select('sys_language.uid', 'sys_language.title')
   ->from('sys_language')
   ->join(
       'sys_language',
       'pages',
       'p',
       $queryBuilder->expr()->eq('p.sys_language_uid', $queryBuilder->quoteIdentifier('sys_language.uid'))
   )
   ->where(
       $queryBuilder->expr()->eq('p.uid', $queryBuilder->createNamedParameter(42, \PDO::PARAM_INT))
   )
   ->executeQuery();

Read how to correctly instantiate a query builder with the connection pool.

Notes to the example above:

  • The query operates with the sys_language table as the main table, this table name is given to getQueryBuilderForTable().

  • The query joins the pages table as INNER JOIN and gives it the alias p.

  • The join condition is `p`.`sys_language_uid` = `sys_language`.`uid`. It would have been identical to swap the expression arguments of the fourth ->join() argument ->eq('sys_language.uid', $queryBuilder->quoteIdentifier('p.sys_language_uid')).

  • The second argument of the join expression instructs the ExpressionBuilder to quote the value as a field identifier (a field name, here a combination of table and field name). Using createNamedParameter would lead in quoting as value (' instead of ` in MySQL) and the query would fail.

  • The alias p - the third argument of the ->join() call - does not necessarily have to be set to a different name than the table name itself here. It is sufficient to use pages as third argument and not to specify any other name. Aliases are mostly useful when a join to the same table is needed: SELECT `something` FROM `tt_content` JOIN `tt_content` `content2` ON .... Aliases are also useful to increase the readability of ->where() expressions.

  • The RestrictionBuilder has added additional WHERE conditions for both tables involved! The sys_language table obviously only specifies a 'disabled' => 'hidden' as enableColumns in its TCA ctrl section, while the pages table specifies the fields deleted, hidden, starttime and stoptime.

A more complex example with two joins. The first join points to the first table, again using an alias to resolve a language overlay scenario. The second join uses the alias of the first join target as left side:

EXT:my_extension/Classes/Domain/Repository/MyRepository.php
// SELECT `tt_content_orig`.`sys_language_uid`
// FROM `tt_content`
// INNER JOIN `tt_content` `tt_content_orig` ON `tt_content`.`t3_origuid` = `tt_content_orig`.`uid`
// INNER JOIN `sys_language` `sys_language` ON `tt_content_orig`.`sys_language_uid` = `sys_language`.`uid`
// WHERE
//     (`tt_content`.`colPos` = 1)
//     AND (`tt_content`.`pid` = 42)
//     AND (`tt_content`.`sys_language_uid` = 2)
//     AND ... RestrictionBuilder TCA restrictions for tables tt_content and sys_language ...
// GROUP BY `tt_content_orig`.`sys_language_uid`
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('sys_language')
$constraints = [
    $queryBuilder->expr()->eq('tt_content.colPos', $queryBuilder->createNamedParameter(1, \PDO::PARAM_INT)),
    $queryBuilder->expr()->eq('tt_content.pid', $queryBuilder->createNamedParameter(42, \PDO::PARAM_INT)),
    $queryBuilder->expr()->eq('tt_content.sys_language_uid', $queryBuilder->createNamedParameter(2, \PDO::PARAM_INT)),
];
$queryBuilder
    ->select('tt_content_orig.sys_language_uid')
    ->from('tt_content')
    ->join(
        'tt_content',
        'tt_content',
        'tt_content_orig',
        $queryBuilder->expr()->eq(
            'tt_content.t3_origuid',
            $queryBuilder->quoteIdentifier('tt_content_orig.uid')
        )
    )
    ->join(
        'tt_content_orig',
        'sys_language',
        'sys_language',
        $queryBuilder->expr()->eq(
            'tt_content_orig.sys_language_uid',
            $queryBuilder->quoteIdentifier('sys_language.uid')
        )
    )
    ->where(...$constraints)
    ->groupBy('tt_content_orig.sys_language_uid')
    ->executeQuery();

Read how to correctly instantiate a query builder with the connection pool.

Further remarks:

  • ->join() and innerJoin are identical. They create an INNER JOIN query, this is identical to a JOIN query.

  • ->leftJoin() creates a LEFT JOIN query, this is identical to a LEFT OUTER JOIN query.

  • ->rightJoin() creates a RIGHT JOIN query, this is identical to a RIGHT OUTER JOIN query.

  • Calls to join() methods are only considered for ->select() and ->count() type queries. ->delete(), ->insert() and update() do not support joins, these query parts are ignored and do not end up in the final statement.

  • The argument of ->getQueryBuilderForTable() should be the leftmost main table.

  • Joining two tables that are configured to different connections will throw an exception. This restricts the tables that can be configured for different database endpoints. It is possible to test the connection objects of the involved tables for equality and implement a fallback logic in PHP if they are different.

  • Doctrine DBAL does not support the use of join methods in combination with ->update(), ->insert() and ->delete() methods, because such a statement is not cross-platform compatible.

orderBy() and addOrderBy()

Add ORDER BY to a ->select() statement. Both ->orderBy() and ->addOrderBy() require a field name as first argument:

EXT:my_extension/Classes/Domain/Repository/MyRepository.php
// SELECT * FROM `sys_language` ORDER BY `sorting` ASC
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('sys_language');
$queryBuilder->getRestrictions()->removeAll();
$languageRecords = $queryBuilder
    ->select('*')
    ->from('sys_language')
    ->orderBy('sorting')
    ->executeQuery()
    ->fetchAllAssociative();

Read how to correctly instantiate a query builder with the connection pool.

Remarks:

  • ->orderBy() resets all previously specified orders. It makes no sense to call this function again after a previous ->orderBy() or ->addOrderBy().

  • Both methods need a field name or a table.fieldName or a tableAlias.fieldName as first argument. In the example above the call to ->orderBy('sys_language.sorting') would have been identical. All identifiers are quoted automatically.

  • The second, optional argument of both methods specifies the sort order. The two allowed values are 'ASC' and 'DESC', where 'ASC' is default and can be omitted.

  • To create a chain of orders, use ->orderBy() and then multiple ->addOrderBy() calls. The call to ->orderBy('header')->addOrderBy('bodytext')->addOrderBy('uid', 'DESC') creates ORDER BY `header` ASC, `bodytext` ASC, `uid` DESC

  • To add more complex sorting you can use ->add('orderBy', 'FIELD(eventtype, 0, 4, 1, 2, 3)', true), remember to quote properly!

groupBy() and addGroupBy()

Add GROUP BY to a ->select() statement. Each argument of the methods is a single identifier:

EXT:my_extension/Classes/Domain/Repository/MyRepository.php
// GROUP BY `pages`.`sys_language_uid`, `sys_language`.`uid`
->groupBy('pages.sys_language_uid', 'sys_language.uid');

Remarks:

  • Similar to ->select() and ->where(), both methods are variadic and take any number of arguments, argument unpacking is supported: ->groupBy(...$myGroupArray)

  • Each argument is either a direct field name GROUP BY `bodytext`, a table.fieldName or a tableAlias.fieldName and is properly quoted.

  • ->groupBy() resets all previously defined group specification and should only be called once per statement.

  • For more complex statements you can use ->add('groupBy', $sql, $append), remember to quote properly!

setMaxResults() and setFirstResult()

Add LIMIT to restrict the number of records and OFFSET for pagination of query parts. Both methods should be called only once per statement:

EXT:my_extension/Classes/Domain/Repository/MyRepository.php
// SELECT * FROM `sys_language` LIMIT 2 OFFSET 4
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('sys_language');
$queryBuilder
    ->select('*')
    ->from('sys_language')
    ->setMaxResults(2)
    ->setFirstResult(4)
    ->executeQuery();

Read how to correctly instantiate a query builder with the connection pool.

Remarks:

  • It is allowed to call ->setMaxResults() without calling ->setFirstResult().

  • It is possible to call ->setFirstResult() without calling setMaxResults(): This is equivalent to "Fetch everything, but leave out the first n records". Internally, LIMIT will be added by Doctrine DBAL and set to a very high value.

add()

The ->add() method appends or replaces a single, generic query part. It can be used as a low level call when more specific calls do not provide enough freedom to express parts of statements:

EXT:my_extension/Classes/Domain/Repository/MyRepository.php
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('sys_language');
$queryBuilder
    ->select('*')
    ->from('sys_language')
    ->add('orderBy', 'FIELD(eventtype, 0, 4, 1, 2, 3)');

Read how to correctly instantiate a query builder with the connection pool.

Remarks:

  • The first argument is the SQL part. One of: select, from, set, where, groupBy, having or orderBy.

  • The second argument is the (properly quoted!) SQL segment of this part.

  • The optional third boolean argument specifies whether the SQL fragment should be appended (true) or replace a possibly existing SQL part of this name (false, default).

getSQL()

The ->getSQL() method returns the created query statement as string. It is incredibly useful during development to verify that the final statement is executed exactly as a developer expects:

EXT:my_extension/Classes/Domain/Repository/MyRepository.php
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('sys_language');
$queryBuilder
    ->select('*')
    ->from('sys_language');
debug($queryBuilder->getSQL());
$result = $queryBuilder->executeQuery();

Read how to correctly instantiate a query builder with the connection pool.

Remarks:

  • This is debugging code. Take proper actions to ensure that these calls do not end up in production!

  • The method is usually called directly before ->executeQuery() or ->executeStatement() to output the final statement.

  • Casting a query builder object to (string) has the same effect as calling ->getSQL(), but the explicit call using the method should be preferred to simplify a search operation for this kind of debugging statements.

  • The method is a simple way to see what restrictions the RestrictionBuilder has added.

  • Doctrine DBAL always creates prepared statements: Each value added via createNamedParameter creates a placeholder that is later replaced when the real query is triggered via ->executeQuery() or ->executeStatement(). ->getSQL() does not show these values, instead it displays the placeholder names, usually with a string like :dcValue1. There is no simple solution to show the fully replaced query within the framework, but you can use getParameters to see the array of parameters used to replace these placeholders within the query. On the frontend, the queries and parameters are available in the admin panel.

getParameters()

The ->getParameters() method returns the values for the placeholders of the prepared statement in an array. This is incredibly useful during development to verify that the final statement is executed as a developer expects:

EXT:my_extension/Classes/Domain/Repository/MyRepository.php
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('sys_language');
$queryBuilder
    ->select('*')
    ->from('sys_language');
debug($queryBuilder->getParameters());
$statement = $queryBuilder->executeQuery();

Read how to correctly instantiate a query builder with the connection pool.

Remarks:

  • This is debugging code. Take proper actions to ensure that these calls do not end up in production!

  • The method is usually called directly before ->executeQuery() or ->executeStatement() to output the final statement.

  • Doctrine DBAL always creates prepared statements: Each value added via createNamedParameter creates a placeholder that is later replaced when the real query is triggered via ->executeQuery() or ->executeStatement(). ->getParameters() does not show the statement or the placeholders, instead the values are displayed, usually an array using keys like :dcValue1. There is no simple solution to show the fully replaced query within the framework, but you can use getSql to see the string with placeholders, which is used as a prepared statement.

execute(), executeQuery() and executeStatement()

Changed in version 11.5: The widely used ->execute() method has been split into executeQuery() and executeStatement(). executeQuery() returns a \Doctrine\DBAL\Result instead of a \Doctrine\DBAL\Statement. executeStatement() returns the number of affected rows.

Although ->execute() still works for backwards compatibility, you should prefer to use ->executeQuery() for SELECT and COUNT statements and ->executeStatement() for INSERT, UPDATE and DELETE queries.

executeQuery()

This method compiles and fires the final query statement. This is usually the last call on a query builder object. It can be called for SELECT and COUNT queries.

On success, it returns a result object of type \Doctrine\DBAL\Result representing the result set. The Result object can then be used by fetchAssociative(), fetchAllAssociative() and fetchOne(). executeQuery() returns a \Doctrine\DBAL\Result and not a \Doctrine\DBAL\Statement anymore.

Note

It is not possible to rebind placeholder values on the result and execute another query, as was sometimes done with the Statement returned by execute().

If the query fails for some reason (for instance, if the database connection was lost or if the query contains a syntax error), an \Doctrine\DBAL\Exception is thrown. It is usually bad habit to catch and suppress this exception, as it indicates a runtime error a program error. Both should bubble up. For more information on proper exception handling, see the coding guidelines.

executeStatement()

The executeStatement() method can be used for INSERT, UPDATE and DELETE statements. It returns the number of affected rows as an integer.

expr()

This method returns an instance of the ExpressionBuilder. It is used to create complex WHERE query parts and JOIN expressions:

EXT:my_extension/Classes/Domain/Repository/MyRepository.php
// use TYPO3\CMS\Core\Database\Connection;
// SELECT `uid` FROM `tt_content` WHERE (`uid` > 42)
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$queryBuilder
    ->select('uid')
    ->from('tt_content')
    ->where(
        $queryBuilder->expr()->gt(
            'uid',
            $queryBuilder->createNamedParameter(42, Connection::PARAM_INT)
        )
    )
    ->executeQuery();

Read how to correctly instantiate a query builder with the connection pool.

Remarks:

  • This object is stateless and can be called and worked on as often as needed. However, it is bound to the specific connection for which a statement is created and therefore only available through the query builder, which is specific to a connection.

  • Never reuse the ExpressionBuilder, especially not between multiple query builder objects, but always get an instance of the expression builder by calling ->expr().

createNamedParameter()

This method creates a placeholder for a field value of a prepared statement. Always use this when dealing with user input in expressions to protect the statement from SQL injections:

EXT:my_extension/Classes/Domain/Repository/MyRepository.php
// SELECT * FROM `tt_content` WHERE (`bodytext` = 'kl\'aus')
$searchWord = "kl'aus"; // $searchWord retrieved from the PSR-7 request
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$queryBuilder->getRestrictions()->removeAll();
$queryBuilder
    ->select('uid')
    ->from('tt_content')
    ->where(
        $queryBuilder->expr()->eq(
            'bodytext',
            $queryBuilder->createNamedParameter($searchWord)
        )
    )
    ->executeQuery();

Read how to correctly instantiate a query builder with the connection pool.

The above example shows the importance of using ->createNamedParameter(): The search word kl'aus is "tainted" and would break the query if not channeled through ->createNamedParameter(), which quotes the backtick and makes the value SQL injection-safe.

Not convinced? Suppose the code would look like this:

EXT:my_extension/Classes/Domain/Repository/MyRepository.php
// NEVER EVER DO THIS!
$_POST['searchword'] = "'foo' UNION SELECT username FROM be_users";
$searchWord = $request->getParsedBody()['searchword']);
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$queryBuilder->getRestrictions()->removeAll();
 this fails with syntax error to prevent copy and paste
$queryBuilder
    ->select('uid')
    ->from('tt_content')
    ->where(
        // MASSIVE SECURITY ISSUE DEMONSTRATED HERE
        // USE ->createNamedParameter() ON $searchWord!
        $queryBuilder->expr()->eq('bodytext', $searchWord)
    );

Read how to correctly instantiate a query builder with the connection pool.

Mind the missing ->createNamedParameter() method call in the ->eq() expression for a given value! This code would happily execute the statement SELECT uid FROM `tt_content` WHERE `bodytext` = 'foo' UNION SELECT username FROM be_users; returning a list of backend user names!

Note

->set() automatically converts the second mandatory parameter into a named parameter of a prepared statement. If the second parameter is wrapped in a ->createNamedParameter() call, this will result in an error during execution. This behaviour can be disabled by passing false as third parameter to ->set().

More examples

Use integer, integer array:

EXT:my_extension/Classes/Domain/Repository/MyRepository.php
// use TYPO3\CMS\Core\Database\Connection;
// SELECT * FROM `tt_content`
//     WHERE `bodytext` = 'kl\'aus'
//     AND   sys_language_uid = 0
//     AND   pid in (2, 42,13333)
$searchWord = "kl'aus"; // $searchWord retrieved from the PSR-7 request
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$queryBuilder->getRestrictions()->removeAll();
$queryBuilder
    ->select('uid')
    ->from('tt_content')
    ->where(
        $queryBuilder->expr()->eq(
            'bodytext',
            $queryBuilder->createNamedParameter($searchWord)
        ),
        $queryBuilder->expr()->eq(
            'sys_language_uid',
            $queryBuilder->createNamedParameter($language, Connection::PARAM_INT)
        ),
        $queryBuilder->expr()->in(
            'pid',
            $queryBuilder->createNamedParameter($pageIds, Connection::PARAM_INT_ARRAY)
        )
    )
    ->executeQuery();

Read how to correctly instantiate a query builder with the connection pool.

Rules

  • Always use ->createNamedParameter() for any input, no matter where it comes from.

  • The second argument of ->expr() is always either a call to ->createNamedParameter() or ->quoteIdentifier().

  • The second argument of ->createNamedParameter() specifies the type of input. For string, this can be omitted, but it is good practice to add \TYPO3\CMS\Core\Database\Connection::PARAM_INT for integers or similar for other field types. This is not strict rule currently, but if you follow it you will have fewer headaches in the future, especially with DBMSes that are not as relaxed as MySQL when it comes to field types. The Connection constants can be used for simple types like bool, string, null, lob and integer. Additionally, the two constants Connection::PARAM_INT_ARRAY and Connection::PARAM_STR_ARRAY can be used when handling an array of strings or integers, for instance in an IN() expression.

  • Keep the ->createNamedParameter() method as close to the expression as possible. Do not structure your code in a way that it quotes something first and only later stuffs the already prepared names into the expression. Having ->createNamedParameter() directly within the created expression, is much less error-prone and easier to review. This is a general rule: Sanitizing input must be done as close as possible to the "sink" where a value is passed to a lower part of the framework. This paradigm should also be followed for other quote operations like htmlspecialchars() or GeneralUtility::quoteJSvalue(). Sanitization should be obvious directly at the very place where it is important:

EXT:my_extension/Classes/Domain/Repository/MyRepository.php
// DO
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$queryBuilder->getRestrictions()->removeAll();
$queryBuilder
    ->select('uid')
    ->from('tt_content')
    ->where(
        $queryBuilder->expr()->eq(
            'bodytext',
            $queryBuilder->createNamedParameter($searchWord)
        )
    )

// DON'T DO, this is much harder to track:
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$myValue = $queryBuilder->createNamedParameter($searchWord);
// Imagine much more code here
$queryBuilder->getRestrictions()->removeAll();
$queryBuilder
    ->select('uid')
    ->from('tt_content')
    ->where(
        $queryBuilder->expr()->eq('bodytext', $myValue)
    )

Read how to correctly instantiate a query builder with the connection pool.

quoteIdentifier() and quoteIdentifiers()

->quoteIdentifier() must be used when not a value but a field name is handled. The quoting is different in those cases and typically ends up with backticks ` instead of ticks ':

EXT:my_extension/Classes/Domain/Repository/MyRepository.php
// SELECT `uid` FROM `tt_content` WHERE (`header` = `bodytext`)
// Return list of rows where header and bodytext values are identical
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$queryBuilder
    ->select('uid')
    ->from('tt_content')
    ->where(
        $queryBuilder->expr()->eq(
            'header',
            $queryBuilder->quoteIdentifier('bodytext')
        )
    );

Read how to correctly instantiate a query builder with the connection pool.

The method quotes single field names or combinations of table names or table aliases with field names:

Some quote examples
// Single field name: `bodytext`
->quoteIdentifier('bodytext');

// Table name and field name: `tt_content`.`bodytext`
->quoteIdentifier('tt_content.bodytext')

// Table alias and field name: `foo`.`bodytext`
->from('tt_content', 'foo')->quoteIdentifier('foo.bodytext')

Remarks:

  • Similar to ->createNamedParameter() this method is crucial to prevent SQL injections. The same rules apply here.

  • The ->set() method for UPDATE statements expects its second argument to be a field value by default, and quotes it internally using ->createNamedParameter(). If a field should be set to the value of another field, this quoting can be turned off and an explicit call to ->quoteIdentifier() must be added.

  • Internally, ->quoteIdentifier() is automatically called on all method arguments that must be a field name. For instance, ->quoteIdentifier() is called for all arguments of ->select().

  • ->quoteIdentifiers() (mind the plural) can be used to quote multiple field names at once. While that method is "public" and thus exposed as an API method, this is mostly useful internally only.

escapeLikeWildcards()

Helper method to quote % characters within a search string. This is helpful in ->like() and ->notLike() expressions:

EXT:my_extension/Classes/Domain/Repository/MyRepository.php
// SELECT `uid` FROM `tt_content` WHERE (`bodytext` LIKE '%kl\\%aus%')
$searchWord = 'kl%aus';
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$queryBuilder
    ->select('uid')
    ->from('tt_content')
    ->where(
        $queryBuilder->expr()->like(
            'bodytext',
            $queryBuilder->createNamedParameter('%' . $queryBuilder->escapeLikeWildcards($searchWord) . '%')
        )
    );

Read how to correctly instantiate a query builder with the connection pool.

Warning

Even when using ->escapeLikeWildcards() the value must be encapsulated again in a ->createNamedParameter() call. Only calling ->escapeLikeWildcards() does not make the value SQL injection safe!