/**
* Returns an SQLSelectClause object that builds a query for retrieving the
* subtopics of the given parent.
*
* @param integer $p_parentId - parent topic identifier
* @param integer $p_depth - depth of the subtopic tree; default 1; 0 for unlimitted
* @param integer $p_indent - query formatting: indent the query $p_indent times
* @return SQLSelectClause
*/
public static function BuildSubtopicsQuery($p_parentId = 0, $p_depth = 1, $p_indent = 0)
{
$topicObj = new Topic();
$depthGreater = $p_parentId > 0 ? 'depth > 0' : 'depth >= 0';
$depthMax = $p_parentId > 0 ? (int) $p_depth : $p_depth - 1;
$query = new SQLSelectClause($p_indent);
$query->addColumn('node.id');
$query->setTable($topicObj->m_dbTableName . ' as node');
$query->addTableFrom($topicObj->m_dbTableName . ' as parent');
if ($p_parentId > 0) {
$query->addColumn('(COUNT(parent.id) - (sub_tree.depth + 1)) AS depth');
$query->addTableFrom($topicObj->m_dbTableName . ' as sub_parent');
$parentDepthQuery = self::BuildDepthQuery($p_parentId, $p_indent + 1);
$query->addTableFrom('(' . $parentDepthQuery->buildQuery() . ') as sub_tree');
$query->addWhere('sub_parent.id = sub_tree.id');
$query->addWhere('node.node_left BETWEEN sub_parent.node_left AND sub_parent.node_right');
} else {
$query->addColumn('(COUNT(parent.id) - 1) AS depth');
}
$query->addWhere('node.node_left BETWEEN parent.node_left AND parent.node_right');
$query->addGroupField('node.id');
if ($p_depth < 1) {
$query->addHaving($depthGreater);
} else {
$query->addHaving($depthGreater);
$query->addHaving('depth <= ' . $depthMax);
}
$query->addOrderBy('node.node_left');
return $query;
}