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