Query builder

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.

<?php

declare(strict_types=1);

namespace MyVendor\MyExtension\Domain\Repository;

use TYPO3\CMS\Core\Database\ConnectionPool;

final class MyRepository
{
    private ConnectionPool $connectionPool;

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

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

select() and addSelect()

Create a SELECT query.

Select all fields:

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

// SELECT `uid`, `pid`, `aField`
$queryBuilder->select('uid', 'pid', 'aField');

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

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

// 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 useful 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:

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

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

$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$result = $queryBuilder
    ->select('uid', 'header', 'bodytext')
    ->from('tt_content')
    ->where(
        $queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter('lorem', Connection::PARAM_STR))
    )
    ->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. See available parameter types.

count()

Create a COUNT query, a typical usage:

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

// 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', Connection::PARAM_STR))
    )
    ->executeQuery()
    ->fetchOne();

Read how to correctly instantiate a query builder with the connection pool. See available parameter types.

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');

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

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

// 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', Connection::PARAM_STR))
    )
    ->executeStatement();

Read how to correctly instantiate a query builder with the connection pool. See available parameter types.

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:

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

// 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', Connection::PARAM_STR))
    )
    ->set('bodytext', 'dolor')
    ->executeStatement();

Read how to correctly instantiate a query builder with the connection pool. See available parameter types.

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

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

// 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', Connection::PARAM_STR))
    )
    ->set('t.bodytext', 'dolor')
    ->executeStatement();

Read how to correctly instantiate a query builder with the connection pool. See available parameter types.

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

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

// 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', Connection::PARAM_STR))
    )
    ->set('bodytext', $queryBuilder->quoteIdentifier('header'), false)
    ->executeStatement();

Read how to correctly instantiate a query builder with the connection pool. See available parameter types.

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:

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

Remarks:

• The uid of the created database row can be fetched from the connection by using $queryBuilder->getConnection()->lastInsertId().
• ->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. Then quoting must be done manually, typically by using ->createNamedParameter() on the values.
• ->executeStatement() after ->insert() returns the number of inserted rows, which is typically 1.
• An alternative to using the query builder for inserting data is using the Connection object with its ->insert() method.
• The query builder does not provide 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:

// 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():

// 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', Connection::PARAM_STR)),
        $queryBuilder->expr()->eq('header', $queryBuilder->createNamedParameter('a name', Connection::PARAM_STR))
    )
    ->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. See available parameter types.

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:

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

$whereExpressions = [
    $queryBuilder->expr()->eq('bodytext', $queryBuilder->createNamedParameter('lorem', Connection::PARAM_STR)),
    $queryBuilder->expr()->eq('header', $queryBuilder->createNamedParameter('a name', Connection::PARAM_STR))
];
if ($needsAdditionalExpression) {
    $whereExpressions[] = $someAdditionalExpression;
}
$queryBuilder->where(...$whereExpressions);

See available parameter types.

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:

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

// 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, Connection::PARAM_INT))
   )
   ->executeQuery();

Read how to correctly instantiate a query builder with the connection pool. See available parameter types.

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:

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

// 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, Connection::PARAM_INT)),
    $queryBuilder->expr()->eq('tt_content.pid', $queryBuilder->createNamedParameter(42, Connection::PARAM_INT)),
    $queryBuilder->expr()->eq('tt_content.sys_language_uid', $queryBuilder->createNamedParameter(2, Connection::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. See available parameter types.

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:

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

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

// 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.
• Use ->setMaxResults(null) to retrieve all results.

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:

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

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

$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()

expr()

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

// 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. See available parameter types.

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:

// SELECT * FROM `tt_content` WHERE (`bodytext` = 'kl\'aus')
$searchWord = "kl'aus"; // $searchWord = GeneralUtility::_GP('searchword');
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$queryBuilder->getRestrictions()->removeAll();
$queryBuilder
    ->select('uid')
    ->from('tt_content')
    ->where(
        $queryBuilder->expr()->eq(
            'bodytext',
            $queryBuilder->createNamedParameter($searchWord, Connection::PARAM_STR)
        )
    )
    ->executeQuery();

Read how to correctly instantiate a query builder with the connection pool. See available parameter types.

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:

// NEVER EVER DO THIS!
$_POST['searchword'] = "'foo' UNION SELECT username FROM be_users";
$searchWord = GeneralUtility::_GP('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!

// 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 = GeneralUtility::_GP('searchword');
$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)
        )
    )
    ->execute();

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

// DO
$queryBuilder = $this->connectionPool->getQueryBuilderForTable('tt_content');
$queryBuilder->getRestrictions()->removeAll();
$queryBuilder
    ->select('uid')
    ->from('tt_content')
    ->where(
        $queryBuilder->expr()->eq(
            'bodytext',
            $queryBuilder->createNamedParameter($searchWord, Connection::PARAM_STR)
        )
    )

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

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

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

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

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

// 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) . '%', Connection::PARAM_STR)
        )
    );

Read how to correctly instantiate a query builder with the connection pool. See available parameter types.

getRestrictions(), setRestrictions(), resetRestrictions()

API methods to deal with the RestrictionBuilder.