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