PHPXRef 0.7.1 : Joomla 4.2.2 documentation : Detail view of DatabaseQuery.php

Functions

Functions that are not part of a class:

__construct(DatabaseInterface $db = null) X-Ref

Class constructor.

param: DatabaseInterface $db The database driver.

__toString() X-Ref

Magic function to convert the query to a string.

return: string The completed query.

__get($name) X-Ref

Magic function to get protected variable value

param: string $name The name of the variable.
return: mixed

call($columns) X-Ref

Add a single column, or array of columns to the CALL clause of the query.

Usage:
$query->call('a.*')->call('b.id');
$query->call(array('a.*', 'b.id'));

param: mixed $columns A string or an array of field names.
return: $this

castAs(string $type, string $value, ?string $length = null) X-Ref

Casts a value to a char.

Ensure that the value is properly quoted before passing to the method.

Usage:
$query->select($query->castAs('CHAR', 'a'));

param: string  $type    The type of string to cast as.
param: string  $value   The value to cast as a char.
param: string  $length  Optionally specify the length of the field (if the type supports it otherwise
return: string  SQL statement to cast the value as a char type.

castAsChar($value) X-Ref

Casts a value to a char.

Ensure that the value is properly quoted before passing to the method.

Usage:
$query->select($query->castAsChar('a'));

param: string $value The value to cast as a char.
return: string SQL statement to cast the value as a char type.

charLength($field, $operator = null, $condition = null) X-Ref

Gets the number of characters in a string.

Note, use 'length' to find the number of bytes in a string.

Usage:
$query->select($query->charLength('a'));

param: string       $field      A value.
param: string|null  $operator   Comparison operator between charLength integer value and $condition
param: string|null  $condition  Integer value to compare charLength with.
return: string  The required char length call.

clear($clause = null) X-Ref

Clear data from the query or a specific clause of the query.

param: string $clause Optionally, the name of the clause to clear, or nothing to clear the whole query.
return: $this

columns($columns) X-Ref

Adds a column, or array of column names that would be used for an INSERT INTO statement.

param: array|string $columns A column name, or array of column names.
return: $this

concatenate($values, $separator = null) X-Ref

Concatenates an array of column names or values.

Usage:
$query->select($query->concatenate(array('a', 'b')));

param: string[]     $values     An array of values to concatenate.
param: string|null  $separator  As separator to place between each value.
return: string  The concatenated values.

currentTimestamp() X-Ref

Gets the current date and time.

Usage:
$query->where('published_up < '.$query->currentTimestamp());

return: string

dateAdd($date, $interval, $datePart) X-Ref

Add to the current date and time.

Usage:
$query->select($query->dateAdd());

Prefixing the interval with a - (negative sign) will cause subtraction to be used.
Note: Not all drivers support all units.

param: string  $date      The db quoted string representation of the date to add to. May be date or datetime
param: string  $interval  The string representation of the appropriate number of units
param: string  $datePart  The part of the date to perform the addition on
return: string  The string with the appropriate sql for addition of dates

dateFormat() X-Ref

Returns a PHP date() function compliant date format for the database driver.

This method is provided for use where the query object is passed to a function for modification.
If you have direct access to the database object, it is recommended you use the getDateFormat method directly.

return: string The format string.

dump() X-Ref

Creates a HTML formatted dump of the query for debugging purposes.

Usage:
echo $query->dump();

return: string

delete($table = null) X-Ref

Add a table name to the DELETE clause of the query.

Usage:
$query->delete('#__a')->where('id = 1');

param: string $table The name of the table to delete from.
return: $this

e($text, $extra = false) X-Ref

Alias for escape method

param: string   $text   The string to be escaped.
param: boolean  $extra  Optional parameter to provide extra escaping.
return: string  The escaped string.

escape($text, $extra = false) X-Ref

Method to escape a string for usage in an SQL statement.

This method is provided for use where the query object is passed to a function for modification.
If you have direct access to the database object, it is recommended you use the escape method directly.

Note that 'e' is an alias for this method as it is in DatabaseDriver.

param: string   $text   The string to be escaped.
param: boolean  $extra  Optional parameter to provide extra escaping.
return: string  The escaped string.

exec($columns) X-Ref

Add a single column, or array of columns to the EXEC clause of the query.

Usage:
$query->exec('a.*')->exec('b.id');
$query->exec(array('a.*', 'b.id'));

param: array|string $columns A string or an array of field names.
return: $this

findInSet($value, $set) X-Ref

Find a value in a varchar used like a set.

Ensure that the value is an integer before passing to the method.

Usage:
$query->findInSet((int) $parent->id, 'a.assigned_cat_ids')

param: string  $value  The value to search for.
param: string  $set    The set of values.
return: string  A representation of the MySQL find_in_set() function for the driver.

from($table) X-Ref

Add a table to the FROM clause of the query.

Usage:
$query->select('*')->from('#__a');
$query->select('*')->from($subquery->alias('a'));

param: string|DatabaseQuery $table The name of the table or a DatabaseQuery object (or a child of it) with alias set.
return: $this

alias($alias) X-Ref

Add alias for current query.

Usage:
$query->select('*')->from('#__a')->alias('subquery');

param: string $alias Alias used for a JDatabaseQuery.
return: $this

year($date) X-Ref

Used to get a string to extract year from date column.

Usage:
$query->select($query->year($query->quoteName('dateColumn')));

param: string $date Date column containing year to be extracted.
return: string Returns string to extract year from a date.

month($date) X-Ref

Used to get a string to extract month from date column.

Usage:
$query->select($query->month($query->quoteName('dateColumn')));

param: string $date Date column containing month to be extracted.
return: string Returns string to extract month from a date.

day($date) X-Ref

Used to get a string to extract day from date column.

Usage:
$query->select($query->day($query->quoteName('dateColumn')));

param: string $date Date column containing day to be extracted.
return: string Returns string to extract day from a date.

hour($date) X-Ref

Used to get a string to extract hour from date column.

Usage:
$query->select($query->hour($query->quoteName('dateColumn')));

param: string $date Date column containing hour to be extracted.
return: string Returns string to extract hour from a date.

minute($date) X-Ref

Used to get a string to extract minute from date column.

Usage:
$query->select($query->minute($query->quoteName('dateColumn')));

param: string $date Date column containing minute to be extracted.
return: string Returns string to extract minute from a date.

second($date) X-Ref

Used to get a string to extract seconds from date column.

Usage:
$query->select($query->second($query->quoteName('dateColumn')));

param: string $date Date column containing second to be extracted.
return: string Returns string to extract second from a date.

group($columns) X-Ref

Add a grouping column to the GROUP clause of the query.

Usage:
$query->group('id');

param: array|string $columns A string or array of ordering columns.
return: $this

having($conditions, $glue = 'AND') X-Ref

A conditions to the HAVING clause of the query.

Usage:
$query->group('id')->having('COUNT(id) > 5');

param: array|string $conditions A string or array of columns.
param: string $glue The glue by which to join the conditions. Defaults to AND.
return: $this

insert($table, $incrementField = false) X-Ref

Add a table name to the INSERT clause of the query.

Usage:
$query->insert('#__a')->set('id = 1');
$query->insert('#__a')->columns('id, title')->values('1,2')->values('3,4');
$query->insert('#__a')->columns('id, title')->values(array('1,2', '3,4'));

param: string $table The name of the table to insert data into.
param: boolean $incrementField The name of the field to auto increment.
return: $this

join($type, $table, $condition = null) X-Ref

Add a JOIN clause to the query.

Usage:
$query->join('INNER', 'b', 'b.id = a.id);

param: string  $type       The type of join. This string is prepended to the JOIN keyword.
param: string  $table      The name of table.
param: string  $condition  The join condition.
return: $this

innerJoin($table, $condition = null) X-Ref

Add an INNER JOIN clause to the query.

Usage:
$query->innerJoin('b', 'b.id = a.id')->innerJoin('c', 'c.id = b.id');

param: string $table The name of table.
param: string $condition The join condition.
return: $this

outerJoin($table, $condition = null) X-Ref

Add an OUTER JOIN clause to the query.

Usage:
$query->outerJoin('b', 'b.id = a.id')->leftJoin('c', 'c.id = b.id');

param: string $table The name of table.
param: string $condition The join condition.
return: $this

leftJoin($table, $condition = null) X-Ref

Add a LEFT JOIN clause to the query.

Usage:
$query->leftJoin('b', 'b.id = a.id')->leftJoin('c', 'c.id = b.id');

param: string $table The name of table.
param: string $condition The join condition.
return: $this

rightJoin($table, $condition = null) X-Ref

Add a RIGHT JOIN clause to the query.

Usage:
$query->rightJoin('b', 'b.id = a.id')->rightJoin('c', 'c.id = b.id');

param: string $table The name of table.
param: string $condition The join condition.
return: $this

length($value) X-Ref

Get the length of a string in bytes.

Note, use 'charLength' to find the number of characters in a string.

Usage:
query->where($query->length('a').' > 3');

param: string $value The string to measure.
return: integer

nullDate($quoted = true) X-Ref

Get the null or zero representation of a timestamp for the database driver.

This method is provided for use where the query object is passed to a function for modification.
If you have direct access to the database object, it is recommended you use the nullDate method directly.

Usage:
$query->where('modified_date <> '.$query->nullDate());

param: boolean $quoted Optionally wraps the null date in database quotes (true by default).
return: string Null or zero representation of a timestamp.

isNullDatetime($column) X-Ref

Generate a SQL statement to check if column represents a zero or null datetime.

Usage:
$query->where($query->isNullDatetime('modified_date'));

param: string $column A column name.
return: string

order($columns) X-Ref

Add a ordering column to the ORDER clause of the query.

Usage:
$query->order('foo')->order('bar');
$query->order(array('foo','bar'));

param: array|string $columns A string or array of ordering columns.
return: $this

q($text, $escape = true) X-Ref

Alias for quote method

param: array|string  $text    A string or an array of strings to quote.
param: boolean       $escape  True (default) to escape the string, false to leave it unchanged.
return: string  The quoted input string.

quote($text, $escape = true) X-Ref

Method to quote and optionally escape a string to database requirements for insertion into the database.

This method is provided for use where the query object is passed to a function for modification.
If you have direct access to the database object, it is recommended you use the quote method directly.

Note that 'q' is an alias for this method as it is in DatabaseDriver.

Usage:
$query->quote('fulltext');
$query->q('fulltext');
$query->q(array('option', 'fulltext'));

param: array|string  $text    A string or an array of strings to quote.
param: boolean       $escape  True (default) to escape the string, false to leave it unchanged.
return: string  The quoted input string.

qn($name, $as = null) X-Ref

Alias for quoteName method

param: array|string  $name  The identifier name to wrap in quotes, or an array of identifier names to wrap in quotes.
param: array|string  $as    The AS query part associated to $name. It can be string or array, in latter case it has to be
return: array|string  The quote wrapped name, same type of $name.

quoteName($name, $as = null) X-Ref

Wrap an SQL statement identifier name such as column, table or database names in quotes to prevent injection
risks and reserved word conflicts.

This method is provided for use where the query object is passed to a function for modification.
If you have direct access to the database object, it is recommended you use the quoteName method directly.

Note that 'qn' is an alias for this method as it is in DatabaseDriver.

Usage:
$query->quoteName('#__a');
$query->qn('#__a');

param: array|string  $name  The identifier name to wrap in quotes, or an array of identifier names to wrap in quotes.
param: array|string  $as    The AS query part associated to $name. It can be string or array, in latter case it has to be
return: array|string  The quote wrapped name, same type of $name.

rand() X-Ref

Get the function to return a random floating-point value

Usage:
$query->rand();

return: string

regexp($value) X-Ref

Get the regular expression operator

Usage:
$query->where('field ' . $query->regexp($search));

param: string $value The regex pattern.
return: string

select($columns) X-Ref

Add a single column, or array of columns to the SELECT clause of the query.

Note that you must not mix insert, update, delete and select method calls when building a query.
The select method can, however, be called multiple times in the same query.

Usage:
$query->select('a.*')->select('b.id');
$query->select(array('a.*', 'b.id'));

param: array|string $columns A string or an array of field names.
return: $this

set($conditions, $glue = ',') X-Ref

Add a single condition string, or an array of strings to the SET clause of the query.

Usage:
$query->set('a = 1')->set('b = 2');
$query->set(array('a = 1', 'b = 2');

param: array|string $conditions A string or array of string conditions.
param: string $glue The glue by which to join the condition strings. Defaults to ,.
return: $this

setLimit($limit = 0, $offset = 0) X-Ref

Sets the offset and limit for the result set, if the database driver supports it.

Usage:
$query->setLimit(100, 0); (retrieve 100 rows, starting at first record)
$query->setLimit(50, 50); (retrieve 50 rows, starting at 50th record)

param: integer $limit The limit for the result set
param: integer $offset The offset for the result set
return: $this

setQuery($sql) X-Ref

Allows a direct query to be provided to the database driver's setQuery() method, but still allow queries
to have bounded variables.

Usage:
$query->setQuery('select * from #__users');

param: DatabaseQuery|string $sql A SQL query string or DatabaseQuery object
return: $this

update($table) X-Ref

Add a table name to the UPDATE clause of the query.

Usage:
$query->update('#__foo')->set(...);

param: string $table A table to update.
return: $this

values($values) X-Ref

Adds a tuple, or array of tuples that would be used as values for an INSERT INTO statement.

Usage:
$query->values('1,2,3')->values('4,5,6');
$query->values(array('1,2,3', '4,5,6'));

param: array|string $values A single tuple, or array of tuples.
return: $this

where($conditions, $glue = 'AND') X-Ref

Add a single condition, or an array of conditions to the WHERE clause of the query.

Usage:
$query->where('a = 1')->where('b = 2');
$query->where(array('a = 1', 'b = 2'));

param: array|string $conditions A string or array of where conditions.
param: string $glue The glue by which to join the conditions. Defaults to AND.
return: $this

whereIn(string $keyName, array $keyValues, $dataType = ParameterType::INTEGER) X-Ref

Add a WHERE IN statement to the query.

Note that all values must be the same data type.

Usage
$query->whereIn('id', [1, 2, 3]);

param: string        $keyName    Key name for the where clause
param: array         $keyValues  Array of values to be matched
param: array|string  $dataType   Constant corresponding to a SQL datatype. It can be an array, in this case it
return: $this

whereNotIn(string $keyName, array $keyValues, $dataType = ParameterType::INTEGER) X-Ref

Add a WHERE NOT IN statement to the query.

Note that all values must be the same data type.

Usage
$query->whereNotIn('id', [1, 2, 3]);

param: string        $keyName    Key name for the where clause
param: array         $keyValues  Array of values to be matched
param: array|string  $dataType   Constant corresponding to a SQL datatype. It can be an array, in this case it
return: $this

extendWhere($outerGlue, $conditions, $innerGlue = 'AND') X-Ref

Extend the WHERE clause with a single condition or an array of conditions, with a potentially
different logical operator from the one in the current WHERE clause.

Usage:
$query->where(array('a = 1', 'b = 2'))->extendWhere('XOR', array('c = 3', 'd = 4'));
will produce: WHERE ((a = 1 AND b = 2) XOR (c = 3 AND d = 4)

param: string  $outerGlue   The glue by which to join the conditions to the current WHERE conditions.
param: mixed   $conditions  A string or array of WHERE conditions.
param: string  $innerGlue   The glue by which to join the conditions. Defaults to AND.
return: $this

orWhere($conditions, $glue = 'AND') X-Ref

Extend the WHERE clause with an OR and a single condition or an array of conditions.

Usage:
$query->where(array('a = 1', 'b = 2'))->orWhere(array('c = 3', 'd = 4'));
will produce: WHERE ((a = 1 AND b = 2) OR (c = 3 AND d = 4)

param: mixed $conditions A string or array of WHERE conditions.
param: string $glue The glue by which to join the conditions. Defaults to AND.
return: $this

andWhere($conditions, $glue = 'OR') X-Ref

Extend the WHERE clause with an AND and a single condition or an array of conditions.

Usage:
$query->where(array('a = 1', 'b = 2'))->andWhere(array('c = 3', 'd = 4'));
will produce: WHERE ((a = 1 AND b = 2) AND (c = 3 OR d = 4)

param: mixed $conditions A string or array of WHERE conditions.
param: string $glue The glue by which to join the conditions. Defaults to OR.
return: $this

bind($key, &$value, $dataType = ParameterType::STRING, $length = 0, $driverOptions = []) X-Ref

Method to add a variable to an internal array that will be bound to a prepared SQL statement before query execution.

param: array|string|integer  $key            The key that will be used in your SQL query to reference the value. Usually of
param: mixed                 $value          The value that will be bound. It can be an array, in this case it has to be
param: array|string          $dataType       Constant corresponding to a SQL datatype. It can be an array, in this case it
param: integer               $length         The length of the variable. Usually required for OUTPUT parameters.
param: array                 $driverOptions  Optional driver options to be used.
return: $this

unbind($key) X-Ref

Method to unbind a bound variable.

param: array|string|integer $key The key or array of keys to unbind.
return: $this

bindArray(array $values, $dataType = ParameterType::INTEGER) X-Ref

Binds an array of values and returns an array of prepared parameter names.

Note that all values must be the same data type.

Usage:
$query->where('column in (' . implode(',', $query->bindArray($keyValues, $dataType)) . ')');

param: array         $values    Values to bind
param: array|string  $dataType  Constant corresponding to a SQL datatype. It can be an array, in this case it
return: array   An array with parameter names

__clone() X-Ref

Method to provide basic copy support.

Any object pushed into the data of this class should have its own __clone() implementation.
This method does not support copying objects in a multidimensional array.

return: void

getBounded($key = null) X-Ref

Retrieves the bound parameters array when key is null and returns it by reference. If a key is provided then that item is
returned.

param: mixed $key The bounded variable key to retrieve.
return: mixed

merge($name, $query) X-Ref

Combine a select statement to the current query by one of the set operators.
Operators: UNION, UNION ALL, EXCEPT or INTERSECT.

param: string $name The name of the set operator with parentheses.
param: DatabaseQuery|string $query The DatabaseQuery object or string.
return: $this

union($query, $distinct = true) X-Ref

Add a query to UNION with the current query.

Usage:
$query->union('SELECT name FROM  #__foo')
$query->union('SELECT name FROM  #__foo', true)

param: DatabaseQuery|string  $query     The DatabaseQuery object or string to union.
param: boolean               $distinct  True to only return distinct rows from the union.
return: $this

unionAll($query) X-Ref

Add a query to UNION ALL with the current query.

Usage:
$query->unionAll('SELECT name FROM #__foo')

param: DatabaseQuery|string $query The DatabaseQuery object or string to union.
return: $this

querySet($query) X-Ref

Set a single query to the query set.
On this type of DatabaseQuery you can use union(), unionAll(), order() and setLimit()

Usage:
$query->querySet($query2->select('name')->from('#__foo')->order('id DESC')->setLimit(1))
->unionAll($query3->select('name')->from('#__foo')->order('id')->setLimit(1))
->order('name')
->setLimit(1)

param: DatabaseQuery $query The DatabaseQuery object or string.
return: $this

toQuerySet() X-Ref

Create a DatabaseQuery object of type querySet from current query.

Usage:
$query->select('name')->from('#__foo')->order('id DESC')->setLimit(1)
->toQuerySet()
->unionAll($query2->select('name')->from('#__foo')->order('id')->setLimit(1))
->order('name')
->setLimit(1)

return: DatabaseQuery A new object of the DatabaseQuery.

format($format) X-Ref

Find and replace sprintf-like tokens in a format string.
Each token takes one of the following forms:
%%       - A literal percent character.
%[t]     - Where [t] is a type specifier.
%[n]$[x] - Where [n] is an argument specifier and [t] is a type specifier.

Types:
a - Numeric: Replacement text is coerced to a numeric type but not quoted or escaped.
e - Escape: Replacement text is passed to $this->escape().
E - Escape (extra): Replacement text is passed to $this->escape() with true as the second argument.
n - Name Quote: Replacement text is passed to $this->quoteName().
q - Quote: Replacement text is passed to $this->quote().
Q - Quote (no escape): Replacement text is passed to $this->quote() with false as the second argument.
r - Raw: Replacement text is used as-is. (Be careful)

Date Types:
- Replacement text automatically quoted (use uppercase for Name Quote).
- Replacement text should be a string in date format or name of a date column.
y/Y - Year
m/M - Month
d/D - Day
h/H - Hour
i/I - Minute
s/S - Second

Invariable Types:
- Takes no argument.
- Argument index not incremented.
t - Replacement text is the result of $this->currentTimestamp().
z - Replacement text is the result of $this->nullDate(false).
Z - Replacement text is the result of $this->nullDate(true).

Usage:
$query->format('SELECT %1$n FROM %2$n WHERE %3$n = %4$a', 'foo', '#__foo', 'bar', 1);
Returns: SELECT `foo` FROM `#__foo` WHERE `bar` = 1

Notes:
The argument specifier is optional but recommended for clarity.
The argument index used for unspecified tokens is incremented only when used.

param: string  $format  The formatting string.
return: string  Returns a string produced according to the formatting string.

validateRowNumber($orderBy, $orderColumnAlias) X-Ref

Validate arguments which are passed to selectRowNumber method and set up common variables.

param: string $orderBy An expression of ordering for window function.
param: string $orderColumnAlias An alias for new ordering column.
return: void

selectRowNumber($orderBy, $orderColumnAlias) X-Ref

Return the number of the current row.

Usage:
$query->select('id');
$query->selectRowNumber('ordering,publish_up DESC', 'new_ordering');
$query->from('#__content');

param: string $orderBy An expression of ordering for window function.
param: string $orderColumnAlias An alias for new ordering column.
return: $this

Copyright:	Copyright (C) 2005 - 2021 Open Source Matters, Inc. All rights reserved.
License:	GNU General Public License version 2 or later; see LICENSE
File Size:	2487 lines (61 kb)
Included or required:	0 times
Referenced:	0 times
Includes or requires:	0 files

PHP Cross Reference of Joomla 4.2.2 documentation

/libraries/vendor/joomla/database/src/ -> DatabaseQuery.php (summary)

Defines 70 functions