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