Question:
In the below query i want the equivalent for
GROUP_CONCAT(ctAncestors.ancestor ORDER BY ctAncestors.depth DESC) AS breadcrumbs_id
in firebird.
So far I got
LIST(ctAncestors.ancestor, ',') AS breadcrumbs_id
but I cannot figure out how to add an order by in the list directive.
How can I do a order by in the List aggregate ?
-- http://www.unlimitedtricks.com/sorting-a-subtree-in-a-closure-table-hierarchical-data-structure/
-- DECLARE @__in_rootnodeId AS bigint
-- SET @__in_rootnodeId = 8
-- SET @__in_rootnodeId = 1
SELECT
COALESCE(ctDescendants.ancestor, 0) AS parent_id
,ctAncestors.descendant AS child_id
,tClosureItemsTable.COM_Id
,tClosureItemsTable.COM_Text
--,'ab' as breadcrumbs
--,LIST(ctAncestors.ancestor ORDER BY ctAncestors.depth DESC) AS breadcrumbs_id
,LIST(ctAncestors.ancestor, ',') AS breadcrumbs_id
--,GROUP_CONCAT(ctAncestors.ancestor ORDER BY ctAncestors.depth DESC) AS breadcrumbs_id
-- ,GROUP_CONCAT(breadcrumb_data.COM_Text ORDER BY ctAncestors.depth DESC) AS breadcrumbs
--,GROUP_CONCAT(breadcrumb_data.COM_Text ORDER BY ctAncestors.depth) AS breadcrumbs
,LIST(breadcrumb_data.COM_Text) AS breadcrumbs
--,GROUP_CONCAT(breadcrumb_data.COM_Text ORDER BY ctAncestors.depth DESC SEPARATOR '-') AS breadcrumbs2
/*
,
SUBSTRING
(
(
SELECT
-- breadcrumb.ancestor AS 'text()' -- Remove substring for this
-- ', ' + CAST(breadcrumb.ancestor AS nvarchar(36)) AS 'text()'
', ' + CAST(breadcrumb_data.comment AS nvarchar(36)) AS 'text()'
FROM T_CommentClosure AS breadcrumb
LEFT JOIN Comments AS breadcrumb_data
ON breadcrumb_data.COM_Id = breadcrumb.ancestor
WHERE (breadcrumb.descendant = ctAncestors.descendant)
ORDER BY breadcrumb.depth DESC
FOR XML PATH('')
)
,2
,8000
) AS breadcrumbs
*/
,
(
SELECT COUNT(*) FROM T_CommentClosure AS tp
WHERE tp.ancestor = tClosureItemsTable.COM_Id AND tp.depth = 1
) AS ChildCount
FROM T_CommentClosure AS ctAncestors
-- Must be left join, for root node
LEFT JOIN T_CommentClosure AS ctDescendants
ON (ctDescendants.descendant = ctAncestors.descendant)
AND (ctDescendants.depth = 1)
-- INNER JOIN just in case item has been somehow deleted when FK disabled
INNER JOIN T_Comments AS tClosureItemsTable
ON (ctAncestors.descendant = tClosureItemsTable.COM_Id)
INNER JOIN T_Comments AS breadcrumb_data
ON breadcrumb_data.COM_Id = ctAncestors.ancestor
WHERE (1=1)
-- AND (ctAncestors.ancestor = @__in_rootnodeId) -- ROOT node id
AND
(
-- ( ctAncestors.ancestor = @__in_rootnodeId) -- ROOT node id
(1=2)
OR
(1=1)
-- (@__in_rootnodeId IS NULL)
)
-- AND tClosureItemsTable.active = 1
GROUP BY
ctAncestors.descendant
,ctDescendants.ancestor
,tClosureItemsTable.COM_Id
,tClosureItemsTable.COM_Text
ORDER BY breadcrumbs ASC -- DESC
Best Answer
As the documentation of
LIST()
states:That said, if you use a subquery or common table expression (CTE) that first orders by the
GROUP BY
-columns and this column then it will work, however this does mean that you are depending on an implementation artefact which might change with point releases or new versions.There is an improvement ticket (CORE-2332) in the Firebird tracker for this, but it hasn't been planned for a version. This ticket also contains an example of the workaround: