Hi,
I have a query that is minimally complex, and I need to convert it into a custom query object.
Basically, I am performing groups-based authorization control. I have the following tables:
containers
+id
+name
+site_id
---
sites
+id
+name
---
groups
+id
+name
---
containers_groups
+id
+container_id
+group_id
---
groups_sites
+id
+group_id
+site_id
---
groups_users
+id
+group_id
+user_id
If a user has membership in group A, then it can see all containers referenced in containers_groups where group is A.
Also, that user can see all containers linked to sites that are referenced in groups_sites where group is A.
The SQL works as expected. There’s a union of two queries wrapped by a query that JOINs the sites table. I’ve parameterized the user_id value.
SELECT
c.id
,c.site_id
,c.name
,s.name AS "site_name"
FROM containers c
LEFT JOIN (
SELECT
g.id AS "group_id"
,s.id AS "site_id"
,c.id AS "container_id"
FROM groups_users gu
LEFT JOIN groups g ON g.id = gu.group_id
LEFT JOIN groups_sites gs ON gs.group_id = g.id
LEFT JOIN sites s ON s.id = gs.site_id
LEFT JOIN containers c ON c.site_id = s.id
WHERE gu.user_id = ?
UNION
SELECT
g.id AS "group_id"
,s.id AS "site_id"
,c.id AS "container_id"
FROM groups_users gu
LEFT JOIN groups g ON g.id = gu.group_id
LEFT JOIN containers_groups cg ON cg.group_id = g.id
LEFT JOIN containers c ON c.id = cg.container_id
LEFT JOIN sites s ON s.id = c.site_id
WHERE gu.user_id = ?
) u ON u.container_id = c.id
LEFT JOIN sites s ON s.id = u.site_id
WHERE u.group_id IS NOT NULL
I need to run this query in the “index” action of the ContainersController. The reason that I am not using just a raw query is that I would like to use the built-in Paginator.
Assistance in creating the CakePHP-flavored query object would be appreciated.