QueryHelper

class \TYPO3\CMS\Core\Database\Query\QueryHelper

Contains misc helper methods to build syntactically valid SQL queries.

Most helper functions are required to deal with legacy data where the format of the input is not strict enough to reliably use the SQL parts in queries directly.

Table of Contents

Methods

getDateTimeFormats()  : array
Returns the date and time formats compatible with the given database.
getDateTimeTypes()  : array
Returns the date and time types compatible with the given database.
implodeToIntQuotedValueList()  : string
Implode array to comma separated list with database int-quoted values to be used as direct value list for database 'in(...)' or 'notIn(...') expressions. Empty array will return 'NULL' as string to avoid database query failure, as 'in()' is invalid, but 'in(null)' will be executed.
implodeToStringQuotedValueList()  : string
Implode array to comma separated list with database string-quoted values to be used as direct value list for database 'in(...)' or 'notIn(...') expressions. Empty array will return 'NULL' as string to avoid database query failure, as 'in()' is invalid, but 'in(null)' will be executed.
parseGroupBy()  : array|string[]
Removes the prefix "GROUP BY" from the input string.
parseJoin()  : array
Split a JOIN SQL fragment into table name, alias and join conditions.
parseOrderBy()  : array|array[]
Takes an input, possibly prefixed with ORDER BY, and explodes it into and array of arrays where each item consists of a fieldName and an order direction.
parseTableList()  : array|array[]
Takes an input, possibly prefixed with FROM, and explodes it into and array of arrays where each item consists of a tableName and an optional alias name.
quoteDatabaseIdentifiers()  : string
Quote database table/column names indicated by {#identifier} markup in a SQL fragment string.
stripLogicalOperatorPrefix()  : string
Removes the prefixes AND/OR from the input string.

Methods

getDateTimeFormats()

Returns the date and time formats compatible with the given database.

public static getDateTimeFormats() : array

This simple method should probably be deprecated and removed later.

Return values

array

getDateTimeTypes()

Returns the date and time types compatible with the given database.

public static getDateTimeTypes() : array

This simple method should probably be deprecated and removed later.

Return values

array

implodeToIntQuotedValueList()

Implode array to comma separated list with database int-quoted values to be used as direct value list for database 'in(...)' or 'notIn(...') expressions. Empty array will return 'NULL' as string to avoid database query failure, as 'in()' is invalid, but 'in(null)' will be executed.

public static implodeToIntQuotedValueList(array $values, Connection $connection) : string

This function should be used with care, as it should be preferred to use placeholders, although there are use cases in some (system) areas which reaches placeholder limit really fast.

Return value should only be used as value list for database query 'IN()' or 'NOTIN()' .

Will be removed in v12, use QueryHelper::quoteArrayBasedValueListToIntegerList() instead.

Parameters

$values : array
$connection : Connection

Return values

string

implodeToStringQuotedValueList()

Implode array to comma separated list with database string-quoted values to be used as direct value list for database 'in(...)' or 'notIn(...') expressions. Empty array will return 'NULL' as string to avoid database query failure, as 'in()' is invalid, but 'in(null)' will be executed.

public static implodeToStringQuotedValueList(array $values, Connection $connection) : string

This function should be used with care, as it should be preferred to use placeholders, although there are use cases in some (system) areas which reaches placeholder limit really fast.

Return value should only be used as value list for database query 'IN()' or 'NOTIN()' .

Will be removed in v12, use QueryHelper::quoteArrayBasedValueListToStringList() instead.

Parameters

$values : array
$connection : Connection

Return values

string

parseGroupBy()

Removes the prefix "GROUP BY" from the input string.

public static parseGroupBy(string $input) : array|string[]

This function should be used when you can't guarantee that the string that you want to use as a GROUP BY fragment is not prefixed.

Parameters

$input : string

eg. "GROUP BY title, uid

Return values

array|string[] —

column names to group by

parseJoin()

Split a JOIN SQL fragment into table name, alias and join conditions.

public static parseJoin(string $input) : array

Parameters

$input : string

eg. "JOIN tableName AS a ON a.uid = anotherTable.uid_foreign"

Return values

array —

assoc array consisting of the keys tableName, tableAlias and joinCondition

parseOrderBy()

Takes an input, possibly prefixed with ORDER BY, and explodes it into and array of arrays where each item consists of a fieldName and an order direction.

public static parseOrderBy(string $input) : array|array[]

Each of the resulting fieldName/direction pairs can be used passed into QueryBuilder::orderBy() so sort a query result set.

Parameters

$input : string

eg . "ORDER BY title, uid

Return values

array|array[] —

Array of arrays containing fieldName/direction pairs

parseTableList()

Takes an input, possibly prefixed with FROM, and explodes it into and array of arrays where each item consists of a tableName and an optional alias name.

public static parseTableList(string $input) : array|array[]

Each of the resulting pairs can be used with QueryBuilder::from() to select from one or more tables.

Parameters

$input : string

eg . "FROM aTable, anotherTable AS b, aThirdTable c"

Return values

array|array[] —

Array of arrays containing tableName/alias pairs

quoteDatabaseIdentifiers()

Quote database table/column names indicated by {#identifier} markup in a SQL fragment string.

public static quoteDatabaseIdentifiers(Connection $connection, string $sql) : string

This is an intermediate step to make SQL fragments in Typoscript and TCA database agnostic.

Parameters

$connection : Connection
$sql : string

Return values

string

stripLogicalOperatorPrefix()

Removes the prefixes AND/OR from the input string.

public static stripLogicalOperatorPrefix(string $constraint) : string

This function should be used when you can't guarantee that the string that you want to use as a WHERE fragment is not prefixed.

Parameters

$constraint : string

The where part fragment with a possible leading AND or OR operator

Return values

string —

The modified where part without leading operator

On this page