Is there any existing SQL to extract the distinct permissions for all the subcategories for a parent category against the permissions for a parent category using the Data Explorer plugin?
סיכום מבוסס בינה מלאכותית
The discussion is about Data Explorer Categories Permissions. csmu is seeking a SQL query to extract the distinct permissions for subcategories of a parent category in comparison to the parent category’s permissions. csmu has shared an existing SQL query that shows all permissions in subcategories that are not in the parent categories, along with example results.
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