/** * 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); }