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.