/classes/database/query/builder/select.php
PHP | 524 lines | 232 code | 76 blank | 216 comment | 17 complexity | 059bbd3d4dd5b77fb24c33ea953664c1 MD5 | raw file
Possible License(s): BSD-3-Clause
- <?php
- /**
- * Fuel is a fast, lightweight, community driven PHP 5.4+ framework.
- *
- * @package Fuel
- * @version 1.9-dev
- * @author Fuel Development Team
- * @license MIT License
- * @copyright 2010 - 2019 Fuel Development Team
- * @copyright 2008 - 2009 Kohana Team
- * @link https://fuelphp.com
- */
- namespace Fuel\Core;
- class Database_Query_Builder_Select extends \Database_Query_Builder_Where
- {
- /**
- * @var array $_select columns to select
- */
- protected $_select = array();
- /**
- * @var bool $_distinct whether to select distinct values
- */
- protected $_distinct = false;
- /**
- * @var array $_from table name
- */
- protected $_from = array();
- /**
- * @var array $_join join objects
- */
- protected $_join = array();
- /**
- * @var array $_group_by group by clauses
- */
- protected $_group_by = array();
- /**
- * @var array $_having having clauses
- */
- protected $_having = array();
- /**
- * @var integer $_offset offset
- */
- protected $_offset = null;
- /**
- * @var Database_Query_Builder_Join $_last_join last join statement
- */
- protected $_last_join;
- /**
- * Sets the initial columns to select from.
- *
- * @param array $columns column list
- */
- 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('', \DB::SELECT);
- }
- /**
- * Enables or disables selecting only unique columns using "SELECT DISTINCT"
- *
- * @param boolean $value enable or disable distinct columns
- * @return $this
- */
- public function distinct($value = true)
- {
- $this->_distinct = (bool) $value;
- return $this;
- }
- /**
- * Choose the columns to select from.
- *
- * @param mixed $columns column name or array($column, $alias) or object
- * @param ...
- *
- * @return $this
- */
- public 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.
- *
- * @param array $columns list of column names or aliases
- * @param bool $reset if true, don't merge but overwrite
- *
- * @return $this
- */
- public function select_array(array $columns, $reset = false)
- {
- $this->_select = $reset ? $columns : array_merge($this->_select, $columns);
- return $this;
- }
- /**
- * Choose the tables to select "FROM ..."
- *
- * @param mixed $tables table name or array($table, $alias)
- * @param ...
- *
- * @return $this
- */
- public function from($tables)
- {
- $tables = func_get_args();
- $this->_from = array_merge($this->_from, $tables);
- return $this;
- }
- /**
- * Adds addition tables to "JOIN ...".
- *
- * @param mixed $table column name or array($column, $alias)
- * @param string $type join type (LEFT, RIGHT, INNER, etc)
- *
- * @return $this
- */
- public function join($table, $type = NULL)
- {
- $this->_join[] = $this->_last_join = new \Database_Query_Builder_Join($table, $type);
- return $this;
- }
- /**
- * Adds "ON ..." conditions for the last created JOIN statement.
- *
- * @param mixed $c1 column name or array($column, $alias) or object
- * @param string $op logic operator
- * @param mixed $c2 column name or array($column, $alias) or object
- *
- * @return $this
- */
- public function on($c1, $op, $c2)
- {
- $this->_last_join->on($c1, $op, $c2);
- return $this;
- }
- /**
- * Adds "AND ON ..." conditions for the last created JOIN statement.
- *
- * @param mixed $c1 column name or array($column, $alias) or object
- * @param string $op logic operator
- * @param mixed $c2 column name or array($column, $alias) or object
- *
- * @return $this
- */
- public function and_on($c1, $op, $c2)
- {
- $this->_last_join->and_on($c1, $op, $c2);
- return $this;
- }
- /**
- * Adds "OR ON ..." conditions for the last created JOIN statement.
- *
- * @param mixed $c1 column name or array($column, $alias) or object
- * @param string $op logic operator
- * @param mixed $c2 column name or array($column, $alias) or object
- *
- * @return $this
- */
- public function or_on($c1, $op, $c2)
- {
- $this->_last_join->or_on($c1, $op, $c2);
- return $this;
- }
- /**
- * Adds an opening bracket the last created JOIN statement.
- *
- * @return $this
- */
- public function on_open()
- {
- $this->_last_join->on_open();
- return $this;
- }
- /**
- * Adds a closing bracket for the last created JOIN statement.
- *
- * @return $this
- */
- public function on_close()
- {
- $this->_last_join->on_close();
- return $this;
- }
- /**
- * Creates a "GROUP BY ..." filter.
- *
- * @param mixed $columns column name or array($column, $column) or object
- * @param ...
- *
- * @return $this
- */
- public function group_by($columns)
- {
- $columns = func_get_args();
- foreach($columns as $idx => $column)
- {
- // if an array of columns is passed, flatten it
- if (is_array($column))
- {
- foreach($column as $c)
- {
- $columns[] = $c;
- }
- unset($columns[$idx]);
- }
- }
- $this->_group_by = array_merge($this->_group_by, $columns);
- return $this;
- }
- /**
- * Alias of and_having()
- *
- * @param mixed $column column name or array($column, $alias) or object
- * @param string $op logic operator
- * @param mixed $value column value
- *
- * @return $this
- */
- public function having($column, $op = null, $value = null)
- {
- return call_fuel_func_array(array($this, 'and_having'), func_get_args());
- }
- /**
- * Creates a new "AND HAVING" condition for the query.
- *
- * @param mixed $column column name or array($column, $alias) or object
- * @param string $op logic operator
- * @param mixed $value column value
- *
- * @return $this
- */
- public function and_having($column, $op = null, $value = null)
- {
- if($column instanceof \Closure)
- {
- $this->and_having_open();
- $column($this);
- $this->and_having_close();
- return $this;
- }
- if(func_num_args() === 2)
- {
- $value = $op;
- $op = '=';
- }
- $this->_having[] = array('AND' => array($column, $op, $value));
- return $this;
- }
- /**
- * Creates a new "OR HAVING" condition for the query.
- *
- * @param mixed $column column name or array($column, $alias) or object
- * @param string $op logic operator
- * @param mixed $value column value
- *
- * @return $this
- */
- public function or_having($column, $op = null, $value = null)
- {
- if($column instanceof \Closure)
- {
- $this->or_having_open();
- $column($this);
- $this->or_having_close();
- return $this;
- }
- if(func_num_args() === 2)
- {
- $value = $op;
- $op = '=';
- }
- $this->_having[] = array('OR' => array($column, $op, $value));
- return $this;
- }
- /**
- * Alias of and_having_open()
- *
- * @return $this
- */
- public function having_open()
- {
- return $this->and_having_open();
- }
- /**
- * Opens a new "AND HAVING (...)" grouping.
- *
- * @return $this
- */
- public function and_having_open()
- {
- $this->_having[] = array('AND' => '(');
- return $this;
- }
- /**
- * Opens a new "OR HAVING (...)" grouping.
- *
- * @return $this
- */
- public function or_having_open()
- {
- $this->_having[] = array('OR' => '(');
- return $this;
- }
- /**
- * Closes an open "AND HAVING (...)" grouping.
- *
- * @return $this
- */
- public function having_close()
- {
- return $this->and_having_close();
- }
- /**
- * Closes an open "AND HAVING (...)" grouping.
- *
- * @return $this
- */
- public function and_having_close()
- {
- $this->_having[] = array('AND' => ')');
- return $this;
- }
- /**
- * Closes an open "OR HAVING (...)" grouping.
- *
- * @return $this
- */
- public function or_having_close()
- {
- $this->_having[] = array('OR' => ')');
- return $this;
- }
- /**
- * Start returning results after "OFFSET ..."
- *
- * @param integer $number starting result number
- *
- * @return $this
- */
- public function offset($number)
- {
- $this->_offset = (int) $number;
- return $this;
- }
- /**
- * Compile the SQL query and return it.
- *
- * @param mixed $db Database_Connection instance or instance name
- *
- * @return string
- */
- public function compile($db = null)
- {
- if ( ! $db instanceof \Database_Connection)
- {
- // Get the database instance
- $db = $this->_connection ?: \Database_Connection::instance($db);
- }
- // Callback to quote identifiers
- $quote_ident = array($db, 'quote_identifier');
- // 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_ident, $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 sorting
- $query .= ' GROUP BY '.implode(', ', array_map($quote_ident, $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;
- }
- return $query;
- }
- /**
- * Reset the query parameters
- * @return $this
- */
- public function reset()
- {
- $this->_select = array();
- $this->_from = array();
- $this->_join = array();
- $this->_where = array();
- $this->_group_by = array();
- $this->_having = array();
- $this->_order_by = array();
- $this->_distinct = false;
- $this->_limit = null;
- $this->_offset = null;
- $this->_last_join = null;
- $this->_parameters = array();
- return $this;
- }
- }