public function builder_call_update_children(Database_Query $builder, Jam_Event_Data $data, $old_path, $new_path)
{
if (FALSE === $builder instanceof Jam_Query_Builder_Update) {
throw new InvalidArgumentException('Can only be used on "update" queries');
}
$builder->value('path', DB::expr("TRIM(BOTH '/' FROM REPLACE(path, :old_path, :new_path))", array(':old_path' => $old_path, ':new_path' => $new_path)));
}
public function __init()
{
$q1 = new Database_Query();
// $q1->insert("names", DB_IGNORE)->set('name','test1')->build()->show()->execute();
/*
$q1 = new Database_Query();
$q1 ->insert("names",'name',DB_IGNORE)
->select('name')
->frm('names')
->build()
->show()
->execute();
*/
$db = new Database_Query();
$db->select('id as id', 'name as test')->frm('names')->build()->execute();
/*
->where(
$db->_where('id = 1',DB_OR,'id = 4'),
DB_AND,
$db->_where('id = 2'),
DB_AND,
'id = 3'
)
*/
// print_r($db->results());
$results = $db->results();
$this->v_test = $results;
$this->extract['test'] = $results;
}
/**
* $builder->deleted(Jam_Behavior_Paranoid::ALL),
* $builder->deleted(Jam_Behavior_Paranoid::DELETED),
* $builder->deleted(Jam_Behavior_Paranoid::NORMAL)
*
* @param Jam_Builder $builder
* @param Jam_Event_Data $data
* @param string $paranoid_filter_type
*/
public function builder_call_deleted(Database_Query $builder, Jam_Event_Data $data, $paranoid_filter_type = Jam_Behavior_Paranoid::NORMAL)
{
if (!in_array($paranoid_filter_type, array(Jam_Behavior_Paranoid::DELETED, Jam_Behavior_Paranoid::NORMAL, Jam_Behavior_Paranoid::ALL))) {
throw new Kohana_Exception("Deleted type should be Jam_Behavior_Paranoid::DELETED, Jam_Behavior_Paranoid::NORMAL or Jam_Behavior_Paranoid::ALL");
}
$builder->params('paranoid_filter_type', $paranoid_filter_type);
}
/**
* Use a sub-query to for the inserted values.
*
* @param object $query Database_Query of SELECT type
* @return $this
*/
public function select(Database_Query $query)
{
if ($query->type() !== Database::SELECT) {
die('Only SELECT queries can be combined with INSERT queries');
}
$this->_values = $query;
return $this;
}
public function select(Database_Query $query)
{
if ($query->type() !== Database::SELECT) {
throw new JsonApiApplication_Exception('Only SELECT queries can be combined with INSERT queries');
}
$this->_values = $query;
return $this;
}
/**
* $builder->root() select only the root items
*
* @param Jam_Builder $builder
* @param Jam_Event_Data $data
* @return Jam_Builder
*/
public function builder_call_root(Database_Query $builder, Jam_Event_Data $data, $is_root = TRUE)
{
if ($is_root) {
$data->return = $builder->where_open()->where($this->_field, '=', 0)->or_where($this->_field, 'IS', NULL)->where_close();
} else {
$data->return = $builder->where($this->_field, '!=', 0)->where($this->_field, 'IS NOT', NULL);
}
$data->stop = TRUE;
}
/**
* Set a primary key condition. If its an array make in an IN condition.
* @param Database_Query $query
* @param string $key
* @return Database_Query
*/
public static function find_by_primary_key(Database_Query $query, $key)
{
if (is_array($key)) {
if (!$key) {
throw new Kohana_Exception('Arrays of primary keys is empty');
}
$query->where(':primary_key', 'IN', $key);
} else {
if (!$key) {
throw new Kohana_Exception('Primary key must not be empty');
}
$query->where(':unique_key', '=', $key);
}
return $query;
}
public function compile($db = NULL)
{
if (!is_object($db)) {
// Get connected to the DB
$db = Database::instance($db);
}
// Start the insert query
$query = 'INSERT INTO ' . $db->quote_table($this->_table);
// Add the column names
$query .= ' (' . implode(', ', array_map(array($db, 'quote_column'), $this->_columns)) . ') ';
if (is_array($this->_values)) {
// Callback for quoting values
$quote = array($db, 'quote');
$groups = array();
foreach ($this->_values as $group) {
foreach ($group as $offset => $value) {
if ((is_string($value) and array_key_exists($value, $this->_parameters)) === FALSE) {
// Quote the value, it is not a parameter
$group[$offset] = $db->quote($value);
}
}
$groups[] = '(' . implode(', ', $group) . ')';
}
// Add the values
$query .= 'VALUES ' . implode(', ', $groups);
}
$this->_sql = $query;
return parent::compile($db);
}
/**
* Use a sub-query to for the inserted values.
*
* @param object Database_Query of SELECT type
* @return $this
*/
public function select(Database_Query $query)
{
if ($query->type() !== Database::SELECT) {
throw new Kohana_Exception('Only SELECT queries can be combined with REPLACE queries');
}
$this->_values = $query;
return $this;
}
public function builder_call_with_terms(Database_Query $builder, Jam_Event_Data $data, $term_slugs, $operator = 'IN', $nesting_level = 1)
{
if ($term_slugs) {
if (!$term_slugs instanceof Jam_Query_Builder_Collection and !$term_slugs instanceof Jam_Array_Association) {
$term_slugs = Jam::all('term')->slugs_children($term_slugs)->order_by('id');
}
$terms = $term_slugs->as_array('id', 'slug');
$terms_ids = array_keys($terms);
if ($nesting_level > 1) {
$terms = Jam::all('term')->slugs_children(array_values($terms))->order_by('id');
$terms_ids = Arr::merge($terms_ids, $terms->ids());
}
if ($terms_ids) {
$unique_alias = 'terms-' . join('-', $terms_ids);
$builder->join(array('terms_items', $unique_alias))->on($unique_alias . '.term_id', $operator, DB::expr("(" . join(',', $terms_ids) . ')'));
}
}
return $builder;
}
public function compile($db = NULL)
{
if (!is_object($db)) {
// Get connected to the DB
$db = Database::instance($db);
}
// Start the insert query
$query = 'TRUNCATE TABLE ' . $db->quote_table($this->_table);
$this->_sql = $query;
return parent::compile($db);
}
/**
* Updates active row in database
*
* @param array $aFields - name of fields which should be updated
* @return bool
*/
public function update($aFields = array())
{
if (!is_array($aFields)) {
$aFields = array($aFields);
}
// Clean params array - it should contain only primary key
$this->aQueryParams = array();
$this->aQueryParams[] = array(Database_Query::C_AND, array($this->sPrimaryKey, Database_Query::parseParam('='), $this->aData[$this->iDataActiveElement][$this->sPrimaryKey]));
$aFieldValues = array();
if (empty($aFields)) {
// Take all fields except primary key
$aFieldValues = $this->aData[$this->iDataActiveElement];
unset($aFieldValues[$this->sPrimaryKey]);
} else {
// Take only specific fields
foreach ($aFields as $sName) {
if (isset($this->aData[$this->iDataActiveElement][$sName])) {
$aFieldValues[$sName] = $this->aData[$this->iDataActiveElement][$sName];
}
}
}
$oQuery = $this->createQuery(Database_Query::T_UPDATE, $aFieldValues, false);
return $this->oDB->query($oQuery);
}
/**
*
* @param string $keyword
* @param boolean $only_title
* @param string $modules
* @param integer $limit
* @param integer $offset
* @param boolean $fulltextsearch
* @return Database_Query
*/
protected function _get_query(Database_Query $query, $keyword, $only_title = FALSE, $modules = NULL, $limit = 50, $offset = 0)
{
$keyword = $this->stem_query($keyword);
if ($limit !== NULL) {
$query->limit((int) $limit)->offset($offset);
}
if (is_array($modules)) {
$query->where('search_index.module', 'in', $modules);
} elseif ($modules !== NULL) {
$query->where('search_index.module', '=', $modules);
}
if ($this->config('full_text_search') === TRUE) {
$query->where(DB::expr('MATCH(`search_index`.`header`, `search_index`.`content`)'), 'AGAINST', DB::expr("('" . self::match_against_query($keyword) . "' IN BOOLEAN MODE)"));
} else {
$words = explode(' ', $keyword);
$query->where_open();
$query->where_open();
foreach ($words as $word) {
if (!empty($word)) {
$query->where('search_index.header', 'like', '%' . $word . '%');
}
}
$query->where_close();
if ($only_title === FALSE) {
$query->or_where_open();
foreach ($words as $word) {
if (!empty($word)) {
$query->where('search_index.content', 'like', '%' . $word . '%');
}
}
$query->where_close();
}
$query->where_close();
}
return $query;
}
protected function select_one_column(Database_Query $query, $column)
{
$results = $query->execute($this->db);
return count($results) ? $results->get($column) : FALSE;
}
public function search_by_keyword(Database_Query $query)
{
$keyword = Request::initial()->query('search');
if (is_array($keyword)) {
$fields = array();
foreach ($keyword as $field => $value) {
if (in_array($field, $this->fields())) {
continue;
}
$fields[$field] = $value;
}
if (!empty($fields)) {
$query->where_open();
foreach ($fields as $field => $value) {
$field = Arr::get($this->fields(), $field);
$query->and_where(Arr::get($field, 'db_name'), 'like', '%' . $value . '%');
}
return $query->where_close();
}
return $query;
} else {
if (empty($keyword)) {
return $query;
}
}
$query->where_open()->or_where('d.id', 'like', '%' . $keyword . '%')->or_where('d.header', 'like', '%' . $keyword . '%');
foreach ($this->fields() as $field) {
if (Arr::get($field, 'searchable') === FALSE or Arr::get($field, 'db_name') === NULL) {
continue;
}
$query->or_where(Arr::get($field, 'db_name'), 'like', '%' . $keyword . '%');
}
return $query->where_close();
}
public function get_query_props(Database_Query $query, DataSource_Hybrid_Agent $agent)
{
$sub_query = DB::select(DB::expr("GROUP_CONCAT(related_id SEPARATOR ',')"))->from('dshybrid_relations')->where('document_id', '=', DB::expr('d.id'))->where('field_id', '=', $this->id);
$query->select(array($sub_query, $this->id));
}
public function builder_call_expired(Database_Query $query, Jam_Event_Data $data, $token = TRUE, $current_time = NULL)
{
$query->where('expires', (bool) $token ? '<' : '>=', $current_time === NULL ? time() : $current_time);
}
/**
* Метод используется для поиска по документам по ключевому слову.
*
* Ключевое слово передается в качестве $_GET запроса с ключем "keyword"
*
* @param Database_Query $query
* @return Database_Query
*/
public function search_by_keyword(Database_Query $query)
{
$keyword = Request::initial()->query('keyword');
if (empty($keyword)) {
return $query;
}
if ($this->_section->is_indexable()) {
$ids = Search::instance()->find_by_keyword($keyword, FALSE, 'ds_' . $this->_section->id(), NULL);
$ids = Arr::get($ids, 'ds_' . $this->_section->id());
if (!empty($ids)) {
$query->where('d.id', 'in', array_keys($ids));
} else {
$query->where('d.id', '=', 0);
}
} else {
$query->where_open()->where('d.id', 'like', '%' . $keyword . '%')->or_where('d.header', 'like', '%' . $keyword . '%')->where_close();
}
return $query;
}
/**
* Условие фильтрации текущего поля, если оно используется в фильтре виджета
* "Список ГД документов"
*
* @see DataSource_Hybrid_Agent::_fetch_filters()
*
* @param Database_Query $query
* @param string $condition
* @param string $value
* @return type
*/
public function filter_condition(Database_Query $query, $condition, $value, array $params = NULL)
{
$field = $this->name;
if (isset($params['db_function'])) {
$field = $this->build_sql_field($params['db_function'], $field, $value);
}
$query->where($field, $condition, $value);
}
/**
*
* @param Database_Query $db
* @param array $clause
* @return type
*/
public static function _conditions($db, array $clause)
{
foreach ($clause as $type => $params) {
switch ($type) {
case 'select':
foreach ($params as $param) {
$db->select($param);
}
break;
case 'where':
foreach ($params as $param) {
$db->where($param[0], $param[1], $param[2]);
}
break;
case 'or_where':
foreach ($params as $param) {
$db->or_where($param[0], $param[1], $param[2]);
}
break;
case 'order_by':
foreach ($params as $param) {
$db->order_by($param[0], $param[1]);
}
break;
case 'limit':
$db->limit((int) $params);
break;
case 'offset':
$db->offset((int) $params);
break;
}
}
return $db;
}
/**
* Asignamos el tipo por defecto usado cuando se devuelven datos.
* @param int $type Tipo a usar.
* @throws Database_Exception
* @author Ignacio Daniel Rostagno <*****@*****.**>
*/
public function set_fetch_type($type)
{
parent::set_fetch_type($type);
$this->query->setFetchMode($this->fetch_mode_pdo($type));
}
/**
* Abort the current transaction
*
* @return boolean
*/
public static function rollback($db = NULL)
{
$query = new Database_Query(NULL, NULL);
return $query->rollback($db);
}
/**
* For a given foreign key column, join the given query to the foreign table
* and return the alias for use in selecting against the foreign table.
*
* If the column is not a foreign key, the alias will just be the qualified
* column name, and no join will be done.
*
* @param WebDB_DBMS_Column $column The column (usually a FK).
* @param Database_Query $query The query.
*
* @return array Array with 'join_clause' and 'column_alias' keys
*/
public function join_for($column, &$query)
{
$token = Profiler::start('WebDB', __METHOD__);
if (!$column->is_foreign_key()) {
return $this->get_name() . '.' . $column->get_name();
}
$fk1_table = $column->get_referenced_table();
$fk1_title_column = $fk1_table->get_title_column();
$fk1_alias = 'f' . $this->alias_count;
$query->join(array($fk1_table->get_name(), $fk1_alias), 'LEFT OUTER')->on($this->_name . '.' . $column->get_name(), '=', $fk1_alias . '.id');
$alias = $fk1_alias . '.' . $fk1_title_column->get_name();
// FK is also an FK?
if ($fk1_title_column->is_foreign_key()) {
$fk2_table = $fk1_title_column->get_referenced_table();
$fk2_title_column = $fk2_table->get_title_column();
$fk2_alias = 'ff' . $this->alias_count;
$query->join(array($fk2_table->get_name(), $fk2_alias), 'LEFT OUTER')->on($fk1_alias . '.' . $fk1_title_column->get_name(), '=', $fk2_alias . '.id');
$alias = $fk2_alias . '.' . $fk2_title_column->get_name();
}
$this->alias_count++;
Profiler::stop($token);
return $alias;
}
public function filter_condition(Database_Query $query, $condition, $value, array $params = NULL)
{
$query->having($this->id, $condition, $value);
}
/**
*
* @param Database_Query $query
*/
protected function _search_by_keyword(Database_Query $query, $only_title = FALSE)
{
if ($this->search_key === NULL or trim($this->search_key) == '') {
return $query;
}
$keyword = $this->_ctx->get($this->search_key);
if (empty($keyword)) {
return $query;
}
$ids = Search::instance()->find_by_keyword($keyword, $only_title, 'ds_' . $this->ds_id, NULL);
$ids = Arr::get($ids, 'ds_' . $this->ds_id);
if (!empty($ids)) {
return $query->where('d.id', 'in', array_keys($ids));
}
return $query;
}
/**
* Builds and returns
* @param Database_Query $oQuery
* @throws Lithium_Exception
*/
protected function buildWhereStatement(Database_Query $oQuery)
{
$sSql = '';
$bAddWhere = false;
$sGlue = '';
foreach ($oQuery->params() as $aParam) {
if (count($aParam) != 2) {
throw new Lithium_Exception('database.incorrect_query_params');
}
list($iType, $aParams) = $aParam;
switch ($iType) {
case Database_Query::C_AND:
$sGlue = 'AND ';
$sOperator = $this->getSqlConditionStatement($aParams);
$bAddWhere = true;
break;
case Database_Query::C_OR:
$sGlue = 'OR ';
$sOperator = $this->getSqlConditionStatement($aParams);
$bAddWhere = true;
break;
case Database_Query::C_ORDERBY:
$sOperator = sprintf('ORDER BY %s %s', $aParams[0], Database_Query::S_DESC == $aParams[1] ? 'ASC' : 'DESC');
break;
default:
throw new Lithium_Exception('database.unknown_condition_type', $iType);
}
if (!empty($sSql)) {
$sSql .= ' ' . $sGlue;
}
$sSql .= $sOperator;
}
if ($bAddWhere && !empty($sSql)) {
$sSql = ' WHERE ' . $sSql;
}
return $sSql;
}
