Add recursive helper methods for Group #408

New issue

Closed

opened 2021-03-21 18:26:38 +01:00 by nik · 6 comments

nik commented

2021-03-21 18:26:38 +01:00

Owner

Groups can be nested in arbitrary layers, so components listing groups and their subgroups need to be able to list all members recursively, or all sub-groups recursively.

PostgreSQL provides recursive CTEs.

Groups can be nested in arbitrary layers, so components listing groups and their subgroups need to be able to list all members recursively, or all sub-groups recursively. PostgreSQL provides recursive CTEs.

nik commented

2021-03-27 23:33:06 +01:00

Author

Owner

unassigned @nik

nik commented

2021-12-25 23:07:42 +01:00

Author

Owner

changed the description

nik commented

2021-12-25 23:08:08 +01:00

Author

Owner

created merge request !2497 to address this issue

nik commented

2021-12-25 23:08:08 +01:00

Author

Owner

mentioned in merge request !2497

nik commented

2021-12-26 15:49:58 +01:00

Author

Owner

 WITH recursive cte AS (
(
       SELECT "core_group_parent_groups"."to_group_id"
       FROM   "core_group_parent_groups"
       WHERE  "core_group_parent_groups"."from_group_id" = 16)
UNION ALL
          (
                     SELECT     "core_group_parent_groups"."to_group_id"
                     FROM       "core_group_parent_groups"
                     INNER JOIN "cte"
                     ON         "core_group_parent_groups"."from_group_id" = "cte"."to_group_id"))
SELECT          "core_group"."id",
                "core_group"."site_id",
                "core_group"."extended_data",
                "core_group"."school_term_id",
                "core_group"."name",
                "core_group"."short_name",
                "core_group"."group_type_id",
                "core_schoolterm"."id",
                "core_schoolterm"."site_id",
                "core_schoolterm"."extended_data",
                "core_schoolterm"."name",
                "core_schoolterm"."date_start",
                "core_schoolterm"."date_end"
FROM            "core_group"
INNER JOIN      "cte"
ON              "core_group"."id" = "cte"."to_group_id"
LEFT OUTER JOIN "core_schoolterm"
ON              (
                                "core_group"."school_term_id" = "core_schoolterm"."id")
WHERE           "core_group"."site_id" = 1
ORDER BY        "core_group"."short_name" ASC,
                "core_group"."name" ASC

```sql WITH recursive cte AS ( ( SELECT "core_group_parent_groups"."to_group_id" FROM "core_group_parent_groups" WHERE "core_group_parent_groups"."from_group_id" = 16) UNION ALL ( SELECT "core_group_parent_groups"."to_group_id" FROM "core_group_parent_groups" INNER JOIN "cte" ON "core_group_parent_groups"."from_group_id" = "cte"."to_group_id")) SELECT "core_group"."id", "core_group"."site_id", "core_group"."extended_data", "core_group"."school_term_id", "core_group"."name", "core_group"."short_name", "core_group"."group_type_id", "core_schoolterm"."id", "core_schoolterm"."site_id", "core_schoolterm"."extended_data", "core_schoolterm"."name", "core_schoolterm"."date_start", "core_schoolterm"."date_end" FROM "core_group" INNER JOIN "cte" ON "core_group"."id" = "cte"."to_group_id" LEFT OUTER JOIN "core_schoolterm" ON ( "core_group"."school_term_id" = "core_schoolterm"."id") WHERE "core_group"."site_id" = 1 ORDER BY "core_group"."short_name" ASC, "core_group"."name" ASC ```