Joomla! API

Convert the query object to a string.

__toString() :

since	2.0.0

Response

string

Add alias for current query.

alias( alias) :

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

since	2.0.0

Arguments

alias: stringAlias used for a JDatabaseQuery.

Response

$this

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

bind(array|string|int key,  &value, array|string dataType = ParameterType::STRING,  length, mixed||string|int driverOptions = []) :

inherited

since	1.0

Arguments

key: array<string|int, mixed>|string|intThe key that will be used in your SQL query to reference the value. Usually of the form ':key', but can also be an integer.
value: mixedThe value that will be bound. It can be an array, in this case it has to be same length of $key; The value is passed by reference to support output parameters such as those possible with stored procedures.
dataType: array<string|int, mixed>|stringConstant corresponding to a SQL datatype. It can be an array, in this case it has to be same length of $key
length: intThe length of the variable. Usually required for OUTPUT parameters.
driverOptions: array<string|int, mixed>Optional driver options to be used.

Response

$this

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

bindArray(mixed||string|int values, array|string dataType = ParameterType::INTEGER) : mixed||string|int

Note that all values must be the same data type.

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

since	2.0.0

Arguments

values: array<string|int, mixed>Values to bind
dataType: array<string|int, mixed>|stringConstant corresponding to a SQL datatype. It can be an array, in this case it has to be same length of $key

Response

array<string|int, mixed>An array with parameter names

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

call(array|string columns) :

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

since	2.0.0
throws	`QueryTypeAlreadyDefinedException`if the query type has already been defined

Arguments

columns: array<string|int, mixed>|stringA string or an array of field names.

Response

$this

Casts a value to a specified type.

castAs( type,  value,  length = null) :

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

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

since	2.0.0
throws	`UnknownTypeException`When unsupported cast for a database driver

Arguments

type: stringThe type of string to cast as.
value: stringThe value to cast as a char.
length: stringOptionally specify the length of the field (if the type supports it otherwise ignored).

Response

stringSQL statement to cast the value as a char type.

Gets the number of characters in a string.

charLength( field, string|null operator = null, string|null condition = null) :

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

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

since	2.0.0

Arguments

field: stringA value.
operator: string|nullComparison operator between charLength integer value and $condition
condition: string|nullInteger value to compare charLength with.

Response

stringSQL statement to get the length of a character.

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

clear( clause = null) :

since	2.0.0

Arguments

clause: stringOptionally, the name of the clause to clear, or nothing to clear the whole query.

Response

$this

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

columns(array|string columns) :

since	2.0.0

Arguments

columns: array<string|int, mixed>|stringA column name, or array of column names.

Response

$this

Concatenates an array of column names or values.

concatenate(string||string|int values, string|null separator = null) :

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

since	2.0.0

Arguments

values: array<string|int, string>An array of values to concatenate.
separator: string|nullAs separator to place between each value.

Response

stringSQL statement representing the concatenated values.

Gets the current date and time.

currentTimestamp() :

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

since	2.0.0

Response

stringSQL statement to get the current timestamp.

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

day( date) :

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

since	2.0.0

Arguments

date: stringDate column containing day to be extracted.

Response

stringSQL statement to get the day from a date value.

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

delete( table = null) :

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

since	2.0.0
throws	`QueryTypeAlreadyDefinedException`if the query type has already been defined

Arguments

table: stringThe name of the table to delete from.

Response

$this

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

exec(array|string columns) :

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

since	2.0.0
throws	`QueryTypeAlreadyDefinedException`if the query type has already been defined

Arguments

columns: array<string|int, mixed>|stringA string or an array of field names.

Response

$this

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.

extendWhere( outerGlue,  conditions,  innerGlue = 'AND') :

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)

since	2.0.0

Arguments

outerGlue: stringThe glue by which to join the conditions to the current WHERE conditions.
conditions: mixedA string or array of WHERE conditions.
innerGlue: stringThe glue by which to join the conditions. Defaults to AND.

Response

$this

Find a value in a varchar used like a set.

findInSet( value,  set) :

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

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

since	2.0.0

Arguments

value: stringThe value to search for.
set: stringThe set of values.

Response

stringA representation of the MySQL find_in_set() function for the driver.

Add a table to the FROM clause of the query.

from(string|\Joomla\Database\QueryInterface table) :

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

since	2.0.0

Arguments

table: string|QueryInterfaceThe name of the table or a QueryInterface object (or a child of it) with alias set.

Response

$this

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

getBounded( key = null) :

inherited

since	1.0

Arguments

key: mixedThe bounded variable key to retrieve.

Response

mixed

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

group(array|string columns) :

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

since	2.0.0

Arguments

columns: array<string|int, mixed>|stringA string or array of ordering columns.

Response

$this

Aggregate function to get input values concatenated into a string, separated by delimiter

groupConcat( expression,  separator = ',') :

Usage: $query->groupConcat('id', ',');

since	2.0.0

Arguments

expression: stringThe expression to apply concatenation to, this may be a column name or complex SQL statement.
separator: stringThe delimiter of each concatenated value

Response

stringInput values concatenated into a string, separated by delimiter

A conditions to the HAVING clause of the query.

having(array|string conditions,  glue = 'AND') :

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

since	2.0.0

Arguments

conditions: array<string|int, mixed>|stringA string or array of columns.
glue: stringThe glue by which to join the conditions. Defaults to AND.

Response

$this

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

hour( date) :

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

since	2.0.0

Arguments

date: stringDate column containing hour to be extracted.

Response

stringSQL statement to get the hour from a date/time value.

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

insert( table,  incrementField = false) :

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'));

since	2.0.0
throws	`QueryTypeAlreadyDefinedException`if the query type has already been defined

Arguments

table: stringThe name of the table to insert data into.
incrementField: boolThe name of the field to auto increment.

Response

$this

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

isNullDatetime( column) :

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

since	2.0.0

Arguments

column: stringA column name.

Response

string

Add a JOIN clause to the query.

join( type,  table,  condition = null) :

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

since	2.0.0

Arguments

type: stringThe type of join. This string is prepended to the JOIN keyword.
table: stringThe name of table.
condition: stringThe join condition.

Response

$this

Get the length of a string in bytes.

length( value) :

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

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

since	2.0.0

Arguments

value: stringThe string to measure.

Response

int

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

minute( date) :

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

since	2.0.0

Arguments

date: stringDate column containing minute to be extracted.

Response

stringSQL statement to get the minute from a date/time value.

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

month( date) :

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

since	2.0.0

Arguments

date: stringDate column containing month to be extracted.

Response

stringSQL statement to get the month from a date value.

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

nullDate( quoted = true) :

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());

since	2.0.0
throws	`RuntimeException`

Arguments

quoted: boolOptionally wraps the null date in database quotes (true by default).

Response

stringNull or zero representation of a timestamp.

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

order(array|string columns) :

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

since	2.0.0

Arguments

columns: array<string|int, mixed>|stringA string or array of ordering columns.

Response

$this

Method to modify a query already in string format with the needed additions to make the query limited to a particular number of results, or start at a particular offset.

processLimit( query,  limit,  offset) :

inherited

since	1.0

Arguments

query: stringThe query in string format
limit: intThe limit for the result set
offset: intThe offset for the result set

Response

string

Set a single query to the query set.

querySet(\Joomla\Database\DatabaseQuery|string query) :

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)

since	2.0.0

Arguments

query: DatabaseQuery|stringThe DatabaseQuery object or string.

Response

$this

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

quoteName(array|string name, array|string as = null) : array|string

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');

since	1.0
throws	`RuntimeException`if the internal db property is not a valid object.

Arguments

name: array<string|int, mixed>|stringThe identifier name to wrap in quotes, or an array of identifier names to wrap in quotes. Each type supports dot-notation name.
as: array<string|int, mixed>|stringThe AS query part associated to $name. It can be string or array, in latter case it has to be same length of $name; if is null there will not be any AS part for string or array element.

Response

array<string|int, mixed>|stringThe quote wrapped name, same type of $name.

Get the function to return a random floating-point value

rand() :

Usage: $query->rand();

since	2.0.0

Response

string

Get the regular expression operator

regexp( value) :

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

since	2.0.0

Arguments

value: stringThe regex pattern.

Response

string

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

second( date) :

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

since	2.0.0

Arguments

date: stringDate column containing second to be extracted.

Response

stringSQL statement to get the second from a date/time value.

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

select(array|string columns) :

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

since	2.0.0
throws	`QueryTypeAlreadyDefinedException`if the query type has already been defined

Arguments

columns: array<string|int, mixed>|stringA string or an array of field names.

Response

$this

Return the number of the current row.

selectRowNumber( orderBy,  orderColumnAlias) :

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

since	2.0.0
throws	`RuntimeException`

Arguments

orderBy: stringAn expression of ordering for window function.
orderColumnAlias: stringAn alias for new ordering column.

Response

$this

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

set(array|string conditions,  glue = ',') :

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

since	2.0.0

Arguments

conditions: array<string|int, mixed>|stringA string or array of string conditions.
glue: stringThe glue by which to join the condition strings. Defaults to ,. Note that the glue is set on first use and cannot be changed.

Response

$this

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

setLimit( limit,  offset) :

inherited

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

since	1.0

Arguments

limit: intThe limit for the result set
offset: intThe offset for the result set

Response

$this

Create a DatabaseQuery object of type querySet from current query.

toQuerySet() : \Joomla\Database\DatabaseQuery

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)

since	2.0.0

Response

DatabaseQueryA new object of the DatabaseQuery.

Method to unbind a bound variable.

unbind(array|string|int key) :

inherited

since	2.0.0

Arguments

key: array<string|int, mixed>|string|intThe key or array of keys to unbind.

Response

$this

Add a query to UNION with the current query.

union(\Joomla\Database\DatabaseQuery|string query,  distinct = true) :

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

since	1.0

Arguments

query: DatabaseQuery|stringThe DatabaseQuery object or string to union.
distinct: boolTrue to only return distinct rows from the union.

Response

$this

Add a query to UNION ALL with the current query.

unionAll(\Joomla\Database\DatabaseQuery|string query) :

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

see	union
since	1.5.0

Arguments

query: DatabaseQuery|stringThe DatabaseQuery object or string to union.

Response

$this

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

update( table) :

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

since	2.0.0
throws	`QueryTypeAlreadyDefinedException`if the query type has already been defined

Arguments

table: stringA table to update.

Response

$this

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

values(array|string values) :

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

since	2.0.0

Arguments

values: array<string|int, mixed>|stringA single tuple, or array of tuples.

Response

$this

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

where(array|string conditions,  glue = 'AND') :

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

since	2.0.0

Arguments

conditions: array<string|int, mixed>|stringA string or array of where conditions.
glue: stringThe glue by which to join the conditions. Defaults to AND. Note that the glue is set on first use and cannot be changed.

Response

$this

Add a WHERE IN statement to the query.

whereIn( keyName, mixed||string|int keyValues, array|string dataType = ParameterType::INTEGER) :

Note that all values must be the same data type.

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

since	2.0.0

Arguments

keyName: stringKey name for the where clause
keyValues: array<string|int, mixed>Array of values to be matched
dataType: array<string|int, mixed>|stringConstant corresponding to a SQL datatype. It can be an array, in this case it has to be same length of $keyValues

Response

$this

Add a WHERE NOT IN statement to the query.

whereNotIn( keyName, mixed||string|int keyValues, array|string dataType = ParameterType::INTEGER) :

Note that all values must be the same data type.

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

since	2.0.0

Arguments

keyName: stringKey name for the where clause
keyValues: array<string|int, mixed>Array of values to be matched
dataType: array<string|int, mixed>|stringConstant corresponding to a SQL datatype. It can be an array, in this case it has to be same length of $keyValues

Response

$this

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

year( date) :

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

since	2.0.0

Arguments

date: stringDate column containing year to be extracted.

Response

stringSQL statement to get the year from a date value.

since	2.0.0
package	Joomla Framework

QueryInterface

Methods

__toString

Response

alias

Arguments

Response

bind

Arguments

Response

bindArray

Arguments

Response

call

Arguments

Response

castAs

Arguments

Response

charLength

Arguments

Response

clear

Arguments

Response

columns

Arguments

Response

concatenate

Arguments

Response

currentTimestamp

Response

day

Arguments

Response

delete

Arguments

Response

exec

Arguments

Response

extendWhere

Arguments

Response

findInSet

Arguments

Response

from

Arguments

Response

getBounded

Arguments

Response

group

Arguments

Response

groupConcat

Arguments

Response

having

Arguments

Response

hour

Arguments

Response

insert

Arguments

Response

isNullDatetime

Arguments

Response

join

Arguments

Response

length

Arguments

Response

minute

Arguments