Database query builder for SELECT statements. See Query Builder for usage and examples.
Class declared in MODPATH/database/classes/Database/Query/Builder/Select.php on line 3.
bool FALSE
bool FALSE
bool FALSE
array(0) array(0) array(0) array(0) NULL
NULL
NULL
array(0) NULL
array(0) array(0) array(0) NULL
NULL
array(0) array(0) Sets the initial columns to select from.
array
$columns
= NULL - Column list voidpublic function __construct(array $columns = null)
{
if (!empty($columns)) {
// Set the initial columns
$this->_select = $columns;
}
// Start the query with no actual SQL statement
parent::__construct(Database::SELECT, '');
}Creates a new "AND HAVING" condition for the query.
mixed
$column
required - Column name or [$column, $alias] or object string
$op
required - Logic operator mixed
$value
= NULL - Column value $thispublic function and_having($column, $op, $value = null)
{
$this->_having[] = ['AND' => [$column, $op, $value]];
return $this;
}Closes an open "AND HAVING (...)" grouping.
$thispublic function and_having_close()
{
$this->_having[] = ['AND' => ')'];
return $this;
}Opens a new "AND HAVING (...)" grouping.
$thispublic function and_having_open()
{
$this->_having[] = ['AND' => '('];
return $this;
}Compile the SQL query and return it.
mixed
$db
= NULL - Database instance or name of instance stringpublic function compile($db = null)
{
if (!is_object($db)) {
// Get the database instance
$db = Database::instance($db);
}
// Callback to quote columns
$quote_column = [$db, 'quote_column'];
// Callback to quote tables
$quote_table = [$db, 'quote_table'];
// Start a selection query
$query = 'SELECT ';
if ($this->_distinct === true) {
// Select only unique results
$query .= 'DISTINCT ';
}
if (empty($this->_select)) {
// Select all columns
$query .= '*';
} else {
// Select all columns
$query .= implode(', ', array_unique(array_map($quote_column, $this->_select)));
}
if (!empty($this->_from)) {
// Set tables to select from
$query .= ' FROM ' . implode(', ', array_unique(array_map($quote_table, $this->_from)));
}
if (!empty($this->_join)) {
// Add tables to join
$query .= ' ' . $this->_compile_join($db, $this->_join);
}
if (!empty($this->_where)) {
// Add selection conditions
$query .= ' WHERE ' . $this->_compile_conditions($db, $this->_where);
}
if (!empty($this->_group_by)) {
// Add grouping
$query .= ' ' . $this->_compile_group_by($db, $this->_group_by);
}
if (!empty($this->_having)) {
// Add filtering conditions
$query .= ' HAVING ' . $this->_compile_conditions($db, $this->_having);
}
if (!empty($this->_order_by)) {
// Add sorting
$query .= ' ' . $this->_compile_order_by($db, $this->_order_by);
}
if ($this->_limit !== null) {
// Add limiting
$query .= ' LIMIT ' . $this->_limit;
}
if ($this->_offset !== null) {
// Add offsets
$query .= ' OFFSET ' . $this->_offset;
}
if (!empty($this->_union)) {
$query = '(' . $query . ')';
foreach ($this->_union as $u) {
$query .= ' UNION ';
if ($u['all'] === true) {
$query .= 'ALL ';
}
$query .= '(' . $u['select']->compile($db) . ')';
}
}
$this->_sql = $query;
return parent::compile($db);
}Enables or disables selecting only unique columns using "SELECT DISTINCT"
boolean
$value
required - Enable or disable distinct columns $thispublic function distinct($value)
{
$this->_distinct = (bool) $value;
return $this;
}Choose the tables to select "FROM ..."
mixed
$tables
required - $table table name or [$table, $alias] or object $thispublic function from($tables)
{
$tables = func_get_args();
$this->_from = array_merge($this->_from, $tables);
return $this;
}Creates a "GROUP BY ..." filter.
mixed
$columns
required - Column name or [$column, $alias] or object $thispublic function group_by($columns)
{
$columns = func_get_args();
$this->_group_by = array_merge($this->_group_by, $columns);
return $this;
}Alias of and_having()
mixed
$column
required - Column name or [$column, $alias] or object string
$op
required - Logic operator mixed
$value
= NULL - Column value $thispublic function having($column, $op, $value = null)
{
return $this->and_having($column, $op, $value);
}Closes an open "AND HAVING (...)" grouping.
$thispublic function having_close()
{
return $this->and_having_close();
}Alias of and_having_open()
$thispublic function having_open()
{
return $this->and_having_open();
}Adds addition tables to "JOIN ...".
mixed
$table
required - Column name or [$column, $alias] or object string
$type
= NULL - Join type (LEFT, RIGHT, INNER, etc) $thispublic function join($table, $type = null)
{
$this->_join[] = $this->_last_join = new Database_Query_Builder_Join($table, $type);
return $this;
}Start returning results after "OFFSET ..."
integer
$number
required - Starting result number or null to reset $thispublic function offset($number)
{
$this->_offset = ($number === null) ? null : (int) $number;
return $this;
}Adds "ON ..." conditions for the last created JOIN statement.
mixed
$c1
required - Column name or [$column, $alias] or object string
$op
required - Logic operator mixed
$c2
required - Column name or [$column, $alias] or object $thispublic function on($c1, $op, $c2)
{
$this->_last_join->on($c1, $op, $c2);
return $this;
}Creates a new "OR HAVING" condition for the query.
mixed
$column
required - Column name or [$column, $alias] or object string
$op
required - Logic operator mixed
$value
= NULL - Column value $thispublic function or_having($column, $op, $value = null)
{
$this->_having[] = ['OR' => [$column, $op, $value]];
return $this;
}Closes an open "OR HAVING (...)" grouping.
$thispublic function or_having_close()
{
$this->_having[] = ['OR' => ')'];
return $this;
}Opens a new "OR HAVING (...)" grouping.
$thispublic function or_having_open()
{
$this->_having[] = ['OR' => '('];
return $this;
}Reset the current builder status.
$thispublic function reset()
{
$this->_select = $this->_from = $this->_join = $this->_where = $this->_group_by = $this->_having = $this->_order_by = $this->_union = [];
$this->_distinct = false;
$this->_limit = $this->_offset = $this->_last_join = null;
$this->_parameters = [];
$this->_sql = null;
return $this;
}Choose the columns to select from.
mixed
$columns
= NULL - Column name or [$column, $alias] or object $thispublic function select($columns = null)
{
$columns = func_get_args();
$this->_select = array_merge($this->_select, $columns);
return $this;
}Choose the columns to select from, using an array.
array
$columns
required - List of column names or aliases $thispublic function select_array(array $columns)
{
$this->_select = array_merge($this->_select, $columns);
return $this;
}Adds an other UNION clause.
mixed
$select
required - If string, it must be the name of a table. Else
must be an instance of Database_Query_Builder_Select boolean
$all
= bool TRUE - Decides if it's an UNION or UNION ALL clause $thispublic function union($select, $all = true)
{
if (is_string($select)) {
$select = DB::select()->from($select);
}
if (!$select instanceof Database_Query_Builder_Select)
throw new Kohana_Exception('first parameter must be a string or an instance of Database_Query_Builder_Select');
$this->_union [] = ['select' => $select, 'all' => $all];
return $this;
}Adds "USING ..." conditions for the last created JOIN statement.
string
$columns
required - Column name $thispublic function using($columns)
{
$columns = func_get_args();
call_user_func_array([$this->_last_join, 'using'], $columns);
return $this;
}Creates a new "AND WHERE" condition for the query.
mixed
$column
required - Column name or [$column, $alias] or object string
$op
required - Logic operator mixed
$value
required - Column value $thispublic function and_where($column, $op, $value)
{
$this->_where[] = ['AND' => [$column, $op, $value]];
return $this;
}Closes an open "WHERE (...)" grouping.
$thispublic function and_where_close()
{
$this->_where[] = ['AND' => ')'];
return $this;
}Opens a new "AND WHERE (...)" grouping.
$thispublic function and_where_open()
{
$this->_where[] = ['AND' => '('];
return $this;
}Return up to "LIMIT ..." results
integer
$number
required - Maximum results to return or null to reset $thispublic function limit($number)
{
$this->_limit = ($number === null) ? null : (int) $number;
return $this;
}Creates a new "OR WHERE" condition for the query.
mixed
$column
required - Column name or [$column, $alias] or object string
$op
required - Logic operator mixed
$value
required - Column value $thispublic function or_where($column, $op, $value)
{
$this->_where[] = ['OR' => [$column, $op, $value]];
return $this;
}Closes an open "WHERE (...)" grouping.
$thispublic function or_where_close()
{
$this->_where[] = ['OR' => ')'];
return $this;
}Opens a new "OR WHERE (...)" grouping.
$thispublic function or_where_open()
{
$this->_where[] = ['OR' => '('];
return $this;
}Applies sorting with "ORDER BY ..."
mixed
$column
required - Column name or [$column, $alias] or object string
$direction
= NULL - Direction of sorting $thispublic function order_by($column, $direction = null)
{
$this->_order_by[] = [$column, $direction];
return $this;
}Alias of and_where()
mixed
$column
required - Column name or [$column, $alias] or object string
$op
required - Logic operator mixed
$value
required - Column value $thispublic function where($column, $op, $value)
{
return $this->and_where($column, $op, $value);
}Closes an open "WHERE (...)" grouping.
$thispublic function where_close()
{
return $this->and_where_close();
}Closes an open "WHERE (...)" grouping or removes the grouping when it is empty.
$thispublic function where_close_empty()
{
$group = end($this->_where);
if ($group AND reset($group) === '(') {
array_pop($this->_where);
return $this;
}
return $this->where_close();
}Alias of and_where_open()
$thispublic function where_open()
{
return $this->and_where_open();
}Return the SQL query string.
stringpublic function __toString()
{
try {
// Return the SQL string
return $this->compile(Database::instance());
} catch (Exception $e) {
return Kohana_Exception::text($e);
}
}Returns results as associative arrays
$thispublic function as_assoc()
{
$this->_as_object = false;
$this->_object_params = [];
return $this;
}Returns results as objects
string
$class
= bool TRUE - Classname or true for stdClass array
$params
= NULL - $params $thispublic function as_object($class = true, array $params = null)
{
$this->_as_object = $class;
if ($params) {
// Add object parameters
$this->_object_params = $params;
}
return $this;
}Bind a variable to a parameter in the query.
string
$param
required - Parameter key to replace byref mixed
$var
required - Variable to use $thispublic function bind($param, & $var)
{
// Bind a value to a variable
$this->_parameters[$param] = & $var;
return $this;
}Enables the query to be cached for a specified amount of time.
integer
$lifetime
= NULL - Number of seconds to cache, 0 deletes it from the cache boolean
$force
= bool FALSE - Whether or not to execute the query during a cache hit $thispublic function cached($lifetime = null, $force = false)
{
if ($lifetime === null) {
// Use the global setting
$lifetime = Kohana::$cache_life;
}
$this->_force_execute = $force;
$this->_lifetime = $lifetime;
return $this;
}Execute the current query on the given database.
mixed
$db
= NULL - Database instance or name of instance string
$as_object
= NULL - Result object classname, true for stdClass or false for array array
$object_params
= NULL - Result object constructor arguments object - Database_Result for SELECT queriesmixed - The insert id for INSERT queriesinteger - Number of affected rows for all other queriespublic function execute($db = null, $as_object = null, $object_params = null)
{
if (!is_object($db)) {
// Get the database instance
$db = Database::instance($db);
}
if ($as_object === null) {
$as_object = $this->_as_object;
}
if ($object_params === null) {
$object_params = $this->_object_params;
}
// Compile the SQL query
$sql = $this->compile($db);
if ($this->_lifetime !== null AND $this->_type === Database::SELECT) {
// Set the cache key based on the database instance name and SQL
$cache_key = 'Database::query("' . $db . '", "' . $sql . '")';
// Read the cache first to delete a possible hit with lifetime <= 0
if (($result = Kohana::cache($cache_key, null, $this->_lifetime)) !== null
AND ! $this->_force_execute) {
// Return a cached result
return new Database_Result_Cached($result, $sql, $as_object, $object_params);
}
}
// Execute the query
$result = $db->query($this->_type, $sql, $as_object, $object_params);
if (isset($cache_key) AND $this->_lifetime > 0) {
// Cache the result array
Kohana::cache($cache_key, $result->as_array(), $this->_lifetime);
}
return $result;
}Set the value of a parameter in the query.
string
$param
required - Parameter key to replace mixed
$value
required - Value to use $thispublic function param($param, $value)
{
// Add or overload a new parameter
$this->_parameters[$param] = $value;
return $this;
}Add multiple parameters to the query.
array
$params
required - List of parameters $thispublic function parameters(array $params)
{
// Merge the new parameters in
$this->_parameters = $params + $this->_parameters;
return $this;
}Get the type of the query.
integerpublic function type()
{
return $this->_type;
}Compiles an array of conditions into an SQL partial. Used for WHERE and HAVING.
object
$db
required - Database instance array
$conditions
required - Condition statements stringprotected function _compile_conditions(Database $db, array $conditions)
{
$last_condition = null;
$sql = '';
foreach ($conditions as $group) {
// Process groups of conditions
foreach ($group as $logic => $condition) {
if ($condition === '(') {
if (!empty($sql) AND $last_condition !== '(') {
// Include logic operator
$sql .= ' ' . $logic . ' ';
}
$sql .= '(';
} elseif ($condition === ')') {
$sql .= ')';
} else {
if (!empty($sql) AND $last_condition !== '(') {
// Add the logic operator
$sql .= ' ' . $logic . ' ';
}
// Split the condition
list($column, $op, $value) = $condition;
if ($value === null) {
if ($op === '=') {
// Convert "val = NULL" to "val IS NULL"
$op = 'IS';
} elseif ($op === '!=' OR $op === '<>') {
// Convert "val != NULL" to "valu IS NOT NULL"
$op = 'IS NOT';
}
}
// Database operators are always uppercase
$op = strtoupper($op);
if ($op === 'BETWEEN' AND is_array($value)) {
// BETWEEN always has exactly two arguments
list($min, $max) = $value;
if ((is_string($min) AND array_key_exists($min, $this->_parameters)) === false) {
// Quote the value, it is not a parameter
$min = $db->quote($min);
}
if ((is_string($max) AND array_key_exists($max, $this->_parameters)) === false) {
// Quote the value, it is not a parameter
$max = $db->quote($max);
}
// Quote the min and max value
$value = $min . ' AND ' . $max;
} elseif ((is_string($value) AND array_key_exists($value, $this->_parameters)) === false) {
// Quote the value, it is not a parameter
$value = $db->quote($value);
}
if ($column) {
if (is_array($column)) {
// Use the column name
$column = $db->quote_identifier(reset($column));
} else {
// Apply proper quoting to the column
$column = $db->quote_column($column);
}
}
// Append the statement to the query
$sql .= trim($column . ' ' . $op . ' ' . $value);
}
$last_condition = $condition;
}
}
return $sql;
}Compiles an array of GROUP BY columns into an SQL partial.
object
$db
required - Database instance array
$columns
required - $columns stringprotected function _compile_group_by(Database $db, array $columns)
{
$group = [];
foreach ($columns as $column) {
if (is_array($column)) {
// Use the column alias
$column = $db->quote_identifier(end($column));
} else {
// Apply proper quoting to the column
$column = $db->quote_column($column);
}
$group[] = $column;
}
return 'GROUP BY ' . implode(', ', $group);
}Compiles an array of JOIN statements into an SQL partial.
object
$db
required - Database instance array
$joins
required - Join statements stringprotected function _compile_join(Database $db, array $joins)
{
$statements = [];
foreach ($joins as $join) {
// Compile each of the join statements
$statements[] = $join->compile($db);
}
return implode(' ', $statements);
}Compiles an array of ORDER BY statements into an SQL partial.
object
$db
required - Database instance array
$columns
required - Sorting columns stringprotected function _compile_order_by(Database $db, array $columns)
{
$sort = [];
foreach ($columns as $group) {
list ($column, $direction) = $group;
if (is_array($column)) {
// Use the column alias
$column = $db->quote_identifier(end($column));
} else {
// Apply proper quoting to the column
$column = $db->quote_column($column);
}
if ($direction) {
// Make the direction uppercase
$direction = strtoupper($direction);
if (!in_array($direction, ['ASC', 'DESC'])) {
throw new Database_Exception('Order direction must be "ASC" or "DESC".');
}
$direction = ' ' . $direction;
}
$sort[] = $column . $direction;
}
return 'ORDER BY ' . implode(', ', $sort);
}Compiles an array of set values into an SQL partial. Used for UPDATE.
object
$db
required - Database instance array
$values
required - Updated values stringprotected function _compile_set(Database $db, array $values)
{
$set = [];
foreach ($values as $group) {
// Split the set
list ($column, $value) = $group;
// Quote the column name
$column = $db->quote_column($column);
if ((is_string($value) AND array_key_exists($value, $this->_parameters)) === false) {
// Quote the value, it is not a parameter
$value = $db->quote($value);
}
$set[$column] = $column . ' = ' . $value;
}
return implode(', ', $set);
}