/**
* @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;
}
/**
* @covers Zend\Db\Sql\Expression::getExpression
* @depends testSetExpression
*/
public function testGetExpression(Expression $expression)
{
$this->assertEquals('Foo Bar', $expression->getExpression());
}
