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.
Sets the initial columns to select from.
array
$columns
= NULL - Column listvoidpublic 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 array($column, $alias) or objectstring
$op
required - Logic operatormixed
$value
= NULL - Column value$thispublic function and_having($column, $op, $value = NULL)
{
$this->_having[] = array('AND' => array($column, $op, $value));
return $this;
}Closes an open "AND HAVING (...)" grouping.
$thispublic function and_having_close()
{
$this->_having[] = array('AND' => ')');
return $this;
}Opens a new "AND HAVING (...)" grouping.
$thispublic function and_having_open()
{
$this->_having[] = array('AND' => '(');
return $this;
}Compile the SQL query and return it.
object
$db
required - Database instancestringpublic function compile(Database $db)
{
// Callback to quote columns
$quote_column = array($db, 'quote_column');
// Callback to quote tables
$quote_table = array($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))
{
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 array($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 array($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 array($column, $alias) or objectstring
$op
required - Logic operatormixed
$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 array($column, $alias) or objectstring
$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;
return $this;
}Adds "ON ..." conditions for the last created JOIN statement.
mixed
$c1
required - Column name or array($column, $alias) or objectstring
$op
required - Logic operatormixed
$c2
required - Column name or array($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 array($column, $alias) or objectstring
$op
required - Logic operatormixed
$value
= NULL - Column value$thispublic function or_having($column, $op, $value = NULL)
{
$this->_having[] = array('OR' => array($column, $op, $value));
return $this;
}Closes an open "OR HAVING (...)" grouping.
$thispublic function or_having_close()
{
$this->_having[] = array('OR' => ')');
return $this;
}Opens a new "OR HAVING (...)" grouping.
$thispublic function or_having_open()
{
$this->_having[] = array('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 = array();
$this->_distinct = FALSE;
$this->_limit =
$this->_offset =
$this->_last_join = NULL;
$this->_parameters = array();
$this->_sql = NULL;
return $this;
}Choose the columns to select from.
mixed
$columns
= NULL - Column name or array($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.
must be an instance of Database_Query_Builder_Select
mixed
$select
required - If string, it must be the name of a table. Elseboolean
$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 []= array('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(array($this->_last_join, 'using'), $columns);
return $this;
}Creates a new "AND WHERE" condition for the query.
mixed
$column
required - Column name or array($column, $alias) or objectstring
$op
required - Logic operatormixed
$value
required - Column value$thispublic function and_where($column, $op, $value)
{
$this->_where[] = array('AND' => array($column, $op, $value));
return $this;
}Closes an open "AND WHERE (...)" grouping.
$thispublic function and_where_close()
{
$this->_where[] = array('AND' => ')');
return $this;
}Opens a new "AND WHERE (...)" grouping.
$thispublic function and_where_open()
{
$this->_where[] = array('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;
return $this;
}Creates a new "OR WHERE" condition for the query.
mixed
$column
required - Column name or array($column, $alias) or objectstring
$op
required - Logic operatormixed
$value
required - Column value$thispublic function or_where($column, $op, $value)
{
$this->_where[] = array('OR' => array($column, $op, $value));
return $this;
}Closes an open "OR WHERE (...)" grouping.
$thispublic function or_where_close()
{
$this->_where[] = array('OR' => ')');
return $this;
}Opens a new "OR WHERE (...)" grouping.
$thispublic function or_where_open()
{
$this->_where[] = array('OR' => '(');
return $this;
}Applies sorting with "ORDER BY ..."
mixed
$column
required - Column name or array($column, $alias) or objectstring
$direction
= NULL - Direction of sorting$thispublic function order_by($column, $direction = NULL)
{
$this->_order_by[] = array($column, $direction);
return $this;
}Alias of and_where()
mixed
$column
required - Column name or array($column, $alias) or objectstring
$op
required - Logic operatormixed
$value
required - Column value$thispublic function where($column, $op, $value)
{
return $this->and_where($column, $op, $value);
}Closes an open "AND WHERE (...)" grouping.
$thispublic function where_close()
{
return $this->and_where_close();
}Alias of and_where_open()
$thispublic function where_open()
{
return $this->and_where_open();
}Return the SQL query string.
stringfinal public 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 = array();
return $this;
}Returns results as objects
string
$class
= bool TRUE - Classname or TRUE for stdClassarray
$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 replacebyref 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 cacheboolean
$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 instancestring
$as_object
= NULL - Result object classname, TRUE for stdClass or FALSE for arrayarray
$object_params
= NULL - Result object constructor argumentsobject - 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 replacemixed
$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 instancearray
$conditions
required - Condition statementsstringprotected 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 === '!=')
{
// 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 instancearray
$columns
required - $columnsstringprotected function _compile_group_by(Database $db, array $columns)
{
$group = array();
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 instancearray
$joins
required - Join statementsstringprotected function _compile_join(Database $db, array $joins)
{
$statements = array();
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 instancearray
$columns
required - Sorting columnsstringprotected function _compile_order_by(Database $db, array $columns)
{
$sort = array();
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);
}
$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 instancearray
$values
required - Updated valuesstringprotected function _compile_set(Database $db, array $values)
{
$set = array();
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);
}