Contao Utils Bundle

DatabaseUtil
in package

Table of Contents

Constants

NEGATIVE_OPERATORS  = [self::OPERATOR_UNLIKE, self::OPERATOR_UNEQUAL, self::OPERATOR_NOT_IN, self::OPERATOR_IS_NULL, self::OPERATOR_IS_EMPTY, self::OPERATOR_NOT_REGEXP]
ON_DUPLICATE_KEY_IGNORE  = 'IGNORE'
ON_DUPLICATE_KEY_UPDATE  = 'UPDATE'
OPERATOR_EQUAL  = 'equal'
OPERATOR_GREATER  = 'greater'
OPERATOR_GREATER_EQUAL  = 'greaterequal'
OPERATOR_IN  = 'in'
OPERATOR_IS_EMPTY  = 'isempty'
OPERATOR_IS_NOT_EMPTY  = 'isnotempty'
OPERATOR_IS_NOT_NULL  = 'isnotnull'
OPERATOR_IS_NULL  = 'isnull'
OPERATOR_LIKE  = 'like'
OPERATOR_LOWER  = 'lower'
OPERATOR_LOWER_EQUAL  = 'lowerequal'
OPERATOR_MAPPING  = [self::OPERATOR_LIKE => 'like', self::OPERATOR_UNLIKE => 'notLike', self::OPERATOR_EQUAL => 'eq', self::OPERATOR_UNEQUAL => 'neq', self::OPERATOR_LOWER => 'lt', self::OPERATOR_LOWER_EQUAL => 'lte', self::OPERATOR_GREATER => 'gt', self::OPERATOR_GREATER_EQUAL => 'gte', self::OPERATOR_IN => 'in', self::OPERATOR_NOT_IN => 'notIn', self::OPERATOR_IS_NULL => 'isNull', self::OPERATOR_IS_NOT_NULL => 'isNotNull']
Maps operators of this class to its corresponding Doctrine ExpressionBuilder method.
OPERATOR_NOT_IN  = 'notin'
OPERATOR_NOT_REGEXP  = 'notregexp'
OPERATOR_REGEXP  = 'regexp'
OPERATOR_UNEQUAL  = 'unequal'
OPERATOR_UNLIKE  = 'unlike'
OPERATORS  = [self::OPERATOR_LIKE, self::OPERATOR_UNLIKE, self::OPERATOR_EQUAL, self::OPERATOR_UNEQUAL, self::OPERATOR_LOWER, self::OPERATOR_LOWER_EQUAL, self::OPERATOR_GREATER, self::OPERATOR_GREATER_EQUAL, self::OPERATOR_IN, self::OPERATOR_NOT_IN, self::OPERATOR_IS_NULL, self::OPERATOR_IS_NOT_NULL, self::OPERATOR_IS_EMPTY, self::OPERATOR_IS_NOT_EMPTY, self::OPERATOR_REGEXP, self::OPERATOR_NOT_REGEXP]
SQL_CONDITION_AND  = 'AND'
SQL_CONDITION_OR  = 'OR'

Properties

$framework  : ContaoFrameworkInterface

Methods

__construct()  : mixed
beginTransaction()  : mixed
commitTransaction()  : mixed
composeWhereForQueryBuilder()  : string
computeCondition()  : array<string|int, mixed>
Computes a MySQL condition appropriate for the given operator.
createWhereForSerializedBlob()  : array<string|int, mixed>
Create a where condition for a field that contains a serialized blob.
delete()  : mixed
doBulkInsert()  : mixed
Bulk insert SQL of given data.
findOneResultBy()  : mixed
Return a single database result by table and search criteria.
findResultByPk()  : mixed
Returns a database result for a given table and id(primary key).
findResultsBy()  : mixed
getChildRecords()  : array<string|int, mixed>
insert()  : mixed
processInPieces()  : bool|int
Process a query in pieces, run callback within each cycle.
transformVerboseOperator()  : string|false
Transforms verbose operators to valid MySQL operators (aka junctors).
update()  : mixed
createQueryWithoutRelations()  : string
Adapted from \Contao\Model\QueryBuilder::find().

Constants

NEGATIVE_OPERATORS

public mixed NEGATIVE_OPERATORS = [self::OPERATOR_UNLIKE, self::OPERATOR_UNEQUAL, self::OPERATOR_NOT_IN, self::OPERATOR_IS_NULL, self::OPERATOR_IS_EMPTY, self::OPERATOR_NOT_REGEXP]

ON_DUPLICATE_KEY_IGNORE

public mixed ON_DUPLICATE_KEY_IGNORE = 'IGNORE'

ON_DUPLICATE_KEY_UPDATE

public mixed ON_DUPLICATE_KEY_UPDATE = 'UPDATE'

OPERATOR_GREATER

public mixed OPERATOR_GREATER = 'greater'

OPERATOR_GREATER_EQUAL

public mixed OPERATOR_GREATER_EQUAL = 'greaterequal'

OPERATOR_IS_EMPTY

public mixed OPERATOR_IS_EMPTY = 'isempty'

OPERATOR_IS_NOT_EMPTY

public mixed OPERATOR_IS_NOT_EMPTY = 'isnotempty'

OPERATOR_IS_NOT_NULL

public mixed OPERATOR_IS_NOT_NULL = 'isnotnull'

OPERATOR_IS_NULL

public mixed OPERATOR_IS_NULL = 'isnull'

OPERATOR_LOWER_EQUAL

public mixed OPERATOR_LOWER_EQUAL = 'lowerequal'

OPERATOR_MAPPING

Maps operators of this class to its corresponding Doctrine ExpressionBuilder method.

public mixed OPERATOR_MAPPING = [self::OPERATOR_LIKE => 'like', self::OPERATOR_UNLIKE => 'notLike', self::OPERATOR_EQUAL => 'eq', self::OPERATOR_UNEQUAL => 'neq', self::OPERATOR_LOWER => 'lt', self::OPERATOR_LOWER_EQUAL => 'lte', self::OPERATOR_GREATER => 'gt', self::OPERATOR_GREATER_EQUAL => 'gte', self::OPERATOR_IN => 'in', self::OPERATOR_NOT_IN => 'notIn', self::OPERATOR_IS_NULL => 'isNull', self::OPERATOR_IS_NOT_NULL => 'isNotNull']

OPERATOR_NOT_REGEXP

public mixed OPERATOR_NOT_REGEXP = 'notregexp'

OPERATOR_UNEQUAL

public mixed OPERATOR_UNEQUAL = 'unequal'

OPERATORS

public mixed OPERATORS = [self::OPERATOR_LIKE, self::OPERATOR_UNLIKE, self::OPERATOR_EQUAL, self::OPERATOR_UNEQUAL, self::OPERATOR_LOWER, self::OPERATOR_LOWER_EQUAL, self::OPERATOR_GREATER, self::OPERATOR_GREATER_EQUAL, self::OPERATOR_IN, self::OPERATOR_NOT_IN, self::OPERATOR_IS_NULL, self::OPERATOR_IS_NOT_NULL, self::OPERATOR_IS_EMPTY, self::OPERATOR_IS_NOT_EMPTY, self::OPERATOR_REGEXP, self::OPERATOR_NOT_REGEXP]

SQL_CONDITION_AND

public mixed SQL_CONDITION_AND = 'AND'

Properties

$framework

protected ContaoFrameworkInterface $framework

Methods

__construct()

public __construct(ContaoFrameworkInterface $framework) : mixed
Parameters
$framework : ContaoFrameworkInterface

composeWhereForQueryBuilder()

public composeWhereForQueryBuilder(QueryBuilder $queryBuilder, string $field, string $operator[, array<string|int, mixed> $dca = null ][, null $value = null ][, array<string|int, mixed> $options = [] ]) : string
Parameters
$queryBuilder : QueryBuilder
$field : string
$operator : string
$dca : array<string|int, mixed> = null
$value : null = null
$options : array<string|int, mixed> = []

{wildcardSuffix: string}

Return values
string

computeCondition()

Computes a MySQL condition appropriate for the given operator.

public computeCondition(string $field, string $operator, mixed $value[, string $table = null ][, bool $skipTablePrefix = false ]) : array<string|int, mixed>
Parameters
$field : string
$operator : string
$value : mixed
$table : string = null
$skipTablePrefix : bool = false
Return values
array<string|int, mixed> —

Returns array($strQuery, $arrValues)

createWhereForSerializedBlob()

Create a where condition for a field that contains a serialized blob.

public createWhereForSerializedBlob(string $field, array<string|int, mixed> $values[, string $connective = self::SQL_CONDITION_OR ][, array<string|int, mixed> $options = [] ]) : array<string|int, mixed>

Use Utils service instead

Parameters
$field : string

The field the condition should be checked against accordances

$values : array<string|int, mixed>

The values array to check the field against

$connective : string = self::SQL_CONDITION_OR

SQL_CONDITION_OR | SQL_CONDITION_AND

$options : array<string|int, mixed> = []

Pass additional options.

Options:

  • inline_values: (bool) Inline the values in the sql part instead of using ? ('REGEXP (':"3"')' instead of 'REGEXP (?)'). Return value not change (still an array with an values index)
Return values
array<string|int, mixed>

delete()

public delete(string $table[, string $where = null ][, array<string|int, mixed> $whereValues = [] ][, array<string|int, mixed> $options = [] ]) : mixed
Parameters
$table : string
$where : string = null
$whereValues : array<string|int, mixed> = []
$options : array<string|int, mixed> = []

doBulkInsert()

Bulk insert SQL of given data.

public doBulkInsert(string $table[, array<string|int, mixed> $data = [] ][, array<string|int, mixed> $fixedValues = [] ][, mixed $onDuplicateKey = null ][, callable $callback = null ][, callable $itemCallback = null ][, int $bulkSize = 100 ][, string $pk = 'id' ]) : mixed
Parameters
$table : string

The database table, where new items should be stored inside

$data : array<string|int, mixed> = []

An array of values associated to its field

$fixedValues : array<string|int, mixed> = []

A array of fixed values associated to its field that should be set for each row as fixed values

$onDuplicateKey : mixed = null

null = Throw error on duplicates, self::ON_DUPLICATE_KEY_IGNORE = ignore error duplicates (skip this entries), self::ON_DUPLICATE_KEY_UPDATE = update existing entries

$callback : callable = null

A callback that should be triggered after each cycle, contains $arrValues of current cycle

$itemCallback : callable = null

A callback to change the insert values for each items, contains $arrValues as first argument, $arrFields as second, $arrOriginal as third, expects an array as return value with same order as $arrFields, if no array is returned, insert of the row will be skipped item insert

$bulkSize : int = 100

The bulk size

$pk : string = 'id'

The primary key of the current table (default: id)

findOneResultBy()

Return a single database result by table and search criteria.

public findOneResultBy(string $table, array<string|int, mixed>|null $columns, array<string|int, mixed>|null $values[, array<string|int, mixed> $options = [] ]) : mixed
Parameters
$table : string
$columns : array<string|int, mixed>|null
$values : array<string|int, mixed>|null
$options : array<string|int, mixed> = []

findResultByPk()

Returns a database result for a given table and id(primary key).

public findResultByPk(string $table, mixed $pk[, array<string|int, mixed> $options = [] ]) : mixed
Parameters
$table : string
$pk : mixed
$options : array<string|int, mixed> = []

findResultsBy()

public findResultsBy(string $table, array<string|int, mixed>|null $columns, array<string|int, mixed>|null $values[, array<string|int, mixed> $options = [] ]) : mixed
Parameters
$table : string
$columns : array<string|int, mixed>|null
$values : array<string|int, mixed>|null
$options : array<string|int, mixed> = []

getChildRecords()

public getChildRecords(array<string|int, mixed> $parentIds, string $table[, array<string|int, mixed> $options = [] ]) : array<string|int, mixed>
Parameters
$parentIds : array<string|int, mixed>
$table : string
$options : array<string|int, mixed> = []
Return values
array<string|int, mixed>

insert()

public insert(string $table, array<string|int, mixed> $set[, array<string|int, mixed> $options = [] ]) : mixed
Parameters
$table : string
$set : array<string|int, mixed>
$options : array<string|int, mixed> = []

processInPieces()

Process a query in pieces, run callback within each cycle.

public processInPieces(string $countQuery, string $query[, callable $callback = null ][, string $key = null ][, int $bulkSize = 5000 ]) : bool|int
Parameters
$countQuery : string

The query that count the total rows, must contain "Select COUNT(*) as total"

$query : string

The query, with the rows that should be iterated over

$callback : callable = null

A callback that should be triggered after each cycle, contains $arrRows of current cycle

$key : string = null

The key of the value that should be set as key identifier for the returned result array entries

$bulkSize : int = 5000

The bulk size

Return values
bool|int —

False if nothing to do, otherwise return the total number of processes entities

transformVerboseOperator()

Transforms verbose operators to valid MySQL operators (aka junctors).

public transformVerboseOperator(string $verboseOperator) : string|false

Supports: like, unlike, equal, unequal, lower, greater, lowerequal, greaterequal, in, notin.

Parameters
$verboseOperator : string
Return values
string|false —

The transformed operator or false if not supported

update()

public update(string $table, array<string|int, mixed> $set[, string $where = null ][, array<string|int, mixed> $whereValues = [] ][, array<string|int, mixed> $options = [] ]) : mixed
Parameters
$table : string
$set : array<string|int, mixed>
$where : string = null
$whereValues : array<string|int, mixed> = []
$options : array<string|int, mixed> = []

createQueryWithoutRelations()

Adapted from \Contao\Model\QueryBuilder::find().

private createQueryWithoutRelations(array<string|int, mixed> $options) : string
Parameters
$options : array<string|int, mixed>
Return values
string 

        

        
    




    

    

                                

                

                                
    

        On this page

        
    


                

                            

            

    

        

            
Search results