Data Explorer Categories Permissions

סיכום מבוסס בינה מלאכותית

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.

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?

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