¿Existe alguna consulta SQL existente para extraer los permisos distintos para todas las subcategorías de una categoría principal en comparación con los permisos de una categoría principal utilizando el complemento Data Explorer?
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