Catégories des permissions de Data Explorer

Communauté Données et rapports
1

Existe-t-il une requête SQL existante pour extraire les autorisations distinctes de toutes les sous-catégories d’une catégorie parente par rapport aux autorisations d’une catégorie parente, en utilisant le plugin Data Explorer ?

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 « J'aime »