Gibt es bereits eine SQL-Abfrage, um mit dem Data Explorer-Plugin die eindeutigen Berechtigungen für alle Unterkategorien einer übergeordneten Kategorie im Vergleich zu den Berechtigungen der übergeordneten Kategorie selbst zu extrahieren?
This SQL shows all permissions in subcategories that are not in the parent categories.
SELECT subcategories.* FROM (
SELECT category.parent_category_id
, category.id category_id
, category.name category_name
, category.position category_position
, category_group.permission_type permission_type
, groups.name group_name
, groups.id group_id
FROM categories category
INNER JOIN category_groups category_group
ON category_group.category_id = category.id
INNER JOIN GROUPS
ON groups.id = category_group.group_id
WHERE parent_category_id IS NOT NULL
) subcategories
LEFT JOIN (
SELECT category.id category_id
, category.name category_name
, category.position category_position
, category_group.permission_type permission_type
, groups.name group_name
, groups.id group_id
FROM categories category
INNER JOIN category_groups category_group
ON category_group.category_id = category.id
INNER JOIN GROUPS
ON groups.id = category_group.group_id
WHERE parent_category_id IS NULL
) categories
ON categories.category_id = subcategories.parent_category_id
AND categories.group_id = subcategories.group_id
AND categories.permission_type = subcategories.permission_type
WHERE categories.category_id IS NULL
Example results
"parent_category_id","category_id","category_name","category_position","permission_type","group_name","group_id"
70,40,"CSMU Bogan's Run",57,1,"bd2l",44
70,40,"CSMU Bogan's Run",57,1,"bd2l-sales-marketing",59
70,55,"CSMU Miaffirmations",63,1,"miaffirmations",56
70,45,"CSMU Music",61,1,"csmu-music",64