/** * @return string */ public function __toString() { $conditions = $this->_where->getConditions(); if (count($conditions) === 0) { return ''; } return implode(' ', $conditions); }
public function testChecarSeOSqlRetornaCorretoComWhere() { $update = new Update(); $where = new Where(); $where->set('id', '=', 1); $update->where($where); $fields = ['name' => 'Erik']; $update->fields($fields); $sql = $update->sql(); $this->assertEquals('UPDATE `users` SET `name`=:name WHERE `id`=:id;', $sql); }
public function testSelectComWheresComOrEAnd() { $select = new Select(); $where = new Where(); $where->set('id', '=', 1); $where->set('last_name', '=', 1, 'or'); $where->set('name', '=', 'Erik', 'or'); $select->table = 'pages'; $select->where($where); $sql = $select->sql(); $this->assertEquals('SELECT * FROM pages WHERE `id`=:id and `last_name`=:last_name or `name`=:name;', $sql); }
public function prepareBindings(array $options) { if (!$options) { return []; } if (isset($options['HAVING'])) { $where = new Where($options['HAVING']); $this->bound = array_merge($this->bound, $where->getBindings()); $options['HAVING'] = $where; } return $options; }
/** * Get SQL string for statement * * @param null|PlatformInterface $platform If null, defaults to Sql92 * @return string */ public function getSqlString(PlatformInterface $platform = null) { $platform = $platform ?: $this->platform ?: new Sql92(); list($table, $schema) = $this->table->getAll(); $table = $platform->quoteIdentifier($table); if ($schema) { $table = $platform->quoteIdentifier($schema) . $platform->getIdentifierSeparator() . $table; } $data = $this->data; if (is_array($data)) { $setSql = array(); foreach ($data as $column => $value) { if ($value instanceof Expression) { $expr = $this->processExpression($value, $platform); $setSql[] = $platform->quoteIdentifier($column) . ' = ' . $expr; } elseif ($value === null) { $setSql[] = $platform->quoteIdentifier($column) . ' = NULL'; } else { $setSql[] = $platform->quoteIdentifier($column) . ' = ' . $platform->quoteValue($value); } } $data = implode(', ', $setSql); } $sql = sprintf($this->specifications[self::SPECIFICATION_UPDATE], $table, $data); if ($this->where->count() > 0) { $expr = $this->processExpression($this->where, $platform, null, 'where'); $sql .= ' ' . sprintf($this->specifications[self::SPECIFICATION_WHERE], $expr); } return $sql; }
/** * Returns the query options * * @return array query options */ public function getOptions() { $options = parent::getOptions(); $options['upsert'] = $this->upsert; $options['multiple'] = $this->multiple; return $options; }
/** * Retrieve query options. * * @return array query options. */ public function getOptions() { $conditions = parent::getOptions(); // Append remove specific options $conditions['justOne'] = $this->justOne; return $conditions; }
protected function processWhere(PlatformInterface $platform, DriverInterface $driver = null, Parameters $parameters = null) { if ($this->where->count() == 0) { return null; } $sql = $this->processExpression($this->where, $platform, $driver, $this->processInfo['paramPrefix'] . 'where', $parameters); return array($sql); }
/** * Set the WHERE clause * * @param $where * @return Delete */ public function where($where = null) { if (null !== $where) { if ($where instanceof Where) { $this->where = $where; } else { if (null === $this->where) { $this->where = (new Where($this->sql))->add($where); } else { $this->where->add($where); } } } if (null === $this->where) { $this->where = new Where($this->sql); } return $this; }
public function matches($where) { $where = Where::create($where)->toArray(); foreach ($where as $clause) { if (!$this->rowMatchesClause($clause)) { return false; } } return true; }
/** * Get the SQL string, based on the platform * Platform defaults to Sql92 if none provided * * @param null|PlatformInterface $platform * @return string */ public function getSqlString(PlatformInterface $platform = null) { $platform = $platform ?: $this->platform ?: new Sql92(); list($table, $schema) = $this->table->getAll(); $table = $platform->quoteIdentifier($table); if ($schema) { $table = $platform->quoteIdentifier($schema) . $platform->getIdentifierSeparator() . $table; } $sql = sprintf($this->specifications[self::SPECIFICATION_DELETE], $table); if ($this->where->count() > 0) { $part = $this->processExpression($this->where, $platform, null, 'where'); $sql .= ' ' . sprintf($this->specifications[self::SPECIFICATION_WHERE], $part); } return $sql; }
/** * Render the UPDATE statement * * @return string */ public function render() { // Start building the UPDATE statement $sql = 'UPDATE ' . $this->sql->quoteId($this->sql->getTable()) . ' SET '; $set = []; $paramCount = 1; $dbType = $this->sql->getDbType(); foreach ($this->columns as $column => $value) { $colValue = strpos($column, '.') !== false ? substr($column, strpos($column, '.') + 1) : $column; // Check for named parameters if (':' . $colValue == substr($value, 0, strlen(':' . $colValue)) && $dbType !== \Pop\Db\Sql::SQLITE && $dbType !== \Pop\Db\Sql::ORACLE) { if ($dbType == \Pop\Db\Sql::MYSQL || $dbType == \Pop\Db\Sql::SQLSRV) { $value = '?'; } else { if ($dbType == \Pop\Db\Sql::PGSQL && !$this->sql->getDb()->isPdo()) { $value = '$' . $paramCount; $paramCount++; } } } $val = null === $value ? 'NULL' : $this->sql->quote($value); $set[] = $this->sql->quoteId($column) . ' = ' . $val; } $sql .= implode(', ', $set); // Build any WHERE clauses if (null !== $this->where) { $sql .= ' WHERE ' . $this->where->render($paramCount); } // Build any ORDER BY clause if (null !== $this->orderBy) { $sql .= ' ORDER BY ' . $this->orderBy; } // Build any LIMIT clause if (null !== $this->limit) { $sql .= ' LIMIT ' . (int) $this->limit; } return $sql; }
$insert->fields(array('field1', 'field2'))->values(array('fvalue1', 'fvalue2')); //insert into test (field1, field2) values ('fvalue1', 'fvalue2'), ('fvalue11', 'fvalue22') ; $insert->values(array('fvalue11', 'fvalue22')); //附加 前面的 //2.2.2 关联数组插入模式 //insert into test (field1, field2) values ('fvalue1', 'fvalue2'), ('fvalue11', 'fvalue22') ; $insert->kvInsert(array('field1' => 'fvalue1', 'field2' => 'fvalue2'))->values(array('fvalue11', 'fvalue22')); //2.2.3 Duplicate Key Update模式 //需要注意dupFields和dupValues的关联性,弱耦合 $insert->enableDuplicate(true); //开启自动Dup更新模式 //insert into test set field1='fvalue1', field2='fvalue2' on duplicate key update field1='newfvalue1', num=num+1 ; $insert->fields(array('field1', 'field2'))->dupFields(array('field1', 'num'))->values(array('fvalue1', 'fvalue2'), array('field2' => 'newfvalue1', array('plain' => 'num=num+1'))); //2.2.4 通过row对象数组或rowset批量插入 $insert->batchSaveRows(); //2.3 更新部分 //获取更新对象 //update tableTest set field1='fvalue1', num=num+1 where (id='16') ; $update = \HuiLib\Db\Query::update('tableTest'); //支持两种更新Set模式,KV模式和Plain模式 $update->sets(array('field1' => 'fvalue1', 'num' => array('plain' => 'num=num+1'))); //绑定条件 $update->where(Where::createPair('id', '16')); //2.4 删除部分 //获取删除对象 //delete from tableTest where (id='2') limit 10 ; $delete = \HuiLib\Db\Query::delete('tableTest'); //设置删除条件 $delete->where(Where::createPair('id', '2')); //设置删除行数 $delete->limit(10);
private function whereToString(\b2\Quote $quote) { return $this->where->toString($quote); }
public function Get() { if (!empty($this->queryString)) { return $this->queryString; } $this->type = strtolower($this->type); $result = $this->type . ' '; if (!empty($this->queryMod)) { $result .= $this->queryMod . ' '; } $table_result = ''; if (is_array($this->tables)) { foreach ($this->tables as $table) { $table_result .= $table . ', '; } $table_result = substr($table_result, 0, strlen($table_result) - 2); } else { $table_result = $this->tables; } switch ($this->type) { case 'select': $field_result = ''; if (is_array($this->fields)) { foreach ($this->fields as $field) { $field_result .= $field . ', '; } $field_result = substr($field_result, 0, strlen($field_result) - 2); } else { $field_result = $this->fields; } if ($this->calcRows) { $result .= 'SQL_CALC_FOUND_ROWS '; } $result .= $field_result; if (!empty($table_result)) { $result .= ' from ' . $table_result; } break; case 'delete': $result .= ' from ' . $table_result; break; case 'insert': $result .= ' into '; $result .= $table_result . ' ('; $field_result = ''; if (is_array($this->fields)) { foreach ($this->fields as $field) { $field_result .= $field . ', '; } $field_result = substr($field_result, 0, strlen($field_result) - 2); } else { $field_result = $this->fields; } $result .= $field_result . ' ) values ( '; if (is_array($this->values)) { for ($i = 0; $i < count($this->values); $i++) { if (strpos($this->values[$i], 'inline:') === false) { $result .= "'" . addslashes($this->values[$i]) . "', "; } else { $this->values[$i] = preg_replace('/^inline:(.*)/', '\\1', $this->values[$i]); $result .= $this->values[$i] . ", "; } } $result = substr($result, 0, strlen($result) - 2); } $result .= ' )'; break; case 'update': $result .= $table_result . ' set '; if (is_array($this->fields)) { for ($i = 0; $i < count($this->fields); $i++) { if (strpos($this->values[$i], 'inline:') === false) { //$result .= $this->fields[$i]." = '".addslashes($this->values[$i])."', "; $result .= $this->fields[$i] . " = :" . $this->fields[$i] . ", "; } else { $this->values[$i] = preg_replace('/^inline:(.*)/', '\\1', $this->values[$i]); $result .= $this->fields[$i] . " = " . $this->values[$i] . ", "; } } $result = substr($result, 0, strlen($result) - 2); } break; } if (!$this->where->isEmpty()) { if ($this->policy) { $this->where->policy = $this->policy; } $result .= ' where ' . $this->where->get(); $this->_params = array_merge($this->_params, $this->where->getParams()); } if (!empty($this->groupBy)) { $result .= ' group by ' . $this->groupBy; } if (!empty($this->having)) { $result .= ' having ' . $this->having; } if (!empty($this->orderBy)) { $result .= ' order by ' . $this->orderBy; } if (!empty($this->limit)) { $result .= ' limit ' . $this->limit; } $first_union = true; foreach ($this->union as $union) { if ($first_union && empty($field_result) && empty($table_result)) { $result = $union->get(); } else { if ($this->unionAllFlag) { $result .= ' UNION ALL ' . $union->Get(); } else { $result .= ' union ' . $union->Get(); } } $first_union = false; } return $result; }
function execute() { $platform = $this->getPlatform(); return $platform->bitwiseOr() . parent::execute(); }
function __construct($mainTable = null, $quoteCharacter = '"', $tablePrefix = '') { parent::__construct($mainTable, $quoteCharacter, $tablePrefix); }
/** * Generate the query. * * @return string */ public function __toString() { $out = 'UPDATE'; if (null !== $this->_or) { $out .= ' OR ' . $this->_or; } $out .= ' ' . $this->enclose($this->_table); $set = []; foreach ($this->_set as $name => $value) { $set[] = $this->enclose($name) . ' = ' . $value; } $out .= ' SET ' . implode(', ', $set); return $out . parent::__toString(); }
/** * (non-PHPdoc) * @see \Koldy\Db\Select::getQuery() */ protected function getQuery() { if ($this->searchTerm !== null) { // there is search term set, so we'll need to include this to where statements // but, there might be already some where statements in the query, so we'll create // new Where instance and we'll add that Where block with AND operator $where = Where::init(); if ($this->searchFields !== null) { foreach ($this->searchFields as $field) { $where->orWhereLike($field, "%{$this->searchTerm}%"); } } else { foreach ($this->getFields() as $field) { $where->orWhereLike($field['name'], "%{$this->searchTerm}%"); } } $this->where($where); } return parent::getQuery(); }
function __construct($mainTable = null, $quoteCharacter = '"', $tablePrefix = '', $execCallback = null, $dbType = null) { parent::__construct($mainTable, $quoteCharacter, $tablePrefix, $execCallback, $dbType); }
/** * Generate the query. * * @return string */ public function __toString() { return 'DELETE FROM ' . $this->enclose($this->_from) . parent::__toString(); }
/** * Generate the query. * * @return string */ public function __toString() { $out = 'SELECT'; if (null !== $this->_distinctOrAll) { $out .= ' ' . $this->_distinctOrAll; } if (!empty($this->_columns)) { $out .= ' ' . implode(', ', $this->enclose($this->_columns)); } else { $out .= ' *'; } if (!empty($this->_from)) { $out .= ' FROM '; $handle = []; foreach ($this->_from as $alias => $from) { if (is_int($alias)) { $handle[] = $this->enclose($from); } else { $handle[] = $this->enclose($from) . ' AS ' . $this->enclose($alias); } } $out .= implode(', ', $handle); } $out .= parent::__toString(); if (!empty($this->_groupBy)) { $out .= ' GROUP BY ' . implode(', ', $this->enclose($this->_groupBy)); if (!empty($this->_having)) { $out .= ' HAVING ' . $this->_having; } } return $out; }