/**
* Build a select, delete or update query
*
* @param \Fuel\Core\Database_Query_Builder_Where DB where() query object
* @param array $columns Optionally
* @param string $type Type of query to build (count/select/update/delete/insert)
*
* @throws \FuelException Models cannot be related between different database connections
* @throws \UnexpectedValueException Trying to get the relation of an unloaded relation
*
* @return array with keys query and relations
*/
public function build_query(\Fuel\Core\Database_Query_Builder_Where $query, $columns = array(), $type = 'select')
{
$read_query = in_array($type, array('select', 'count'));
// Get the limit
if (!is_null($this->limit)) {
$query->limit($this->limit);
}
// Get the offset
if (!is_null($this->offset)) {
$query->offset($this->offset);
}
$where_conditions = call_user_func($this->model . '::condition', 'where');
empty($where_conditions) or $this->where($where_conditions);
$where_backup = $this->where;
if (!empty($this->where)) {
$open_nests = 0;
$where_nested = array();
$include_nested = true;
foreach ($this->where as $key => $w) {
list($method, $conditional) = $w;
if ($read_query and (empty($conditional) or $open_nests > 0)) {
$include_nested and $where_nested[$key] = $w;
if (!empty($conditional) and strpos($conditional[0], $this->alias . '.') !== 0) {
$include_nested = false;
}
strpos($method, '_open') and $open_nests++;
strpos($method, '_close') and $open_nests--;
continue;
}
if (empty($conditional) or strpos($conditional[0], $this->alias . '.') === 0 or !$read_query and $conditional[0] instanceof \Fuel\Core\Database_Expression) {
if (!$read_query and !empty($conditional) and !$conditional[0] instanceof \Fuel\Core\Database_Expression) {
$conditional[0] = substr($conditional[0], strlen($this->alias . '.'));
}
call_fuel_func_array(array($query, $method), $conditional);
unset($this->where[$key]);
}
}
if ($include_nested and !empty($where_nested)) {
foreach ($where_nested as $key => $w) {
list($method, $conditional) = $w;
if (empty($conditional) or strpos($conditional[0], $this->alias . '.') === 0 or !$read_query and $conditional[0] instanceof \Fuel\Core\Database_Expression) {
if (!$read_query and !empty($conditional) and !$conditional[0] instanceof \Fuel\Core\Database_Expression) {
$conditional[0] = substr($conditional[0], strlen($this->alias . '.'));
}
call_fuel_func_array(array($query, $method), $conditional);
unset($this->where[$key]);
}
}
}
}
// If it's not a select or count, we're done
if (!$read_query) {
return array('query' => $query, 'models' => array());
}
$i = 1;
$models = array();
foreach ($this->relations as $name => $rel) {
// when there's a dot it must be a nested relation
if ($pos = strrpos($name, '.')) {
if (empty($models[substr($name, 0, $pos)]['table'][1])) {
throw new \UnexpectedValueException('Trying to get the relation of an unloaded relation, make sure you load the parent relation before any of its children.');
}
$alias = $models[substr($name, 0, $pos)]['table'][1];
} else {
$alias = $this->alias;
}
$join = $rel[0]->join($alias, $name, $i++, $rel[1]);
$models = array_merge($models, $this->modify_join_result($join, $name));
}
// if no order_by was given, see if a default was defined in the model
empty($this->order_by) and $this->order_by(call_user_func($this->model . '::condition', 'order_by'));
if ($this->use_subquery()) {
// Count queries should not select on anything besides the count
if ($type != 'count') {
// Get the columns for final select
foreach ($models as $m) {
foreach ($m['columns'] as $c) {
$columns[] = $c;
}
}
}
// do we need to add order_by clauses on the subquery?
foreach ($this->order_by as $idx => $ob) {
if (!$ob[0] instanceof \Fuel\Core\Database_Expression) {
if (strpos($ob[0], $this->alias . '.') === 0) {
// order by on the current model
$read_query or $ob[0] = substr($ob[0], strlen($this->alias . '.'));
$query->order_by($ob[0], $ob[1]);
}
}
}
// make current query subquery of ultimate query
$new_query = call_fuel_func_array('DB::select', $columns);
$query = $new_query->from(array($query, $this->alias));
} else {
// Count queries should not select on anything besides the count
if ($type != 'count') {
// add additional selected columns
foreach ($models as $m) {
foreach ($m['columns'] as $c) {
$query->select($c);
}
}
}
}
// join tables
foreach ($this->joins as $j) {
$join_query = $query->join($j['table'], $j['join_type']);
foreach ($j['join_on'] as $on) {
$join_query->on($on[0], $on[1], $on[2]);
}
}
foreach ($models as $m) {
if ($read_query and $m['connection'] != $this->connection or !$read_query and $m['connection'] != $this->write_connection) {
throw new \FuelException('Models cannot be related between different database connections.');
}
$join_query = $query->join($m['table'], $m['join_type']);
foreach ($m['join_on'] as $on) {
$join_query->on($on[0], $on[1], $on[2]);
}
}
// Get the order, if none set see if we have an order_by condition set
$order_by = $order_by_backup = $this->order_by;
// Add any additional order_by and where clauses from the relations
foreach ($models as $m_name => $m) {
if (!empty($m['order_by'])) {
foreach ((array) $m['order_by'] as $k_ob => $v_ob) {
if (is_int($k_ob)) {
$v_dir = is_array($v_ob) ? $v_ob[1] : 'ASC';
$v_ob = is_array($v_ob) ? $v_ob[0] : $v_ob;
if (!$v_ob instanceof \Fuel\Core\Database_Expression and strpos($v_ob, '.') === false) {
$v_ob = $m_name . '.' . $v_ob;
}
$order_by[] = array($v_ob, $v_dir);
} else {
strpos($k_ob, '.') === false and $k_ob = $m_name . '.' . $k_ob;
$order_by[] = array($k_ob, $v_ob);
}
}
}
if (!empty($m['where'])) {
$this->_parse_where_array($m['where'], $m_name . '.');
}
}
// Get the order
if (!empty($order_by)) {
foreach ($order_by as $ob) {
if (!$ob[0] instanceof \Fuel\Core\Database_Expression) {
if (strpos($ob[0], $this->alias . '.') === 0) {
// order by on the current model
$read_query or $ob[0] = substr($ob[0], strlen($this->alias . '.'));
} else {
// try to rewrite conditions on the relations to their table alias
$dotpos = strrpos($ob[0], '.');
$relation = substr($ob[0], 0, $dotpos);
if ($dotpos > 0 and array_key_exists($relation, $models)) {
$ob[0] = $models[$relation]['table'][1] . substr($ob[0], $dotpos);
}
}
}
$query->order_by($ob[0], $ob[1]);
}
}
// Get the grouping
if (!empty($this->group_by)) {
foreach ($this->group_by as $gb) {
if (!$gb instanceof \Fuel\Core\Database_Expression) {
if (strpos($gb, $this->alias . '.') === false) {
// try to rewrite on the relations to their table alias
$dotpos = strrpos($gb, '.');
$relation = substr($gb, 0, $dotpos);
if ($dotpos > 0) {
if (array_key_exists($relation, $models)) {
$gb = $models[$relation]['table'][1] . substr($gb, $dotpos);
}
} else {
$gb = $this->alias . '.' . $gb;
}
}
}
$query->group_by($gb);
}
}
// put omitted where conditions back
if (!empty($this->where)) {
foreach ($this->where as $w) {
list($method, $conditional) = $w;
// try to rewrite conditions on the relations to their table alias
if (!empty($conditional)) {
$dotpos = strrpos($conditional[0], '.');
$relation = substr($conditional[0], 0, $dotpos);
if ($dotpos > 0 and array_key_exists($relation, $models)) {
$conditional[0] = $models[$relation]['table'][1] . substr($conditional[0], $dotpos);
}
}
call_fuel_func_array(array($query, $method), $conditional);
}
}
$this->where = $where_backup;
$this->order_by = $order_by_backup;
// Set the row limit and offset, these are applied to the outer query when a subquery
// is used or overwrite limit/offset when it's a normal query
!is_null($this->rows_limit) and $query->limit($this->rows_limit);
!is_null($this->rows_offset) and $query->offset($this->rows_offset);
return array('query' => $query, 'models' => $models);
}
/**
* Build a select, delete or update query
*
* @param \Fuel\Core\Database_Query_Builder_Where
* @param string|select either array for select query or string update, delete, insert
* @return array with keys query and relations
*/
public function build_query(\Fuel\Core\Database_Query_Builder_Where $query, $columns = array(), $type = 'select')
{
// Get the limit
if (!is_null($this->limit)) {
$query->limit($this->limit);
}
// Get the offset
if (!is_null($this->offset)) {
$query->offset($this->offset);
}
// Get the order
$order_by = $this->order_by;
// create a backup for subquery
$order_by_backup = $order_by;
if (!empty($order_by)) {
foreach ($order_by as $key => $ob) {
if (!$ob[0] instanceof \Fuel\Core\Database_Expression and strpos($ob[0], $this->alias . '.') === 0) {
$query->order_by($type == 'select' ? $ob[0] : substr($ob[0], strlen($this->alias . '.')), $ob[1]);
// order by has been updated to Database_Query_Builder_Where instance, set it to empty to avoid duplicate entries
unset($order_by[$key]);
}
}
}
$where_backup = $this->where;
if (!empty($this->where)) {
$open_nests = 0;
foreach ($this->where as $key => $w) {
list($method, $conditional) = $w;
if ($type == 'select' and (empty($conditional) or $open_nests > 0)) {
strpos($method, '_open') and $open_nests++;
strpos($method, '_close') and $open_nests--;
continue;
}
if (empty($conditional) or strpos($conditional[0], $this->alias . '.') === 0 or $type != 'select' and $conditional[0] instanceof \Fuel\Core\Database_Expression) {
if ($type != 'select' and !empty($conditional) and !$conditional[0] instanceof \Fuel\Core\Database_Expression) {
$conditional[0] = substr($conditional[0], strlen($this->alias . '.'));
}
call_user_func_array(array($query, $method), $conditional);
unset($this->where[$key]);
}
}
}
// If it's not a select we're done
if ($type != 'select') {
return array('query' => $query, 'models' => array());
}
$i = 1;
$models = array();
foreach ($this->relations as $name => $rel) {
// when there's a dot it must be a nested relation
if ($pos = strrpos($name, '.')) {
if (empty($models[substr($name, 0, $pos)]['table'][1])) {
throw new \UnexpectedValueException('Trying to get the relation of an unloaded relation, make sure you load the parent relation before any of its children.');
}
$alias = $models[substr($name, 0, $pos)]['table'][1];
} else {
$alias = $this->alias;
}
$models = array_merge($models, $rel[0]->join($alias, $name, $i++, $rel[1]));
}
if ($this->use_subquery()) {
// Get the columns for final select
foreach ($models as $m) {
foreach ($m['columns'] as $c) {
$columns[] = $c;
}
}
// make current query subquery of ultimate query
$new_query = call_user_func_array('DB::select', $columns);
$query = $new_query->from(array($query, $this->alias));
// set order_by from backup
$order_by = $order_by_backup;
} else {
// add additional selected columns
foreach ($models as $m) {
foreach ($m['columns'] as $c) {
$query->select($c);
}
}
}
// join tables
foreach ($this->joins as $j) {
$join_query = $query->join($j['table'], $j['join_type']);
foreach ($j['join_on'] as $on) {
$join_query->on($on[0], $on[1], $on[2]);
}
}
foreach ($models as $m) {
if ($m['connection'] != $this->connection) {
throw new \FuelException('Models cannot be related between connection.');
}
$join_query = $query->join($m['table'], $m['join_type']);
foreach ($m['join_on'] as $on) {
$join_query->on($on[0], $on[1], $on[2]);
}
}
// Add any additional order_by and where clauses from the relations
foreach ($models as $m_name => $m) {
if (!empty($m['order_by'])) {
foreach ((array) $m['order_by'] as $k_ob => $v_ob) {
if (is_int($k_ob)) {
$v_dir = is_array($v_ob) ? $v_ob[1] : 'ASC';
$v_ob = is_array($v_ob) ? $v_ob[0] : $v_ob;
if (!$v_ob instanceof \Fuel\Core\Database_Expression and strpos($v_ob, '.') === false) {
$v_ob = $m_name . '.' . $v_ob;
}
$order_by[] = array($v_ob, $v_dir);
} else {
strpos($k_ob, '.') === false and $k_ob = $m_name . '.' . $k_ob;
$order_by[] = array($k_ob, $v_ob);
}
}
}
if (!empty($m['where'])) {
$this->_parse_where_array($m['where'], $m_name . '.');
}
}
// Get the order
if (!empty($order_by)) {
foreach ($order_by as $ob) {
if (!$ob[0] instanceof \Fuel\Core\Database_Expression) {
// try to rewrite conditions on the relations to their table alias
$dotpos = strrpos($ob[0], '.');
$relation = substr($ob[0], 0, $dotpos);
if ($dotpos > 0 and array_key_exists($relation, $models)) {
$ob[0] = $models[$relation]['table'][1] . substr($ob[0], $dotpos);
}
}
$query->order_by($ob[0], $ob[1]);
}
}
// put omitted where conditions back
if (!empty($this->where)) {
foreach ($this->where as $w) {
list($method, $conditional) = $w;
// try to rewrite conditions on the relations to their table alias
if (!empty($conditional)) {
$dotpos = strrpos($conditional[0], '.');
$relation = substr($conditional[0], 0, $dotpos);
if ($dotpos > 0 and array_key_exists($relation, $models)) {
$conditional[0] = $models[$relation]['table'][1] . substr($conditional[0], $dotpos);
}
}
call_user_func_array(array($query, $method), $conditional);
}
}
$this->where = $where_backup;
// Set the row limit and offset, these are applied to the outer query when a subquery
// is used or overwrite limit/offset when it's a normal query
!is_null($this->rows_limit) and $query->limit($this->rows_limit);
!is_null($this->rows_offset) and $query->offset($this->rows_offset);
return array('query' => $query, 'models' => $models);
}
