/** * Quote an identifier and an optional alias. * * @param string|array|Zend_Db_Expr $ident The identifier or expression. * @param string $alias An optional alias. * @param boolean $auto If true, heed the AUTO_QUOTE_IDENTIFIERS config option. * @param string $as The string to add between the identifier/expression and the alias. * @return string The quoted identifier and alias. */ protected function _quoteIdentifierAs($ident, $alias = null, $auto = false, $as = ' AS ') { if ($ident instanceof Zend_Db_Expr) { $quoted = $ident->__toString(); } elseif ($ident instanceof Zend_Db_Select) { $quoted = '(' . $ident->assemble() . ')'; } else { if (is_string($ident)) { $ident = explode('.', $ident); } if (is_array($ident)) { $segments = array(); foreach ($ident as $segment) { if ($segment instanceof Zend_Db_Expr) { $segments[] = $segment->__toString(); } else { $segments[] = $this->_quoteIdentifier($segment, $auto); } } if ($alias !== null && end($ident) == $alias) { $alias = null; } $quoted = implode('.', $segments); } else { $quoted = $this->_quoteIdentifier($ident, $auto); } } if ($alias !== null) { $quoted .= $as . $this->_quoteIdentifier($alias, $auto); } return $quoted; }
/** * Adds a FROM table and optional columns to the query. * * The table name can be expressed * * @param array|string|Zend_Db_Expr|Zend_Db_Table_Abstract $name The table name or an associative array relating table name to correlation name. * @param array|string|Zend_Db_Expr $cols The columns to select from this table. * @param string $schema The schema name to specify, if any. * @return Zend_Db_Table_Select This Zend_Db_Table_Select object. */ public function from($name, $cols = self::SQL_WILDCARD, $schema = null) { if ($name instanceof Zend_Db_Table_Abstract) { $info = $name->info(); $name = $info[Zend_Db_Table_Abstract::NAME]; if (isset($info[Zend_Db_Table_Abstract::SCHEMA])) { $schema = $info[Zend_Db_Table_Abstract::SCHEMA]; } } return $this->joinInner($name, null, $cols, $schema); }
/** * Adds a row order to the query. * * @param mixed $spec The column(s) and direction to order by. * @return Zend_Db_Select This Zend_Db_Select object. */ public function order($spec) { if (!is_array($spec)) { $spec = array($spec); } // force 'ASC' or 'DESC' on each order spec, default is ASC. foreach ($spec as $val) { if (preg_match('/\\(.*\\)/', $val)) { $val = new Zend_Db_Expr($val); } if ($val instanceof Zend_Db_Expr) { $expr = $val->__toString(); if (empty($expr)) { continue; } $this->_parts[self::ORDER][] = $val; } else { if (empty($val)) { continue; } $direction = 'ASC'; if (preg_match('/(.*)\\s+(ASC|DESC)\\s*$/i', $val, $matches)) { $val = trim($matches[1]); $direction = $matches[2]; } $this->_parts[self::ORDER][] = array(trim($val), $direction); } } return $this; }
/** * Строит sql CASE WHEN .. THEN .. ELSE .. END для секции SELECT * т.е. на основаниие весов атрибутов строит части запроса для вычесления релевантности. * * @param string $query оригинальный запрос * @param array $arQuery подготовленный запрос * @param array $attributes атрибуты с весом * * @return string */ protected function _getCaseCondition($query, $arQuery, $attributes) { $uid = Mage::helper('mstcore/debug')->start(); $select = ''; $cases = array(); $fullCases = array(); $words = Mage::helper('core/string')->splitWords($query, true); foreach ($attributes as $attr => $weight) { if ($weight == 0) { continue; } $cases[$weight * 4][] = $this->getCILike('s.' . $attr, $query); $cases[$weight * 3][] = $this->getCILike('s.' . $attr, ' ' . $query . ' ', array('position' => 'any')); } foreach ($words as $word) { foreach ($attributes as $attr => $weight) { $w = intval($weight / count($arQuery)); if ($w == 0) { continue; } $cases[$w][] = $this->getCILike('s.' . $attr, $word, array('position' => 'any')); $cases[$w + 1][] = $this->getCILike('s.' . $attr, ' ' . $word . ' ', array('position' => 'any')); } } foreach ($words as $word) { foreach ($attributes as $attr => $weight) { $w = intval($weight / count($arQuery)); if ($w == 0) { continue; } // $locate = new Zend_Db_Expr('LOCATE("'.$word.'", s.'.$attr.')'); // $cases[$w.'-'.$locate->__toString()][] = $locate; $locate = new Zend_Db_Expr('(LENGTH(s.' . $attr . ') - LOCATE("' . addslashes($word) . '", s.' . $attr . ')) / LENGTH(s.' . $attr . ')'); $cases[$w . '*' . $locate->__toString()][] = $locate; } } foreach ($cases as $weight => $conds) { foreach ($conds as $cond) { $fullCases[] = 'CASE WHEN ' . $cond . ' THEN ' . $weight . ' ELSE 0 END'; } } if (count($fullCases)) { $select = '(' . implode('+', $fullCases) . ')'; } else { $select = new Zend_Db_Expr('0'); } Mage::helper('mstcore/debug')->end($uid, (string) $select); return $select; }
/** * process Or combination on collection. * * @param $collection * @param $conditions * @param $type * * @return mixed */ public function processOrCombination($collection, $conditions, $type) { $fieldsConditions = []; $multiFieldsConditions = []; foreach ($conditions as $condition) { $attribute = $condition['attribute']; $cond = $condition['conditions']; $value = $condition['cvalue']; //ignore condition if value is null or empty if ($value == '' or $value == null) { continue; } if ($type == self::REVIEW && isset($this->attributeMapForQuote[$attribute])) { $attribute = $this->attributeMapForOrder[$attribute]; } elseif ($type == self::ABANDONED && isset($this->attributeMapForOrder[$attribute])) { $attribute = $this->attributeMapForQuote[$attribute]; } else { $this->productAttribute[] = $condition; continue; } if ($cond == 'null') { if ($value == '1') { if (isset($fieldsConditions[$attribute])) { $multiFieldsConditions[$attribute] = ['notnull' => true]; continue; } $fieldsConditions[$attribute] = ['notnull' => true]; } elseif ($value == '0') { if (isset($fieldsConditions[$attribute])) { $multiFieldsConditions[$attribute] = [$cond => true]; continue; } $fieldsConditions[$attribute] = [$cond => true]; } } else { if ($cond == 'like' or $cond == 'nlike') { $value = '%' . $value . '%'; } if (isset($fieldsConditions[$attribute])) { $multiFieldsConditions[$attribute] = [$this->conditionMap[$cond] => $value]; continue; } $fieldsConditions[$attribute] = [$this->conditionMap[$cond] => $value]; } } //all rules condition will be with or combination if (!empty($fieldsConditions)) { $column = []; $cond = []; foreach ($fieldsConditions as $key => $fieldsCondition) { $exp = new \Zend_Db_Expr($key); $column[] = $exp->__toString(); $cond[] = $fieldsCondition; } if (!empty($multiFieldsConditions)) { foreach ($multiFieldsConditions as $key => $multiFieldsCondition) { if (in_array($key, $column)) { $exp = new \Zend_Db_Expr($key); $column[] = $exp->__toString(); $cond[] = $multiFieldsCondition; continue; } } } $collection->addFieldToFilter($column, $cond); } return $this->_processProductAttributes($collection); }
/** * Instantiate teh current timestamp expression. */ public function __construct() { parent::__construct('CURRENT_TIMESTAMP'); }
/** * @param array $columns * @param string $query * @return string */ public function getScoreQuery($columns, $query) { $cases = []; $fullCases = []; $words = preg_split('#\\s#siu', $query, null, PREG_SPLIT_NO_EMPTY); foreach ($columns as $column) { $cases[5][] = $this->dbHelper->getCILike($column, ' ' . $query . ' '); } foreach ($words as $word) { foreach ($columns as $column) { $cases[3][] = $this->dbHelper->getCILike($column, ' ' . $word . ' ', ['position' => 'any']); $cases[2][] = $this->dbHelper->getCILike($column, $word, ['position' => 'any']); } } foreach ($words as $word) { foreach ($columns as $column) { $e = '(LENGTH(' . $column . ')'; $e .= '- LOCATE("' . addslashes($word) . '", ' . $column . ')) / LENGTH(' . $column . ')'; $locate = new \Zend_Db_Expr($e); $cases[$locate->__toString()][] = $locate; } } foreach ($cases as $weight => $conditions) { foreach ($conditions as $condition) { $fullCases[] = 'CASE WHEN ' . $condition . ' THEN ' . $weight . ' ELSE 0 END'; } } if (count($fullCases)) { $select = '(' . implode('+', $fullCases) . ')'; } else { $select = '0'; } return $select; }
/** * Instantiate teh current timestamp expression. */ public function __construct() { parent::__construct('CURRENT_DATE'); }
/** * @param string $string * @param string|null $alias * @param string $expectedResult * @dataProvider getExpressionToQuoteDataProvider */ public function testQuoteTableAsWithZendDbExpr($string, $alias, $expectedResult) { $this->zendDbExprMock->expects($this->once())->method('__toString')->willReturn($string); $this->assertEquals($expectedResult, $this->model->quoteTableAs($this->zendDbExprMock, $alias)); }
/** * Quote an identifier and an optional alias. * * @param string|array|Zend_Db_Expr $ident The identifier or expression. * @param string $alias An optional alias. * @param string $as The string to add between the identifier/expression and the alias. * @return string The quoted identifier and alias. */ protected function _quoteIdentifierAs($ident, $alias = null, $as = ' AS ') { $q = $this->getQuoteIdentifierSymbol(); if ($ident instanceof Zend_Db_Expr) { $quoted = $ident->__toString(); } else { if (is_string($ident)) { $ident = explode('.', $ident); } if (is_array($ident)) { $segments = array(); foreach ($ident as $segment) { if ($segment instanceof Zend_Db_Expr) { $segments[] = $segment->__toString(); } else { $segments[] = $q . str_replace("{$q}", "{$q}{$q}", $segment) . $q; } } if ($alias !== null && end($ident) == $alias) { $alias = null; } $quoted = implode('.', $segments); } else { $quoted = $q . str_replace("{$q}", "{$q}{$q}", $ident) . $q; } } if ($alias !== null) { $quoted .= $as . $q . str_replace("{$q}", "{$q}{$q}", $alias) . $q; } return $quoted; }
/** * Quotes an identifier. * * @param string|Zend_Db_Expr $ident The identifier. * @return string The quoted identifier. */ public function quoteIdentifier($ident) { if ($ident instanceof Zend_Db_Expr) { return $ident->__toString(); } $q = $this->getQuoteIdentifierSymbol(); $ident = str_replace("{$q}", "{$q}{$q}", $ident); return $q . $ident . $q; }
/** * Adds a FROM table and optional columns to the query. * * The table name can be expressed * * @param array|string|Zend_Db_Expr|Zend_Db_Table_Abstract $name The table name or an associative array relating table name to correlation name. * @param array|string|Zend_Db_Expr $cols The columns to select from this table. * @param string $schema The schema name to specify, if any. * @return Zend_Db_Table_Select This Zend_Db_Table_Select object. */ public function from($name, $cols = '*', $schema = null) { if ($name instanceof Zend_Db_Table_Abstract) { $info = $name->info(); $name = $info[Zend_Db_Table_Abstract::NAME]; } return $this->joinInner($name, null, $cols, $schema); }
/** * Adds a FROM table and optional columns to the query. * * The table name can be expressed * * @param array|string|Zend_Db_Expr|Kwf_Db_Table $name The table name or an associative array relating table name to correlation name. * @param array|string|Zend_Db_Expr $cols The columns to select from this table. * @param string $schema The schema name to specify, if any. * @return Kwf_Db_Table_Select This Kwf_Db_Table_Select object. */ public function from($name, $cols = self::SQL_WILDCARD, $schema = null) { if ($name instanceof Kwf_Db_Table) { $schema = $name->getTableName(); $schema = $name->getSchemaName(); } return $this->joinInner($name, null, $cols, $schema); }