Feature: #103309 - Add more expression methods to ExpressionBuilder

See forge#103309

Description

The TYPO3 \TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder provides a relatively conservative set of database query expressions since a couple of TYPO3 and Doctrine DBAL versions now.

Additional expression methods are now available to build more advanced database queries that ensure compatibility across supported database vendors.

ExpressionBuilder::as()

Creates a statement to append a field alias to a value, identifier or sub-expression.

Method signature
/**
 * @param string $expression Value, identifier or expression which
 *                           should be aliased.
 * @param string $asIdentifier Used to add a field identifier alias
 *                             (`AS`) if non-empty string (optional).
 *
 * @return string   Returns aliased expression.
 */
public function as(
    string $expression,
    string $asIdentifier = '',
): string {}

// use TYPO3\CMS\Core\Database\Connection;
// use TYPO3\CMS\Core\Database\ConnectionPool;
// use TYPO3\CMS\Core\Utility\GeneralUtility;
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
    ->getQueryBuilderForTable('some_table');
$expressionBuilder = $queryBuilder->expr();

$queryBuilder->selectLiteral(
  $queryBuilder->quoteIdentifier('uid'),
  $expressionBuilder->as('(1 + 1 + 1)', 'calculated_field'),
);

$queryBuilder->selectLiteral(
  $queryBuilder->quoteIdentifier('uid'),
  $expressionBuilder->as(
    $expressionBuilder->concat(
        $expressionBuilder->literal('1'),
        $expressionBuilder->literal(' '),
        $expressionBuilder->literal('1'),
    ),
    'concatenated_value'
  ),
);
Copied!

ExpressionBuilder::concat()

Can be used to concatenate values, row field values or expression results into a single string value.

Method signature
/**
 * @param string ...$parts      Unquoted value or expression parts to
 *                              concatenate with each other
 * @return string  Returns the concatenation expression compatible with
 *                 the database connection platform.
 */
public function concat(string ...$parts): string {}
Copied!
Usage example
// use TYPO3\CMS\Core\Database\Connection;
// use TYPO3\CMS\Core\Database\ConnectionPool;
// use TYPO3\CMS\Core\Utility\GeneralUtility;
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
    ->getQueryBuilderForTable('pages');
$expressionBuilder = $queryBuilder->expr();
$result = $queryBuilder
    ->select('uid', 'pid', 'title')
    ->addSelectLiteral(
        $expressionBuilder->concat(
            $queryBuilder->quoteIdentifier('title'),
            $queryBuilder->quote(' - ['),
            $queryBuilder->quoteIdentifier('uid'),
            $queryBuilder->quote('|'),
            $queryBuilder->quoteIdentifier('pid'),
            $queryBuilder->quote(']'),
        ) . ' AS ' . $queryBuilder->quoteIdentifier('page_title_info')
    )
    ->where(
        $expressionBuilder->eq(
            'pid',
            $queryBuilder->createNamedParameter(0, Connection::PARAM_INT)
        ),
    )
    ->executeQuery();

while ($row = $result->fetchAssociative()) {
    // $row = array{
    //  'uid' => 1,
    //  'pid' => 0,
    //  'title' => 'Site Root Page',
    //  'page_title_info' => 'Site Root Page - [1|0]',
    // }
}
Copied!

ExpressionBuilder::castVarchar()

Can be used to create an expression which converts a value, row field value or the result of an expression to varchar type with dynamic length.

Method signature
/**
 * @param string    $value          Unquoted value or expression,
 *                                  which should be casted.
 * @param int       $length         Dynamic varchar field length.
 * @param string    $asIdentifier   Used to add a field identifier alias
 *                                  (`AS`) if non-empty string (optional).
 * @return string   Returns the cast expression compatible for the database platform.
 */
public function castVarchar(
    string $value,
    int $length = 255,
    string $asIdentifier = '',
): string {}
Copied!
Usage example
// use TYPO3\CMS\Core\Database\ConnectionPool;
// use TYPO3\CMS\Core\Utility\GeneralUtility;
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
    ->getQueryBuilderForTable('some_table');

$fieldVarcharCastExpression = $queryBuilder->expr()->castVarchar(
    $queryBuilder->quote('123'), // integer as string
    255,                         // convert to varchar(255) field - dynamic length
    'new_field_identifier',
);

$fieldExpressionCastExpression = $queryBuilder->expr()->castVarchar(
    '(100 + 200)',           // calculate a integer value
    100,                     // dynamic varchar(100) field
    'new_field_identifier',
);
Copied!

ExpressionBuilder::castInt()

Can be used to create an expression which converts a value, row field value or the result of an expression to signed integer type.

Method signature
/**
 * @param string    $value         Quoted value or expression result which
 *                                 should be casted to integer type.
 * @param string    $asIdentifier  Used to add a field identifier alias
 *                                 (`AS`) if non-empty string (optional).
 * @return string   Returns the integer cast expression compatible with the
 *                  connection database platform.
 */
public function castInt(string $value, string $asIdentifier = ''): string {}
Copied!
Usage example
// use TYPO3\CMS\Core\Database\ConnectionPool;
// use TYPO3\CMS\Core\Utility\GeneralUtility;
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
    ->getQueryBuilderForTable('pages');
$queryBuilder
    ->select('uid')
    ->from('pages');

// simple value (quoted) to be used as sub-expression
$expression1 = $queryBuilder->expr()->castInt(
    $queryBuilder->quote('123'),
);

// simple value (quoted) to return as select field
$queryBuilder->addSelectLiteral(
    $queryBuilder->expr()->castInt(
        $queryBuilder->quote('123'),
        'virtual_field',
    ),
);

$expression3 = queryBuilder->expr()->castInt(
  $queryBuilder->quoteIdentifier('uid'),
);

// expression to be used as sub-expression
$expression4 = $queryBuilder->expr()->castInt(
    $queryBuilder->expr()->castVarchar('(1 * 10)'),
);

// expression to return as select field
$queryBuilder->addSelectLiteral(
    $queryBuilder->expr()->castInt(
        $queryBuilder->expr()->castVarchar('(1 * 10)'),
        'virtual_field',
    ),
);
Copied!

ExpressionBuilder::repeat()

Create a statement to generate a value repeating defined $value for $numberOfRepeats times. This method can be used to provide the repeat number as a sub-expression or calculation.

Method signature
/**
 * @param int|string    $numberOfRepeats    Statement or value defining
 *                                          how often the $value should
 *                                          be repeated. Proper quoting
 *                                          must be ensured.
 * @param string        $value              Value which should be repeated.
 *                                          Proper quoting must be ensured.
 * @param string        $asIdentifier       Provide `AS` identifier if not
 *                                          empty.
 * @return string   Returns the platform compatible statement to create the
 *                  x-times repeated value.
 */
public function repeat(
    int|string $numberOfRepeats,
    string $value,
    string $asIdentifier = '',
): string {}
Copied!
Usage example
// use TYPO3\CMS\Core\Database\ConnectionPool;
// use TYPO3\CMS\Core\Utility\GeneralUtility;
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
    ->getQueryBuilderForTable('some_table');

$expression1 = $queryBuilder->expr()->repeat(
    10,
    $queryBuilder->quote('.'),
);

$expression2 = $queryBuilder->expr()->repeat(
    20,
    $queryBuilder->quote('0'),
    $queryBuilder->quoteIdentifier('aliased_field'),
);

$expression3 = $queryBuilder->expr()->repeat(
    20,
    $queryBuilder->quoteIdentifier('table_field'),
    $queryBuilder->quoteIdentifier('aliased_field'),
);

$expression4 = $queryBuilder->expr()->repeat(
    $queryBuilder->expr()->castInt(
        $queryBuilder->quoteIdentifier('repeat_count_field')
    ),
    $queryBuilder->quoteIdentifier('table_field'),
    $queryBuilder->quoteIdentifier('aliased_field'),
);

$expression5 = $queryBuilder->expr()->repeat(
    '(7 + 3)',
    $queryBuilder->quote('.'),
);

$expression6 = $queryBuilder->expr()->repeat(
  '(7 + 3)',
  $queryBuilder->concat(
    $queryBuilder->quote(''),
    $queryBuilder->quote('.'),
    $queryBuilder->quote(''),
  ),
  'virtual_field_name',
);
Copied!

ExpressionBuilder::space()

Create statement containing $numberOfSpaces spaces.

Method signature
/**
 * @param int|string    $numberOfSpaces Expression or value defining how
 *                                      many spaces should be created.
 * @param string        $asIdentifier   Provide result as identifier field
 *                                      (AS), not added if empty string.
 * @return string   Returns the platform compatible statement to create the
 *                  x-times repeated space(s).
 */
public function space(
    string $numberOfSpaces,
    string $asIdentifier = '',
): string {}
Copied!
Usage example
// use TYPO3\CMS\Core\Database\ConnectionPool;
// use TYPO3\CMS\Core\Utility\GeneralUtility;
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
    ->getQueryBuilderForTable('some_table');

$expression1 = $queryBuilder->expr()->space(
    '10'
);

$expression2 = $queryBuilder->expr()->space(
    '20',
    $queryBuilder->quoteIdentifier('aliased_field'),
);

$expression3 = $queryBuilder->expr()->space(
    '(210)'
);

$expression3 = $queryBuilder->expr()->space(
    '(210)',
    $queryBuilder->quoteIdentifier('aliased_field'),
);

$expression5 = $queryBuilder->expr()->space(
    $queryBuilder->expr()->castInt(
        $queryBuilder->quoteIdentifier('table_repeat_number_field'),
    ),
);

$expression6 = $queryBuilder->expr()->space(
    $queryBuilder->expr()->castInt(
        $queryBuilder->quoteIdentifier('table_repeat_number_field'),
    ),
    $queryBuilder->quoteIdentifier('aliased_field'),
);
Copied!

ExpressionBuilder::left()

Extract $length character of $value from the left side.

Method signature
/**
 * @param int|string    $length         Integer value or expression
 *                                      providing the length as integer.
 * @param string        $value          Value, identifier or expression
 *                                      defining the value to extract from
 *                                      the left.
 * @param string        $asIdentifier   Provide `AS` identifier if not empty.
 * @return string   Return the expression to extract defined substring
 *                  from the right side.
 */
public function left(
    int|string $length,
    string $value,
    string $asIdentifier = '',
): string {}
Copied!
Usage example
// use TYPO3\CMS\Core\Database\ConnectionPool;
// use TYPO3\CMS\Core\Utility\GeneralUtility;
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
    ->getQueryBuilderForTable('some_table');

$expression1 = $queryBuilder->expr()->left(
    6,
    $queryBuilder->quote('some-string'),
);

$expression2 = $queryBuilder->expr()->left(
    '6',
    $queryBuilder->quote('some-string'),
);

$expression3 = $queryBuilder->expr()->left(
    $queryBuilder->castInt('(23)'),
    $queryBuilder->quote('some-string'),
);

$expression4 = $queryBuilder->expr()->left(
    $queryBuilder->castInt('(23)'),
    $queryBuilder->quoteIdentifier('table_field_name'),
);
Copied!

ExpressionBuilder::right()

Extract $length character of $value from the right side.

Method signature
/**
 * @param int|string    $length         Integer value or expression
 *                                      providing the length as integer.
 * @param string        $value          Value, identifier or expression
 *                                      defining the value to extract from
 *                                      the right.
 * @param string        $asIdentifier   Provide `AS` identifier if not empty.
 *
 * @return string   Return the expression to extract defined substring
 *                  from the right side.
 */
public function right(
    int|string $length,
    string $value,
    string $asIdentifier = '',
): string {}
Copied!
Usage example
// use TYPO3\CMS\Core\Database\ConnectionPool;
// use TYPO3\CMS\Core\Utility\GeneralUtility;
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
    ->getQueryBuilderForTable('some_table');

$expression1 = $queryBuilder->expr()->right(
    6,
    $queryBuilder->quote('some-string'),
);

$expression2 = $queryBuilder->expr()->right(
    '6',
    $queryBuilder->quote('some-string'),
);

$expression3 = $queryBuilder->expr()->right(
    $queryBuilder->castInt('(23)'),
    $queryBuilder->quote('some-string'),
);

$expression4 = $queryBuilder->expr()->right(
    $queryBuilder->castInt('(23)'),
    $queryBuilder->quoteIdentifier('table_field_name'),
);
Copied!

ExpressionBuilder::leftPad()

Left-pad the value or sub-expression result with $paddingValue, to a total length of $length.

Method signature
/**
 * @param string        $value          Value, identifier or expression
 *                                      defining the value which should
 *                                      be left padded.
 * @param int|string    $length         Value, identifier or expression
 *                                      defining the padding length to
 *                                      fill up on the left or crop.
 * @param string        $paddingValue   Padding character used to fill
 *                                      up if characters are missing on
 *                                      the left side.
 * @param string        $asIdentifier   Used to add a field identifier alias
 *                                      (`AS`) if non-empty string (optional).
 * @return string   Returns database connection platform compatible
 *                  left-pad expression.
 */
public function leftPad(
    string $value,
    int|string $length,
    string $paddingValue,
    string $asIdentifier = '',
): string {}
Copied!
Usage example
// use TYPO3\CMS\Core\Database\ConnectionPool;
// use TYPO3\CMS\Core\Utility\GeneralUtility;
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
    ->getQueryBuilderForTable('some_table');

$expression1 = $queryBuilder->expr()->leftPad(
    $queryBuilder->quote('123'),
    10,
    '0',
);

$expression2 = $queryBuilder->expr()->leftPad(
    $queryBuilder->expr()->castVarchar($queryBuilder->quoteIdentifier('uid')),
    10,
    '0',
);

$expression3 = $queryBuilder->expr()->leftPad(
    $queryBuilder->expr()->concat(
        $queryBuilder->quote('1'),
        $queryBuilder->quote('2'),
        $queryBuilder->quote('3'),
    ),
    10,
    '0',
);

$expression4 = $queryBuilder->expr()->leftPad(
    $queryBuilder->castVarchar('( 1123 )'),
    10,
    '0',
);

$expression5 = $queryBuilder->expr()->leftPad(
    $queryBuilder->castVarchar('( 1123 )'),
    10,
    '0',
    'virtual_field',
);
Copied!

ExpressionBuilder::rightPad()

Right-pad the value or sub-expression result with $paddingValue, to a total length of $length.

Method signature
/**
 * @param string        $value          Value, identifier or expression
 *                                      defining the value which should be
 *                                      right padded.
 * @param int|string    $length         Value, identifier or expression
 *                                      defining the padding length to
 *                                      fill up on the right or crop.
 * @param string        $paddingValue   Padding character used to fill up
 *                                      if characters are missing on the
 *                                      right side.
 * @param string        $asIdentifier   Used to add a field identifier alias
 *                                      (`AS`) if non-empty string (optional).
 * @return string   Returns database connection platform compatible
 *                  right-pad expression.
 */
public function rightPad(
    string $value,
    int|string $length,
    string $paddingValue,
    string $asIdentifier = '',
): string {}
Copied!
Usage example
// use TYPO3\CMS\Core\Database\ConnectionPool;
// use TYPO3\CMS\Core\Utility\GeneralUtility;
$queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
    ->getQueryBuilderForTable('some_table');

$expression1 = $queryBuilder->expr()->rightPad(
    $queryBuilder->quote('123'),
    10,
    '0',
);

$expression2 = $queryBuilder->expr()->rightPad(
    $queryBuilder->expr()->castVarchar($queryBuilder->quoteIdentifier('uid')),
    10,
    '0',
 );

$expression3 = $queryBuilder->expr()->rightPad(
    $queryBuilder->expr()->concat(
        $queryBuilder->quote('1'),
        $queryBuilder->quote('2'),
        $queryBuilder->quote('3'),
    ),
    10,
    '0',
);

$expression4 = $queryBuilder->expr()->rightPad(
    $queryBuilder->castVarchar('( 1123 )'),
    10,
    '0',
);

$expression5 = $queryBuilder->expr()->rightPad(
    $queryBuilder->quote('123'),
    10,
    '0',
    'virtual_field',
);
Copied!

Impact

Extension authors can use the new expression methods to build more advanced queries without the requirement to deal with the correct implementation for all supported database vendors.