/** * Count sql rows * @param $matches * @param $distinct * @return int */ private function _getCount($matches = null, $distinct = false) { //initialize table gateways of not initialized if (!$this->isInitialized) { $this->initialize(); } //build count expression $expression = new Expression(); $expression->setExpression("COUNT(" . ($distinct ? ' DISTINCT ' : '') . "?)"); if (empty($matches)) { $expression->setParameters('*'); $expression->setTypes(array(Expression::TYPE_LITERAL)); } else { $field = $this->__normalizeKeys($matches['fields']); $expression->setParameters($field); $expression->setTypes(array(Expression::TYPE_IDENTIFIER)); } $select = $this->sql->select(); //set columns to just the count expression $select->columns(array('count' => $expression), false); $statement = $this->sql->prepareStatementForSqlObject($select); //execute the statement and return the result $result = $statement->execute()->current(); return $result['count']; }
public function testGetExpressionDataWillEscapePercent() { $expression = new Expression('X LIKE "foo%"'); $this->assertEquals(array(array('X LIKE "foo%%"', array(), array())), $expression->getExpressionData()); }
/** * @param $parentId * @param bool $immediate * @return Select */ public function getDecendentsByParentId($parentId, $immediate = true) { $subTree = $this->getSql()->select()->from(['child' => $this->getTable()])->columns([$this->primary, 'depth' => new Expression('(COUNT(parent.' . $this->getPrimaryKey() . ') - 1)')])->join(['parent' => $this->getTable()], 'child.' . self::COLUMN_LEFT . ' BETWEEN parent.' . self::COLUMN_LEFT . ' AND parent.' . self::COLUMN_RIGHT, [], Select::JOIN_INNER)->where(['child.' . $this->getPrimaryKey() . ' = ?' => $parentId])->group('child.' . $this->getPrimaryKey())->order('child.' . self::COLUMN_LEFT); $subTreeDepth = 'subTree.depth + 1'; if ($this->isMysql57Compatible()) { $subTreeDepth = 'ANY_VALUE(' . $subTreeDepth . ')'; } $depth = new Expression('(COUNT(parent.' . $this->getPrimaryKey() . ') - (' . $subTreeDepth . '))'); $select = $this->getSql()->select()->from(['child' => $this->getTable()])->columns([Select::SQL_STAR, 'depth' => $depth])->join(['parent' => $this->getTable()], 'child.' . self::COLUMN_LEFT . ' BETWEEN parent.' . self::COLUMN_LEFT . ' AND parent.' . self::COLUMN_RIGHT, [], Select::JOIN_INNER)->join(['subParent' => $this->getTable()], 'child.' . self::COLUMN_LEFT . ' BETWEEN subParent.' . self::COLUMN_LEFT . ' AND subParent.' . self::COLUMN_RIGHT, [], Select::JOIN_INNER)->join(['subTree' => $subTree], 'subParent.' . $this->getPrimaryKey() . ' = subTree.' . $this->getPrimaryKey(), [], Select::JOIN_INNER)->group('child.' . $this->getPrimaryKey())->order('child.' . self::COLUMN_LEFT); if (true === $immediate) { // Hack for sqlite as having does not work otherwise. if ('SQLite' === $this->getAdapter()->getPlatform()->getName()) { $select->having($depth->getExpression() . ' = 1'); } else { $select->having('depth = 1'); } } return $select; }
/** * Cast expression * * @param mixed $value * @param string $cast * @param bool $escapeCast * @param string $type */ public function __construct($value, $cast, $escapeCast = false, $type = self::TYPE_VALUE) { $parameters = array($value); $types = array($type); $expression = sprintf('CAST(%s AS %s)', self::PLACEHOLDER, $escapeCast ? self::PLACEHOLDER : $cast); if ($escapeCast) { $parameters[] = $cast; $types[] = self::TYPE_IDENTIFIER; } parent::__construct($expression, $parameters, $types); }
/** * @covers Zend\Db\Sql\Expression::getExpressionData */ public function testGetExpressionData() { $expression = new Expression( 'X SAME AS ? AND Y = ? BUT LITERALLY ?', array('foo', 5, 'FUNC(FF%X)'), array(Expression::TYPE_IDENTIFIER, Expression::TYPE_VALUE, Expression::TYPE_LITERAL) ); $this->assertEquals( array(array( 'X SAME AS %s AND Y = %s BUT LITERALLY %s', array('foo', 5, 'FUNC(FF%X)'), array(Expression::TYPE_IDENTIFIER, Expression::TYPE_VALUE, Expression::TYPE_LITERAL) )), $expression->getExpressionData() ); }
/** * @param string $expression * @param string|array $parameters * @param array $types */ public function __construct($expression = '', $parameters = null, array $types = array()) { \Zend\Db\Sql\Expression::__construct($expression, $parameters, $types); }
/** * Function call expression * * @param string $name * @param array $arguments * @param array $types */ public function __construct($name = null, array $arguments = array(), array $types = array()) { list($expression, $parameters, $types) = $this->getExpressionParametersTypes($name, $arguments, $types); parent::__construct($expression, $parameters, $types); }
/** * Array literal expression * * @param array $values * @param array $types */ public function __construct(array $values = array(), array $types = array()) { list($expression, $parameters, $types) = $this->getExpressionParametersTypes($values, $types); parent::__construct($expression, $parameters, $types); }