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