Fix queries using ACL for content and media

[sielay]

Issue Title

Type

• Bug / Documentation

  Environment

• Version / Commit hash: HEAD

  Expected behaviour

We have concept of taxonomy, badge/tag which can be grouped by taxonomyType.

Some taxonomyTypes can be restrictive, what means they would be used not only for description and filtering, but also for ACL.

Therefore following elements can have taxonomies assigned:

• user
• role
• content
• template
• media

Some taxonomies are inherited

• user from role
• content from template

Generic rule defined in QueryBuilder is following:

User can see content (access or find in listing/search) if:

• content has no restrictive taxonomy assigned
• OR
  • for each restrictive taxonomyType assigned to content and its template (or to media) user or some of roles assigned to him have at least one common taxonomy.

    Actual behaviour

In case of two or more restrictive taxonomies, query pass user who have at least one taxonomy type intersecting.

Example of current query

SELECT count(*) as "count" FROM content WHERE 
        id IN (
            SELECT id FROM content AS con WHERE 
        (
            SELECT count(*) FROM (
                SELECT ttt."taxonomyId" FROM "content" AS c
                    JOIN "templateToTaxonomy" AS ttt
                        ON ttt."templateId" = c."templateId"
                        AND c.id = con.id
                UNION
                SELECT ctt."taxonomyId" FROM "contentToTaxonomy" AS ctt
                    WHERE ctt."contentId" = con.id
                ) AS FOO
         WHERE "taxonomyId" IN (4)) > 0

       ) AND state = 'published' AND 
        "publishAt" <= NOW()
     AND 
        id NOT IN (
            SELECT id FROM content WHERE "templateId" IN (
                SELECT "templateId" FROM "templateToTaxonomy" where "taxonomyId" IN (20, 22)
            )
            UNION
            SELECT "contentId" FROM "contentToTaxonomy" WHERE "taxonomyId" IN (20, 22)
            EXCEPT
            SELECT id FROM content WHERE "templateId" IN (
                SELECT "templateId" FROM "templateToTaxonomy" where "taxonomyId" IN (14, 13, 21, 12, 11)
            )
            EXCEPT
            SELECT "contentId" FROM "contentToTaxonomy" WHERE "taxonomyId" IN (14, 13, 21, 12, 11)
        )

Notes

Taxonomies for content

SELECT ctt."contentId", t."id", t."taxonomyTypeId" FROM "contentToTaxonomy" ctt JOIN "taxonomy" t ON t."id" = ctt."taxonomyId"
UNION
SELECT c.id, t.id, t."taxonomyTypeId" FROM "content" c JOIN "templateToTaxonomy" ttt ON ttt."templateId" = c."templateId"  JOIN "taxonomy" t ON t."id" = ttt."taxonomyId"

User taxonomies

/* ALL TAXONOMIES ASSIGNED TO THIS USER */
SELECT 
    t.id, t."taxonomyTypeId", t.name
    FROM (
        SELECT utt."taxonomyId" FROM "userToTaxonomy" utt WHERE utt."taxonomyUserId" = 20
        UNION
        SELECT rtt."taxonomyId"  FROM "roleToTaxonomy" rtt JOIN acl ON acl."roleId" = rtt."roleId" AND acl."userId" = 20
    ) AS "userTaxonomies"
    JOIN "taxonomy" AS t ON t.id = "userTaxonomies"."taxonomyId"
/* /ALL TAXONOMIES ASSIGNED TO THIS USER */

[sielay]

Wonder, if this wouldn't require writing procedure.

[sielay]

112 is fixing this