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.

Internal

Table of Contents

Methods

getDateTimeFormats()  : array<string|int, mixed>
Returns the date and time formats compatible with the given database.
getDateTimeTypes()  : array<string|int, mixed>
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|int, mixed>|array<string|int, string>
Removes the prefix "GROUP BY" from the input string.
parseJoin()  : array<string|int, mixed>
Split a JOIN SQL fragment into table name, alias and join conditions.
parseOrderBy()  : array<string|int, mixed>|array<string|int, array<string|int, mixed>>
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<string|int, mixed>|array<string|int, array<string|int, mixed>>
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<string|int, mixed>

This simple method should probably be deprecated and removed later.

Return values
array<string|int, mixed>

getDateTimeTypes()

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

public static getDateTimeTypes() : array<string|int, mixed>

This simple method should probably be deprecated and removed later.

Return values
array<string|int, mixed>

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<string|int, mixed> $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<string|int, mixed>
$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<string|int, mixed> $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<string|int, mixed>
$connection : Connection
Return values
string

parseGroupBy()

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

public static parseGroupBy(string $input) : array<string|int, mixed>|array<string|int, 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|int, mixed>|array<string|int, 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<string|int, mixed>
Parameters
$input : string

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

Return values
array<string|int, mixed> —

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<string|int, mixed>|array<string|int, array<string|int, mixed>>

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<string|int, mixed>|array<string|int, array<string|int, mixed>> —

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<string|int, mixed>|array<string|int, array<string|int, mixed>>

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<string|int, mixed>|array<string|int, array<string|int, mixed>> —

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

        
    


            

                    

        

    

        

            
Search results