SqlsrvQuery

Extends DatabaseQuery

SQL Server Query Building Class.

since

1.0

package

Joomla Framework

Methods

__clone

Method to provide basic copy support.

__clone() : void
inherited

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.

since

1.0

__construct

Class constructor.

__construct(\Joomla\Database\DatabaseInterface db = null) : mixed
inherited
since

1.0

Arguments

db

\Joomla\Database\DatabaseInterfaceThe database driver.

Response

mixed

__get

Magic function to get protected variable value

__get(string name) : mixed
inherited
since

1.0

Arguments

name

stringThe name of the variable.

Response

mixed

__toString

Magic function to convert the query to a string.

__toString() : string
inherited
since

1.0

Response

stringThe completed query.

alias

Add alias for current query.

alias(string alias) : $this
inherited

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

since

2.0.0

Arguments

alias

stringAlias used for a JDatabaseQuery.

Response

$this

andWhere

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

andWhere(mixed conditions, string glue = 'OR') : $this
inherited

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)

since

1.3.0

Arguments

conditions

mixedA string or array of WHERE conditions.

glue

stringThe glue by which to join the conditions. Defaults to OR.

Response

$this

bind

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, mixed &value, array|string dataType = ParameterType::STRING, int length, array driverOptions = []) : $this
inherited
since

1.5.0

throws

\InvalidArgumentException

Arguments

key

array|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|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

arrayOptional driver options to be used.

Response

$this

bindArray

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

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

Note that all values must be the same data type.

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

since

2.0.0

Arguments

values

arrayValues to bind

dataType

array|stringConstant corresponding to a SQL datatype. It can be an array, in this case it has to be same length of $key

Response

arrayAn array with parameter names

call

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

call(mixed columns) : $this
inherited

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

since

1.0

throws

\Joomla\Database\Exception\QueryTypeAlreadyDefinedExceptionif the query type has already been defined

Arguments

columns

mixedA string or an array of field names.

Response

$this

castAs

Casts a value to a char.

castAs(string type, string value, string length = null) : string
inherited

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

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

since

1.0

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.

castAsChar

Casts a value to a char.

castAsChar(string value) : string
inherited deprecated

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

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

since

1.0

deprecated

3.0 Use $query->castAs('CHAR', $value)

Arguments

value

stringThe value to cast as a char.

Response

stringSQL statement to cast the value as a char type.

charLength

Gets the number of characters in a string.

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

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

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

since

1.0

Arguments

field

stringA value.

operator

string|nullComparison operator between charLength integer value and $condition

condition

string|nullInteger value to compare charLength with.

Response

stringThe required char length call.

clear

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

clear(string clause = null) : $this
inherited
since

1.0

Arguments

clause

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

Response

$this

columns

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

columns(array|string columns) : $this
inherited
since

1.0

Arguments

columns

array|stringA column name, or array of column names.

Response

$this

concatenate

Concatenates an array of column names or values.

concatenate(string[] values, string|null separator = null) : string
inherited

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

since

1.0

Arguments

values

string[]An array of values to concatenate.

separator

string|nullAs separator to place between each value.

Response

stringThe concatenated values.

currentTimestamp

Gets the current date and time.

currentTimestamp() : string
inherited

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

since

1.0

Response

string

dateAdd

Add to the current date and time.

dateAdd(string date, string interval, string datePart) : string
inherited

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.

link
since

1.5.0

Arguments

date

stringThe db quoted string representation of the date to add to. May be date or datetime

interval

stringThe string representation of the appropriate number of units

datePart

stringThe part of the date to perform the addition on

Response

stringThe string with the appropriate sql for addition of dates

dateFormat

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

dateFormat() : string
inherited

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.

since

1.0

throws

\RuntimeException

Response

stringThe format string.

day

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

day(string date) : string
inherited

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

since

1.0

Arguments

date

stringDate column containing day to be extracted.

Response

stringReturns string to extract day from a date.

delete

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

delete(string table = null) : $this
inherited

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

since

1.0

throws

\Joomla\Database\Exception\QueryTypeAlreadyDefinedExceptionif the query type has already been defined

Arguments

table

stringThe name of the table to delete from.

Response

$this

dump

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

dump() : string
inherited deprecated

Usage: echo $query->dump();

since

1.0

deprecated

3.0 Deprecated without replacement

Response

string

e

Alias for escape method

e(string text, bool extra = false) : string
inherited
since

1.0

throws

\RuntimeExceptionif the internal db property is not a valid object.

Arguments

text

stringThe string to be escaped.

extra

boolOptional parameter to provide extra escaping.

Response

stringThe escaped string.

escape

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

escape(string text, bool extra = false) : string
inherited

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.

since

1.0

throws

\RuntimeExceptionif the internal db property is not a valid object.

Arguments

text

stringThe string to be escaped.

extra

boolOptional parameter to provide extra escaping.

Response

stringThe escaped string.

exec

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

exec(array|string columns) : $this
inherited

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

since

1.0

throws

\Joomla\Database\Exception\QueryTypeAlreadyDefinedExceptionif the query type has already been defined

Arguments

columns

array|stringA string or an array of field names.

Response

$this

extendWhere

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(string outerGlue, mixed conditions, string innerGlue = 'AND') : $this
inherited

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

1.3.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

findInSet

Find a value in a varchar used like a set.

findInSet(string value, string set) : string
inherited

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

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

since

1.5.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.

fixGroupColumns

Add missing columns names to GROUP BY clause.

fixGroupColumns(array[] selectColumns) : $this
since

2.0.0

Arguments

selectColumns

array[]Array of columns from splitSqlExpression method.

Response

$this

fixSelectAliases

Add required aliases to columns for select statement in subquery.

fixSelectAliases() : array[]
since

2.0.0

Response

array[]Array of columns with added missing aliases.

format

Find and replace sprintf-like tokens in a format string.

format(string format) : string
inherited

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.

since

1.0

Arguments

format

stringThe formatting string.

Response

stringReturns a string produced according to the formatting string.

from

Add a table to the FROM clause of the query.

from(string|\Joomla\Database\DatabaseQuery table) : $this
inherited

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

since

1.0

throws

\RuntimeException

Arguments

table

string|\Joomla\Database\DatabaseQueryThe name of the table or a DatabaseQuery object (or a child of it) with alias set.

Response

$this

getBounded

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(mixed key = null) : mixed
inherited
since

1.5.0

Arguments

key

mixedThe bounded variable key to retrieve.

Response

mixed

group

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

group(array|string columns) : $this
inherited

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

since

1.0

Arguments

columns

array|stringA string or array of ordering columns.

Response

$this

groupConcat

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

groupConcat(string expression, string separator = ',') : string

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

having

A conditions to the HAVING clause of the query.

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

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

since

1.0

Arguments

conditions

array|stringA string or array of columns.

glue

stringThe glue by which to join the conditions. Defaults to AND.

Response

$this

hour

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

hour(string date) : string
inherited

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

since

1.0

Arguments

date

stringDate column containing hour to be extracted.

Response

stringReturns string to extract hour from a date.

innerJoin

Add an INNER JOIN clause to the query.

innerJoin(string table, string condition = null) : $this
inherited

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

since

1.0

Arguments

table

stringThe name of table.

condition

stringThe join condition.

Response

$this

insert

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

insert(string table, bool incrementField = false) : $this
inherited

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

1.0

throws

\Joomla\Database\Exception\QueryTypeAlreadyDefinedExceptionif 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

isNullDatetime

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

isNullDatetime(string column) : string
inherited

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

since

2.0.0

Arguments

column

stringA column name.

Response

string

join

Add a JOIN clause to the query.

join(string type, string table, string condition = null) : $this
inherited

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

since

1.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

leftJoin

Add a LEFT JOIN clause to the query.

leftJoin(string table, string condition = null) : $this
inherited

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

since

1.0

Arguments

table

stringThe name of table.

condition

stringThe join condition.

Response

$this

length

Get the length of a string in bytes.

length(string value) : int
inherited

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

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

since

1.0

Arguments

value

stringThe string to measure.

Response

int

merge

Combine a select statement to the current query by one of the set operators.

merge(string name, \Joomla\Database\DatabaseQuery|string query) : $this
inherited

Operators: UNION, UNION ALL, EXCEPT or INTERSECT.

since

2.0.0

Arguments

name

stringThe name of the set operator with parentheses.

query

\Joomla\Database\DatabaseQuery|stringThe DatabaseQuery object or string.

Response

$this

minute

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

minute(string date) : string
inherited

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

since

1.0

Arguments

date

stringDate column containing minute to be extracted.

Response

stringReturns string to extract minute from a date.

month

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

month(string date) : string
inherited

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

since

1.0

Arguments

date

stringDate column containing month to be extracted.

Response

stringReturns string to extract month from a date.

nullDate

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

nullDate(bool quoted = true) : string
inherited

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

1.0

throws

\RuntimeException

Arguments

quoted

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

Response

stringNull or zero representation of a timestamp.

order

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

order(array|string columns) : $this
inherited

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

since

1.0

Arguments

columns

array|stringA string or array of ordering columns.

Response

$this

orWhere

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

orWhere(mixed conditions, string glue = 'AND') : $this
inherited

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)

since

1.3.0

Arguments

conditions

mixedA string or array of WHERE conditions.

glue

stringThe glue by which to join the conditions. Defaults to AND.

Response

$this

outerJoin

Add an OUTER JOIN clause to the query.

outerJoin(string table, string condition = null) : $this
inherited

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

since

1.0

Arguments

table

stringThe name of table.

condition

stringThe join condition.

Response

$this

processLimit

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(string query, int limit, int offset) : string
since

2.0.0

Arguments

query

stringThe query in string format

limit

intThe limit for the result set

offset

intThe offset for the result set

Response

string

q

Alias for quote method

q(array|string text, bool escape = true) : string
inherited
since

1.0

throws

\RuntimeExceptionif the internal db property is not a valid object.

Arguments

text

array|stringA string or an array of strings to quote.

escape

boolTrue (default) to escape the string, false to leave it unchanged.

Response

stringThe quoted input string.

qn

Alias for quoteName method

qn(array|string name, array|string as = null) : array|string
inherited
since

1.0

throws

\RuntimeExceptionif the internal db property is not a valid object.

Arguments

name

array|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|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|stringThe quote wrapped name, same type of $name.

querySet

Set a single query to the query set.

querySet(\Joomla\Database\DatabaseQuery query) : $this
inherited

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

\Joomla\Database\DatabaseQueryThe DatabaseQuery object or string.

Response

$this

quote

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

quote(array|string text, bool escape = true) : string
inherited

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

since

1.0

throws

\RuntimeExceptionif the internal db property is not a valid object.

Arguments

text

array|stringA string or an array of strings to quote.

escape

boolTrue (default) to escape the string, false to leave it unchanged.

Response

stringThe quoted input string.

quoteName

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
inherited

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

\RuntimeExceptionif the internal db property is not a valid object.

Arguments

name

array|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|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|stringThe quote wrapped name, same type of $name.

rand

Get the function to return a random floating-point value

rand() : string
inherited

Usage: $query->rand();

since

1.5.0

Response

string

regexp

Get the regular expression operator

regexp(string value) : string
inherited

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

since

1.5.0

Arguments

value

stringThe regex pattern.

Response

string

rightJoin

Add a RIGHT JOIN clause to the query.

rightJoin(string table, string condition = null) : $this
inherited

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

since

1.0

Arguments

table

stringThe name of table.

condition

stringThe join condition.

Response

$this

second

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

second(string date) : string
inherited

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

since

1.0

Arguments

date

stringDate column containing second to be extracted.

Response

stringReturns string to extract second from a date.

select

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

select(array|string columns) : $this
inherited

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

since

1.0

throws

\Joomla\Database\Exception\QueryTypeAlreadyDefinedExceptionif the query type has already been defined

Arguments

columns

array|stringA string or an array of field names.

Response

$this

selectRowNumber

Return the number of the current row.

selectRowNumber(string orderBy, string orderColumnAlias) : $this
inherited

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

set

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

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

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

since

1.0

Arguments

conditions

array|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

setLimit

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

setLimit(int limit, int offset) : $this
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

2.0.0

Arguments

limit

intThe limit for the result set

offset

intThe offset for the result set

Response

$this

setQuery

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

setQuery(\Joomla\Database\DatabaseQuery|string sql) : $this
inherited

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

since

1.0

Arguments

sql

\Joomla\Database\DatabaseQuery|stringA SQL query string or DatabaseQuery object

Response

$this

splitSqlExpression

Split a string of sql expression into an array of individual columns.

splitSqlExpression(string string) : array[]

Single line or line end comments and multi line comments are stripped off. Always return at least one column.

since

2.0.0

Arguments

string

stringInput string of sql expression like select expression.

Response

array[]The columns from the input string separated into an array.

toQuerySet

Create a DatabaseQuery object of type querySet from current query.

toQuerySet() : \Joomla\Database\DatabaseQuery
inherited

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

\Joomla\Database\DatabaseQueryA new object of the DatabaseQuery.

unbind

Method to unbind a bound variable.

unbind(array|string|int key) : $this
inherited
since

2.0.0

Arguments

key

array|string|intThe key or array of keys to unbind.

Response

$this

union

Add a query to UNION with the current query.

union(\Joomla\Database\DatabaseQuery|string query, bool distinct = true) : $this
inherited

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

since

1.0

Arguments

query

\Joomla\Database\DatabaseQuery|stringThe DatabaseQuery object or string to union.

distinct

boolTrue to only return distinct rows from the union.

Response

$this

unionAll

Add a query to UNION ALL with the current query.

unionAll(\Joomla\Database\DatabaseQuery|string query) : $this
inherited

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

see union
since

1.5.0

Arguments

query

\Joomla\Database\DatabaseQuery|stringThe DatabaseQuery object or string to union.

Response

$this

update

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

update(string table) : $this
inherited

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

since

1.0

throws

\Joomla\Database\Exception\QueryTypeAlreadyDefinedExceptionif the query type has already been defined

Arguments

table

stringA table to update.

Response

$this

validateRowNumber

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

validateRowNumber(string orderBy, string orderColumnAlias) : void
inherited
since

2.0.0

throws

\RuntimeException

Arguments

orderBy

stringAn expression of ordering for window function.

orderColumnAlias

stringAn alias for new ordering column.

values

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

values(array|string values) : $this
inherited

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

since

1.0

Arguments

values

array|stringA single tuple, or array of tuples.

Response

$this

where

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

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

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

since

1.0

Arguments

conditions

array|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

whereIn

Add a WHERE IN statement to the query.

whereIn(string keyName, array keyValues, array|string dataType = ParameterType::INTEGER) : $this
inherited

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

arrayArray of values to be matched

dataType

array|stringConstant corresponding to a SQL datatype. It can be an array, in this case it has to be same length of $keyValues

Response

$this

whereNotIn

Add a WHERE NOT IN statement to the query.

whereNotIn(string keyName, array keyValues, array|string dataType = ParameterType::INTEGER) : $this
inherited

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

arrayArray of values to be matched

dataType

array|stringConstant corresponding to a SQL datatype. It can be an array, in this case it has to be same length of $keyValues

Response

$this

year

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

year(string date) : string
inherited

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

since

1.0

Arguments

date

stringDate column containing year to be extracted.

Response

stringReturns string to extract year from a date.

Properties

nullDatetimeList

The list of zero or null representation of a datetime.

inherited
since

2.0.0

Type(s)

string[]

bounded

Holds key / value pair of bound objects.

inherited
since

2.0.0

Type(s)

array

parameterMapping

Mapping array for parameter types.

inherited
since

2.0.0

Type(s)

array

db

The database driver.

inherited
since

1.0

Type(s)

\Joomla\Database\DatabaseInterface

sql

The SQL query (if a direct query string was provided).

inherited
since

1.0

Type(s)

string

type

The query type.

inherited
since

1.0

Type(s)

string|null

alias

The query alias.

inherited
since

2.0.0

Type(s)

string|null

element

The query element for a generic query (type = null).

inherited
since

1.0

Type(s)

\Joomla\Database\Query\QueryElement

select

The select element.

inherited
since

1.0

Type(s)

\Joomla\Database\Query\QueryElement

delete

The delete element.

inherited
since

1.0

Type(s)

\Joomla\Database\Query\QueryElement

update

The update element.

inherited
since

1.0

Type(s)

\Joomla\Database\Query\QueryElement

insert

The insert element.

inherited
since

1.0

Type(s)

\Joomla\Database\Query\QueryElement

from

The from element.

inherited
since

1.0

Type(s)

\Joomla\Database\Query\QueryElement

join

The join elements.

inherited
since

1.0

Type(s)

\Joomla\Database\Query\QueryElement[]

set

The set element.

inherited
since

1.0

Type(s)

\Joomla\Database\Query\QueryElement

where

The where element.

inherited
since

1.0

Type(s)

\Joomla\Database\Query\QueryElement

group

The group by element.

inherited
since

1.0

Type(s)

\Joomla\Database\Query\QueryElement

having

The having element.

inherited
since

1.0

Type(s)

\Joomla\Database\Query\QueryElement

columns

The column list for an INSERT statement.

inherited
since

1.0

Type(s)

\Joomla\Database\Query\QueryElement

values

The values list for an INSERT statement.

inherited
since

1.0

Type(s)

\Joomla\Database\Query\QueryElement

order

The order element.

inherited
since

1.0

Type(s)

\Joomla\Database\Query\QueryElement

autoIncrementField

The auto increment insert field element.

inherited
since

1.0

Type(s)

bool

call

The call element.

inherited
since

1.0

Type(s)

\Joomla\Database\Query\QueryElement

exec

The exec element.

inherited
since

1.0

Type(s)

\Joomla\Database\Query\QueryElement

merge

The list of query elements, which may include UNION, UNION ALL, EXCEPT and INTERSECT.

inherited
since

2.0.0

Type(s)

\Joomla\Database\Query\QueryElement[]

querySet

The query object.

inherited
since

2.0.0

Type(s)

\Joomla\Database\Query\DatabaseQuery

selectRowNumber

Details of window function.

inherited
since

2.0.0

Type(s)

array|null

offset

The offset for the result set.

inherited
since

2.0.0

Type(s)

int|null

limit

The limit for the result set.

inherited
since

2.0.0

Type(s)

int|null

preparedIndex

An internal index for the bindArray function for unique prepared parameters.

inherited
since

2.0.0

Type(s)

int

bounded

inherited

Holds key / value pair of bound objects.

Type(s)

array

parameterMapping

inherited

Mapping array for parameter types.

Type(s)

array

db

inherited

The database driver.

Type(s)

\Joomla\Database\DatabaseInterface

sql

inherited

The SQL query (if a direct query string was provided).

Type(s)

string

type

inherited

The query type.

Type(s)

string

alias

inherited

The query alias.

Type(s)

string|null

element

inherited

The query element for a generic query (type = null).

Type(s)

\Joomla\Database\Query\QueryElement

select

inherited

The select element.

Type(s)

\Joomla\Database\Query\QueryElement

delete

inherited

The delete element.

Type(s)

\Joomla\Database\Query\QueryElement

update

inherited

The update element.

Type(s)

\Joomla\Database\Query\QueryElement

insert

inherited

The insert element.

Type(s)

\Joomla\Database\Query\QueryElement

from

inherited

The from element.

Type(s)

\Joomla\Database\Query\QueryElement

join

inherited

The join elements.

Type(s)

\Joomla\Database\Query\QueryElement[]|null

set

inherited

The set element.

Type(s)

\Joomla\Database\Query\QueryElement

where

inherited

The where element.

Type(s)

\Joomla\Database\Query\QueryElement

group

inherited

The group element.

Type(s)

\Joomla\Database\Query\QueryElement

having

inherited

The having element.

Type(s)

\Joomla\Database\Query\QueryElement

columns

inherited

The column list for an INSERT statement.

Type(s)

\Joomla\Database\Query\QueryElement

values

inherited

The values list for an INSERT statement.

Type(s)

\Joomla\Database\Query\QueryElement

order

inherited

The order element.

Type(s)

\Joomla\Database\Query\QueryElement

autoIncrementField

inherited

The auto increment insert field element.

Type(s)

bool

call

inherited

The call element.

Type(s)

\Joomla\Database\Query\QueryElement

exec

inherited

The exec element.

Type(s)

\Joomla\Database\Query\QueryElement

merge

inherited

The list of query elements.

Type(s)

\Joomla\Database\Query\QueryElement[]|null

querySet

inherited

The query object.

Type(s)

\Joomla\Database\DatabaseQuery|null

selectRowNumber

inherited

Details of window function.

Type(s)

array|null

nullDatetimeList

inherited

The list of zero or null representation of a datetime.

Type(s)

string[]

offset

inherited

The offset for the result set.

Type(s)

int|null

limit

inherited

The limit for the result set.

Type(s)

int|null

preparedIndex

inherited

An internal index for the bindArray function for unique prepared parameters.

Type(s)

int