PHP Zend_Db_Expr类代码示例

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

 /**
  * @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;
 }

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

 /**
  * Instantiate teh current timestamp expression.
  */
 public function __construct()
 {
     parent::__construct('CURRENT_TIMESTAMP');
 }

 /**
  * @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));
 }