public function testWhere() { $select = new Varien_Db_Select($this->_getAdapterMockWithMockedQuote(1, "'5'")); $select->from('test')->where('field = ?', 5); $this->assertEquals("SELECT `test`.* FROM `test` WHERE (field = '5')", $select->assemble()); $select = new Varien_Db_Select($this->_getAdapterMockWithMockedQuote(1, "''")); $select->from('test')->where('field = ?'); $this->assertEquals("SELECT `test`.* FROM `test` WHERE (field = '')", $select->assemble()); $select = new Varien_Db_Select($this->_getAdapterMockWithMockedQuote(1, "'%?%'")); $select->from('test')->where('field LIKE ?', '%value?%'); $this->assertEquals("SELECT `test`.* FROM `test` WHERE (field LIKE '%?%')", $select->assemble()); $select = new Varien_Db_Select($this->_getAdapterMockWithMockedQuote(0)); $select->from('test')->where("field LIKE '%value?%'", null, Varien_Db_Select::TYPE_CONDITION); $this->assertEquals("SELECT `test`.* FROM `test` WHERE (field LIKE '%value?%')", $select->assemble()); $select = new Varien_Db_Select($this->_getAdapterMockWithMockedQuote(1, "'1', '2', '4', '8'")); $select->from('test')->where("id IN (?)", array(1, 2, 4, 8)); $this->assertEquals("SELECT `test`.* FROM `test` WHERE (id IN ('1', '2', '4', '8'))", $select->assemble()); }
/** * Get insert from Select object query * * @param Varien_Db_Select $select * @param string $table insert into table * @param array $fields * @param bool|int $mode * @return string */ public function insertFromSelect(Varien_Db_Select $select, $table, array $fields = array(), $mode = false) { $query = 'INSERT'; if ($mode == self::INSERT_IGNORE) { $query .= ' IGNORE'; } $query = sprintf('%s INTO %s', $query, $this->quoteIdentifier($table)); if ($fields) { $columns = array_map(array($this, 'quoteIdentifier'), $fields); $query = sprintf('%s (%s)', $query, join(', ', $columns)); } $query = sprintf('%s %s', $query, $select->assemble()); if ($mode == self::INSERT_ON_DUPLICATE) { if (!$fields) { $describe = $this->describeTable($table); foreach ($describe as $column) { if ($column['PRIMARY'] === false) { $fields[] = $column['COLUMN_NAME']; } } } $update = array(); foreach ($fields as $k => $v) { $field = $value = null; if (!is_numeric($k)) { $field = $this->quoteIdentifier($k); if ($v instanceof Zend_Db_Expr) { $value = $v->__toString(); } elseif (is_string($v)) { $value = sprintf('VALUES(%s)', $this->quoteIdentifier($v)); } elseif (is_numeric($v)) { $value = $this->quoteInto('?', $v); } } elseif (is_string($v)) { $value = sprintf('VALUES(%s)', $this->quoteIdentifier($v)); $field = $this->quoteIdentifier($v); } if ($field && $value) { $update[] = sprintf('%s = %s', $field, $value); } } if ($update) { $query = sprintf('%s ON DUPLICATE KEY UPDATE %s', $query, join(', ', $update)); } } return $query; }
/** * Add EXISTS clause * * @param Varien_Db_Select $select * @param string $joinCondition * @param bool $isExists * @return Varien_Db_Select */ public function exists($select, $joinCondition, $isExists = true) { if ($isExists) { $exists = 'EXISTS (%s)'; } else { $exists = 'NOT EXISTS (%s)'; } $select->reset(self::COLUMNS)->columns(array(new Zend_Db_Expr('1')))->where($joinCondition); $exists = sprintf($exists, $select->assemble()); $this->where($exists); return $this; }
/** * Returns select query with analytic functions * * @param Varien_Db_Select $select * @return string */ public function getQueryUsingAnalyticFunction(Varien_Db_Select $select) { return $select->assemble(); }
/** * Get insert from Select object query * * @param Varien_Db_Select $select * @param string $table insert into table * @param array $fields * @param int $mode * @return string */ public function insertFromSelect(Varien_Db_Select $select, $table, array $fields = array(), $mode = false) { $query = 'INSERT'; if ($mode == self::INSERT_IGNORE) { $query .= ' IGNORE'; } $query = sprintf('%s INTO %s', $query, $this->quoteIdentifier($table)); if ($fields) { $columns = array_map(array($this, 'quoteIdentifier'), $fields); $query = sprintf('%s (%s)', $query, join(', ', $columns)); } $query = sprintf('%s %s', $query, $select->assemble()); if ($mode == self::INSERT_ON_DUPLICATE) { if (!$fields) { $describe = $this->describeTable($table); foreach ($describe as $column) { if ($column['PRIMARY'] === false) { $fields[] = $column['COLUMN_NAME']; } } } $update = array(); foreach ($fields as $field) { $update[] = sprintf('%1$s = VALUES(%1$s)', $this->quoteIdentifier($field)); } if ($update) { $query = sprintf('%s ON DUPLICATE KEY UPDATE %s', $query, join(', ', $update)); } } return $query; }
/** * (non-PHPdoc) * @see Zend_Db_Select::assemble() */ public function assemble() { $sql = parent::assemble(); $bindings = $this->_binding; $select = $this; $regex = '/{([a-z_]+)}/i'; $cb = function ($match) use(&$cb, $regex, $bindings, $sql, $select) { if (isset($bindings[$match[1]])) { return preg_replace_callback($regex, $cb, $bindings[$match[1]]); } $exception = new Mzax_Db_Select_Exception("Binding '{$match[1]}' does not exist", 1001, $select); $exception->sql = $sql; throw $exception; }; // replace all binding placeholders return preg_replace_callback($regex, $cb, $sql); }
/** * (non-PHPdoc) * @see Zend_Db_Select::assemble() */ public function assemble() { $sql = parent::assemble(); $bindings = $this->_binding; $regex = '/{([a-z_]+)}/i'; $cb = function ($match) use(&$cb, $regex, $bindings) { if (isset($bindings[$match[1]])) { return preg_replace_callback($regex, $cb, $bindings[$match[1]]); } throw new Exception("Binding '{$match[1]}' does not exist"); }; // replace all binding placeholders return preg_replace_callback($regex, $cb, $sql); }