Data Explorer Categories Permissions

Data & reporting
1

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?

2

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
2 Likes