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

Parameters

• array $columns = NULL - Column list

Return Values

• void

?
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) link to this

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

?
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) link to this

Return Values

• $this

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

public and_having_open( ) (defined in Kohana_Database_Query_Builder_Select) link to this

Return Values

• $this

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

public compile( object $db ) (defined in Kohana_Database_Query_Builder_Select) link to this

Parameters

• object $db required - Database instance

Return Values

• string

?
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) link to this

Parameters

• boolean $value required - Enable or disable distinct columns

Return Values

• $this

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

public from( mixed $tables ) (defined in Kohana_Database_Query_Builder_Select) link to this

Parameters

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

Return Values

• $this

?
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) link to this

Parameters

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

Return Values

• $this

?
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) link to this

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

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

public having_close( ) (defined in Kohana_Database_Query_Builder_Select) link to this

Return Values

• $this

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

public having_open( ) (defined in Kohana_Database_Query_Builder_Select) link to this

Return Values

• $this

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

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

Parameters

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

Return Values

• $this

?
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) link to this

Parameters

• integer $number required - Starting result number or NULL to reset

Return Values

• $this

?
public function offset($number)
{
    $this->_offset = $number;
 
    return $this;
}

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

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

?
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) link to this

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

?
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) link to this

Return Values

• $this

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

public or_having_open( ) (defined in Kohana_Database_Query_Builder_Select) link to this

Return Values

• $this

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

public reset( ) (defined in Kohana_Database_Query_Builder_Select) link to this

Return Values

• $this

?
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) link to this

Parameters

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

Return Values

• $this

?
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) link to this

Parameters

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

Return Values

• $this

?
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) link to this

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

?
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) link to this

Parameters

• string $columns required - Column name

Return Values

• $this

?
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) link to this

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

?
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) link to this

Return Values

• $this

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

public and_where_open( ) (defined in Kohana_Database_Query_Builder_Where) link to this

Return Values

• $this

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

public limit( integer $number ) (defined in Kohana_Database_Query_Builder_Where) link to this

Parameters

• integer $number required - Maximum results to return or NULL to reset

Return Values

• $this

?
public function limit($number)
{
    $this->_limit = $number;
 
    return $this;
}

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

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

?
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) link to this

Return Values

• $this

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

public or_where_open( ) (defined in Kohana_Database_Query_Builder_Where) link to this

Return Values

• $this

?
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) link to this

Parameters

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

Return Values

• $this

?
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) link to this

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

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

public where_close( ) (defined in Kohana_Database_Query_Builder_Where) link to this

Return Values

• $this

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

public where_open( ) (defined in Kohana_Database_Query_Builder_Where) link to this

Return Values

• $this

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

final public __toString( ) (defined in Kohana_Database_Query) link to this

Return Values

• string

?
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) link to this

Return Values

• $this

?
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) link to this

Parameters

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

Return Values

• $this

?
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) link to this

Parameters

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

Return Values

• $this

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

public cached( [ integer $lifetime = NULL , boolean $force = bool FALSE ] ) (defined in Kohana_Database_Query) link to this

Parameters

• 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

Tags

• Uses - Kohana::$cache_life

Return Values

• $this

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

public execute( [ mixed $db = NULL , string $as_object = NULL , array $object_params = NULL ] ) (defined in Kohana_Database_Query) link to this

Parameters

• 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

Return Values

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

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

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

Parameters

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

Return Values

• $this

?
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) link to this

Parameters

• array $params required - List of parameters

Return Values

• $this

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

public type( ) (defined in Kohana_Database_Query) link to this

Return Values

• integer

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

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

Parameters

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

Return Values

• string

?
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) link to this

Parameters

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

Return Values

• string

?
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) link to this

Parameters

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

Return Values

• string

?
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) link to this

Parameters

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

Return Values

• string

?
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) link to this

Parameters

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

Return Values

• string

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