Modules

Database_Query_Builder_Select
extends Kohana_Database_Query_Builder_Select
extends Database_Query_Builder_Where
extends Kohana_Database_Query_Builder_Where
extends Database_Query_Builder
extends Kohana_Database_Query_Builder
extends Database_Query
extends Kohana_Database_Query

Database query builder for SELECT statements. See Query Builder for usage and examples.

package
Kohana/Database
category
Query
author
Kohana Team
copyright
© 2008-2009 Kohana Team
license
http://kohanaphp.com/license

Class declared in MODPATH/database/classes/database/query/builder/select.php on line 3.

Properties

protected $_as_object

protected $_distinct

protected $_from

protected $_group_by

protected $_having

protected $_join

protected $_last_join

protected $_lifetime

protected $_limit

protected $_object_params

protected $_offset

protected $_order_by

protected $_parameters

protected $_select

protected $_sql

protected $_type

protected $_union

protected $_where

Methods

public __construct( [ array $columns = NULL ] ) (defined in Kohana_Database_Query_Builder_Select)

Sets the initial columns to select from.

Parameters

  • array $columns = NULL - Column list

Return Values

  • void

Source Code

public 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, '');
}

public and_having( mixed $column , string $op [, mixed $value = NULL ] ) (defined in Kohana_Database_Query_Builder_Select)

Creates a new "AND HAVING" condition for the query.

Parameters

  • mixed $column required - Column name or array($column, $alias) or object
  • string $op required - Logic operator
  • mixed $value = NULL - Column value

Return Values

  • $this

Source Code

public function and_having($column, $op, $value = NULL)
{
	$this->_having[] = array('AND' => array($column, $op, $value));

	return $this;
}

public and_having_close( ) (defined in Kohana_Database_Query_Builder_Select)

Closes an open "AND HAVING (...)" grouping.

Return Values

  • $this

Source Code

public function and_having_close()
{
	$this->_having[] = array('AND' => ')');

	return $this;
}

public and_having_open( ) (defined in Kohana_Database_Query_Builder_Select)

Opens a new "AND HAVING (...)" grouping.

Return Values

  • $this

Source Code

public function and_having_open()
{
	$this->_having[] = array('AND' => '(');

	return $this;
}

public compile( object $db ) (defined in Kohana_Database_Query_Builder_Select)

Compile the SQL query and return it.

Parameters

  • object $db required - Database instance

Return Values

  • string

Source Code

public 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);
}

public distinct( boolean $value ) (defined in Kohana_Database_Query_Builder_Select)

Enables or disables selecting only unique columns using "SELECT DISTINCT"

Parameters

  • boolean $value required - Enable or disable distinct columns

Return Values

  • $this

Source Code

public function distinct($value)
{
	$this->_distinct = (bool) $value;

	return $this;
}

public from( mixed $tables ) (defined in Kohana_Database_Query_Builder_Select)

Choose the tables to select "FROM ..."

Parameters

  • mixed $tables required - $table table name or array($table, $alias) or object

Return Values

  • $this

Source Code

public function from($tables)
{
	$tables = func_get_args();

	$this->_from = array_merge($this->_from, $tables);

	return $this;
}

public group_by( mixed $columns ) (defined in Kohana_Database_Query_Builder_Select)

Creates a "GROUP BY ..." filter.

Parameters

  • mixed $columns required - Column name or array($column, $alias) or object

Return Values

  • $this

Source Code

public function group_by($columns)
{
	$columns = func_get_args();

	$this->_group_by = array_merge($this->_group_by, $columns);

	return $this;
}

public having( mixed $column , string $op [, mixed $value = NULL ] ) (defined in Kohana_Database_Query_Builder_Select)

Alias of and_having()

Parameters

  • mixed $column required - Column name or array($column, $alias) or object
  • string $op required - Logic operator
  • mixed $value = NULL - Column value

Return Values

  • $this

Source Code

public function having($column, $op, $value = NULL)
{
	return $this->and_having($column, $op, $value);
}

public having_close( ) (defined in Kohana_Database_Query_Builder_Select)

Closes an open "AND HAVING (...)" grouping.

Return Values

  • $this

Source Code

public function having_close()
{
	return $this->and_having_close();
}

public having_open( ) (defined in Kohana_Database_Query_Builder_Select)

Alias of and_having_open()

Return Values

  • $this

Source Code

public function having_open()
{
	return $this->and_having_open();
}

public join( mixed $table [, string $type = NULL ] ) (defined in Kohana_Database_Query_Builder_Select)

Adds addition tables to "JOIN ...".

Parameters

  • mixed $table required - Column name or array($column, $alias) or object
  • string $type = NULL - Join type (LEFT, RIGHT, INNER, etc)

Return Values

  • $this

Source Code

public function join($table, $type = NULL)
{
	$this->_join[] = $this->_last_join = new Database_Query_Builder_Join($table, $type);

	return $this;
}

public offset( integer $number ) (defined in Kohana_Database_Query_Builder_Select)

Start returning results after "OFFSET ..."

Parameters

  • integer $number required - Starting result number

Return Values

  • $this

Source Code

public function offset($number)
{
	$this->_offset = (int) $number;

	return $this;
}

public on( mixed $c1 , string $op , mixed $c2 ) (defined in Kohana_Database_Query_Builder_Select)

Adds "ON ..." conditions for the last created JOIN statement.

Parameters

  • mixed $c1 required - Column name or array($column, $alias) or object
  • string $op required - Logic operator
  • mixed $c2 required - Column name or array($column, $alias) or object

Return Values

  • $this

Source Code

public function on($c1, $op, $c2)
{
	$this->_last_join->on($c1, $op, $c2);

	return $this;
}

public or_having( mixed $column , string $op [, mixed $value = NULL ] ) (defined in Kohana_Database_Query_Builder_Select)

Creates a new "OR HAVING" condition for the query.

Parameters

  • mixed $column required - Column name or array($column, $alias) or object
  • string $op required - Logic operator
  • mixed $value = NULL - Column value

Return Values

  • $this

Source Code

public function or_having($column, $op, $value = NULL)
{
	$this->_having[] = array('OR' => array($column, $op, $value));

	return $this;
}

public or_having_close( ) (defined in Kohana_Database_Query_Builder_Select)

Closes an open "OR HAVING (...)" grouping.

Return Values

  • $this

Source Code

public function or_having_close()
{
	$this->_having[] = array('OR' => ')');

	return $this;
}

public or_having_open( ) (defined in Kohana_Database_Query_Builder_Select)

Opens a new "OR HAVING (...)" grouping.

Return Values

  • $this

Source Code

public function or_having_open()
{
	$this->_having[] = array('OR' => '(');

	return $this;
}

public reset( ) (defined in Kohana_Database_Query_Builder_Select)

Reset the current builder status.

Return Values

  • $this

Source Code

public 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;
}

public select( [ mixed $columns = NULL ] ) (defined in Kohana_Database_Query_Builder_Select)

Choose the columns to select from.

Parameters

  • mixed $columns = NULL - Column name or array($column, $alias) or object

Return Values

  • $this

Source Code

public function select($columns = NULL)
{
	$columns = func_get_args();

	$this->_select = array_merge($this->_select, $columns);

	return $this;
}

public select_array( array $columns ) (defined in Kohana_Database_Query_Builder_Select)

Choose the columns to select from, using an array.

Parameters

  • array $columns required - List of column names or aliases

Return Values

  • $this

Source Code

public function select_array(array $columns)
{
	$this->_select = array_merge($this->_select, $columns);

	return $this;
}

public union( mixed $select [, boolean $all = bool TRUE ] ) (defined in Kohana_Database_Query_Builder_Select)

Adds an other UNION clause.

must be an instance of Database_Query_Builder_Select

Parameters

  • mixed $select required - If string, it must be the name of a table. Else
  • boolean $all = bool TRUE - Decides if it's an UNION or UNION ALL clause

Return Values

  • $this

Source Code

public 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;
}

public using( string $columns ) (defined in Kohana_Database_Query_Builder_Select)

Adds "USING ..." conditions for the last created JOIN statement.

Parameters

  • string $columns required - Column name

Return Values

  • $this

Source Code

public function using($columns)
{
	$columns = func_get_args();

	call_user_func_array(array($this->_last_join, 'using'), $columns);

	return $this;
}

public and_where( mixed $column , string $op , mixed $value ) (defined in Kohana_Database_Query_Builder_Where)

Creates a new "AND WHERE" condition for the query.

Parameters

  • mixed $column required - Column name or array($column, $alias) or object
  • string $op required - Logic operator
  • mixed $value required - Column value

Return Values

  • $this

Source Code

public function and_where($column, $op, $value)
{
	$this->_where[] = array('AND' => array($column, $op, $value));

	return $this;
}

public and_where_close( ) (defined in Kohana_Database_Query_Builder_Where)

Closes an open "AND WHERE (...)" grouping.

Return Values

  • $this

Source Code

public function and_where_close()
{
	$this->_where[] = array('AND' => ')');

	return $this;
}

public and_where_open( ) (defined in Kohana_Database_Query_Builder_Where)

Opens a new "AND WHERE (...)" grouping.

Return Values

  • $this

Source Code

public function and_where_open()
{
	$this->_where[] = array('AND' => '(');

	return $this;
}

public limit( integer $number ) (defined in Kohana_Database_Query_Builder_Where)

Return up to "LIMIT ..." results

Parameters

  • integer $number required - Maximum results to return

Return Values

  • $this

Source Code

public function limit($number)
{
	$this->_limit = (int) $number;

	return $this;
}

public or_where( mixed $column , string $op , mixed $value ) (defined in Kohana_Database_Query_Builder_Where)

Creates a new "OR WHERE" condition for the query.

Parameters

  • mixed $column required - Column name or array($column, $alias) or object
  • string $op required - Logic operator
  • mixed $value required - Column value

Return Values

  • $this

Source Code

public function or_where($column, $op, $value)
{
	$this->_where[] = array('OR' => array($column, $op, $value));

	return $this;
}

public or_where_close( ) (defined in Kohana_Database_Query_Builder_Where)

Closes an open "OR WHERE (...)" grouping.

Return Values

  • $this

Source Code

public function or_where_close()
{
	$this->_where[] = array('OR' => ')');

	return $this;
}

public or_where_open( ) (defined in Kohana_Database_Query_Builder_Where)

Opens a new "OR WHERE (...)" grouping.

Return Values

  • $this

Source Code

public function or_where_open()
{
	$this->_where[] = array('OR' => '(');

	return $this;
}

public order_by( mixed $column [, string $direction = NULL ] ) (defined in Kohana_Database_Query_Builder_Where)

Applies sorting with "ORDER BY ..."

Parameters

  • mixed $column required - Column name or array($column, $alias) or object
  • string $direction = NULL - Direction of sorting

Return Values

  • $this

Source Code

public function order_by($column, $direction = NULL)
{
	$this->_order_by[] = array($column, $direction);

	return $this;
}

public where( mixed $column , string $op , mixed $value ) (defined in Kohana_Database_Query_Builder_Where)

Alias of and_where()

Parameters

  • mixed $column required - Column name or array($column, $alias) or object
  • string $op required - Logic operator
  • mixed $value required - Column value

Return Values

  • $this

Source Code

public function where($column, $op, $value)
{
	return $this->and_where($column, $op, $value);
}

public where_close( ) (defined in Kohana_Database_Query_Builder_Where)

Closes an open "AND WHERE (...)" grouping.

Return Values

  • $this

Source Code

public function where_close()
{
	return $this->and_where_close();
}

public where_open( ) (defined in Kohana_Database_Query_Builder_Where)

Alias of and_where_open()

Return Values

  • $this

Source Code

public function where_open()
{
	return $this->and_where_open();
}

final public __toString( ) (defined in Kohana_Database_Query)

Return the SQL query string.

Return Values

  • string

Source Code

final public function __toString()
{
	try
	{
		// Return the SQL string
		return $this->compile(Database::instance());
	}
	catch (Exception $e)
	{
		return Kohana_Exception::text($e);
	}
}

public as_assoc( ) (defined in Kohana_Database_Query)

Returns results as associative arrays

Return Values

  • $this

Source Code

public function as_assoc()
{
	$this->_as_object = FALSE;

	$this->_object_params = array();

	return $this;
}

public as_object( [ string $class = bool TRUE , array $params = NULL ] ) (defined in Kohana_Database_Query)

Returns results as objects

Parameters

  • string $class = bool TRUE - Classname or TRUE for stdClass
  • array $params = NULL - $params

Return Values

  • $this

Source Code

public function as_object($class = TRUE, array $params = NULL)
{
	$this->_as_object = $class;

	if ($params)
	{
		// Add object parameters
		$this->_object_params = $params;
	}

	return $this;
}

public bind( string $param , mixed & $var ) (defined in Kohana_Database_Query)

Bind a variable to a parameter in the query.

Parameters

  • string $param required - Parameter key to replace
  • byref mixed $var required - Variable to use

Return Values

  • $this

Source Code

public function bind($param, & $var)
{
	// Bind a value to a variable
	$this->_parameters[$param] =& $var;

	return $this;
}

public cached( [ integer $lifetime = NULL ] ) (defined in Kohana_Database_Query)

Enables the query to be cached for a specified amount of time.

Parameters

  • integer $lifetime = NULL - Number of seconds to cache

Tags

Return Values

  • $this

Source Code

public function cached($lifetime = NULL)
{
	if ($lifetime === NULL)
	{
		// Use the global setting
		$lifetime = Kohana::$cache_life;
	}

	$this->_lifetime = $lifetime;

	return $this;
}

public execute( [ mixed $db = NULL ] ) (defined in Kohana_Database_Query)

Execute the current query on the given database.

Parameters

  • mixed $db = NULL - Database instance or name of instance

Return Values

  • object - Database_Result for SELECT queries
  • mixed - The insert id for INSERT queries
  • integer - Number of affected rows for all other queries

Source Code

public function execute($db = NULL)
{
	if ( ! is_object($db))
	{
		// Get the database instance
		$db = Database::instance($db);
	}

	// 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.'")';

		if (($result = Kohana::cache($cache_key, NULL, $this->_lifetime)) !== NULL)
		{
			// Return a cached result
			return new Database_Result_Cached($result, $sql, $this->_as_object, $this->_object_params);
		}
	}

	// Execute the query
	$result = $db->query($this->_type, $sql, $this->_as_object, $this->_object_params);

	if (isset($cache_key))
	{
		// Cache the result array
		Kohana::cache($cache_key, $result->as_array(), $this->_lifetime);
	}

	return $result;
}

public param( string $param , mixed $value ) (defined in Kohana_Database_Query)

Set the value of a parameter in the query.

Parameters

  • string $param required - Parameter key to replace
  • mixed $value required - Value to use

Return Values

  • $this

Source Code

public function param($param, $value)
{
	// Add or overload a new parameter
	$this->_parameters[$param] = $value;

	return $this;
}

public parameters( array $params ) (defined in Kohana_Database_Query)

Add multiple parameters to the query.

Parameters

  • array $params required - List of parameters

Return Values

  • $this

Source Code

public function parameters(array $params)
{
	// Merge the new parameters in
	$this->_parameters = $params + $this->_parameters;

	return $this;
}

public type( ) (defined in Kohana_Database_Query)

Get the type of the query.

Return Values

  • integer

Source Code

public function type()
{
	return $this->_type;
}

protected _compile_conditions( object $db , array $conditions ) (defined in Kohana_Database_Query_Builder)

Compiles an array of conditions into an SQL partial. Used for WHERE and HAVING.

Parameters

  • object $db required - Database instance
  • array $conditions required - Condition statements

Return Values

  • string

Source Code

protected 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;
}

protected _compile_group_by( object $db , array $columns ) (defined in Kohana_Database_Query_Builder)

Compiles an array of GROUP BY columns into an SQL partial.

Parameters

  • object $db required - Database instance
  • array $columns required - $columns

Return Values

  • string

Source Code

protected 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);
}

protected _compile_join( object $db , array $joins ) (defined in Kohana_Database_Query_Builder)

Compiles an array of JOIN statements into an SQL partial.

Parameters

  • object $db required - Database instance
  • array $joins required - Join statements

Return Values

  • string

Source Code

protected 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);
}

protected _compile_order_by( object $db , array $columns ) (defined in Kohana_Database_Query_Builder)

Compiles an array of ORDER BY statements into an SQL partial.

Parameters

  • object $db required - Database instance
  • array $columns required - Sorting columns

Return Values

  • string

Source Code

protected 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);
}

protected _compile_set( object $db , array $values ) (defined in Kohana_Database_Query_Builder)

Compiles an array of set values into an SQL partial. Used for UPDATE.

Parameters

  • object $db required - Database instance
  • array $values required - Updated values

Return Values

  • string

Source Code

protected 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);
}