Need help converting SQL into query object - RESOLVED

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.

This authorization control needs to be used on “related” sections too.

For example, user Alice is a member of group A, and should be able to browse to any site that is referenced in group A and any container referenced in group A.

Also, since Alice is not a member of group B, then she MUST NOT be allowed to see site C or container D, then the “Related Sites” section MUST NOT include links to those objects.

Also, if she were to copy-and-paste a link to the ‘view’, ‘edit’, ‘delete’ page for any of the disallowed objects, then her browser should redirected to the ‘index’ action for that type of object.

NOTE: I am already using the following in my AppController -> initialize method:

...
$this->loadComponent('RequestHandler');
$this->loadComponent('Flash');
$this->loadComponent('Auth', [
    'authorize'=> 'Controller',
    'authenticate' => [
        'Form' => [
            'fields' => [
                'username' => 'email',
                'password' => 'pw'
            ],
            'finder' => 'auth'
        ]
    ],
    'checkAuthIn' => 'Controller.initialize',
...

Ideas? Comments? Criticism?

I’m thinking that perhaps a Behavior might be the right approach. Using that approach, I presume that I would add a " implements GroupsAuthorizableBehavior" or something.

Ideas? Comments? Criticism? Mocking?

Perhaps Traits?

Anyone?

if i get it right you basicly want to show all sites and containers of selected user?

if yes then its quite easy, you just use BelongsToMany with through option

$this->Users->findById($userId)
            ->contain([
                'Groups' => [
                    'Sites',
                    'Containers' => ['Sites']
                ]
            ])
            ->first();

Hmm, I’m not sure if that will work.

In the ContainersController -> index method, I need to retrieve only the list of Containers that the User is permitted to view.

Similarly, in the view method of the SitesController, I need to retrieve only the list of Sites that the User is permitted to view. Since Containers are linked directly to Sites, the query is much simpler.

Additionally, in the view method of the UsersController, I need to retrieve the list of Sites and Containers that the specific User is allowed to view.

This design is defined by the following business rules:

  • by default, Users cannot view any Sites or Containers,
  • if a User has access to a Site, then they have access to all Containers at that Site,
  • if a User has access to only a subset of the Containers at a Site, then can see the Site “view” page, but the data for its “related” section ( for example ) would only contain the subset of Containers.

This should help:

SELECT id, name FROM users;
+----+----------+
| id | name     |
+----+----------+
| 1  | Alice    |
| 2  | Bob      |
| 3  | Chris    |
| 4  | Danielle |
| 5  | Ernesto  |
| 6  | Francine |
+----+----------+

SELECT id, name FROM sites;
+----+---------+
| id | name    |
+----+---------+
| 1  | Alpha   |
| 2  | Bravo   |
| 3  | Charlie |
+----+---------+

SELECT id, name, site_id FROM containers;
+----+---------+---------+
| id | name    | site_id |
+----+---------+---------+
| 1  | Apples  | 1       |
| 2  | Beets   | 1       |
| 3  | Carrots | 2       |
| 4  | Dates   | 2       |
| 5  | Eggs    | 3       |
| 6  | Figs    | 3       |
+----+---------+---------+

SELECT id, name, descr FROM groups;
+----+--------------------+------------------------------------------------+
| id | name               | descr                                          |
+----+--------------------+------------------------------------------------+
| 1  | AlphaOnly          | site Alpha ... and its containers              |
| 2  | AlphaAndCarrots    | site Alpha and Carrots container               |
| 3  | ApplesAndDates     | Apples container and Dates container           |
| 4  | AlphaAndBravo      | sites Alpha and Bravo ... and their containers |
| 5  | BeetsOnly          | Beets container                                |
| 6  | DatesOnly          | Dates container                                |
+----+--------------------+------------------------------------------------+

SELECT id, container_id, group_id FROM containers_groups;
+----+--------------+----------+
| id | container_id | group_id |
+----+--------------+----------+
|  1 | 3            | 2        |
|  2 | 1            | 3        |
|  3 | 4            | 3        |
|  4 | 2            | 5        |
|  5 | 4            | 6        |
+----+--------------+----------+

SELECT id, group_id, site_id FROM groups_sites;
+----+----------+-----------+
| id | group_id | site_id   |
+----+----------+-----------+
| 1  | 1        | 1         |
| 2  | 2        | 1         |
| 3  | 4        | 1         |
| 4  | 4        | 2         |
+----+----------+-----------+

SELECT id, group_id, user_id FROM groups_users;
+----+----------+-----------+
| id | group_id | user_id   |
+----+----------+-----------+
| 1  | 1        | 1         |
| 2  | 2        | 2         |
| 3  | 3        | 3         |
| 4  | 4        | 4         |
| 5  | 1        | 5         |
| 6  | 5        | 5         |
| 7  | 1        | 6         |
| 8  | 6        | 6         |
+----+----------+-----------+

Common Use Cases:
* User is a member of a group that has one site
* User is a member of a group that has one site and a Container of another site.
* User is a member of a group that has multiple Containers of differnt sites.
* User is a member of a group that has multiple sites
* User is a member of multiple groups. Each group has a site not included in the other groups.
* User is a member of multiple groups. One group has one site, and the other group has one container of a different group.

Concrete Use Case Examples Using Data Above ( ... with minor changes to the SELECT field names and ordering ) :
* Alice is a member of group "AlphaOnly".
    - She has access to site "Alpha" and all of its containers.
    +---------+-----------+--------------+----------------+
    | site_id | site_name | container_id | container_name | 
    +---------+-----------+--------------+----------------+
    | 1       | Alpha     | 1            | Apples         |
    | 1       | Alpha     | 2            | Beets          |
    +---------+-----------+--------------+----------------+

* Bob is a member of group "AlphaAndCarrots".
    - He has access to site "Alpha" and container "Carrots", which is associated with site "Bravo".
    +---------+-----------+--------------+----------------+
    | site_id | site_name | container_id | container_name | 
    +---------+-----------+--------------+----------------+
    | 1       | Alpha     | 1            | Apples         |
    | 1       | Alpha     | 2            | Beets          |
    | 2       | Bravo     | 3            | Carrots        |
    +---------+-----------+--------------+----------------+

* Chris is a member of group "ApplesAndDates".
    - He has access to containers "Apples" and "Dates", which are associated with sites "Alpha" and "Bravo" respectively.
    +---------+-----------+--------------+----------------+
    | site_id | site_name | container_id | container_name | 
    +---------+-----------+--------------+----------------+
    | 1       | Alpha     | 1            | Apples         |
    | 2       | Bravo     | 4            | Dates          |
    +---------+-----------+--------------+----------------+

* Danielle is a member of group "AlphaAndBravo".
    - She has access to site "Alpha" and "Bravo" and all of their containers.
    +---------+-----------+--------------+----------------+
    | site_id | site_name | container_id | container_name | 
    +---------+-----------+--------------+----------------+
    | 1       | Alpha     | 1            | Apples         |
    | 1       | Alpha     | 2            | Beets          |
    | 2       | Bravo     | 3            | Carrots        |
    | 2       | Bravo     | 4            | Dates          |
    +---------+-----------+--------------+----------------+

* Ernesto is a member of groups "AlphaOnly" and "BeetsOnly".
    - He has access to site "Alpha" and all of its containers. The container "Beets" is associated with site "Alpha"; so, he has access granted to container "Beets" from two different groups.
    +---------+-----------+--------------+----------------+
    | site_id | site_name | container_id | container_name | 
    +---------+-----------+--------------+----------------+
    | 1       | Alpha     | 1            | Apples         |
    | 1       | Alpha     | 2            | Beets          |
    +---------+-----------+--------------+----------------+

* Francine is a member of groups "AlphaOnly" and "DatesOnly".
    - She has access to site "Alpha" and all of its containers, and to container "Dates".
    +---------+-----------+--------------+----------------+
    | site_id | site_name | container_id | container_name | 
    +---------+-----------+--------------+----------------+
    | 1       | Alpha     | 1            | Apples         |
    | 1       | Alpha     | 2            | Beets          |
    | 2       | Bravo     | 4            | Dates          |
    +---------+-----------+--------------+----------------+

I hope that this provides some clarity to the issue.

I can execute the raw query with no problem, but I want to use the CakePHP-specific way of doing it, so that I can use things like “paginate()”.

May main stumbling point is how to do the subquery with a union in that subquery. (See query above)

Thanks.

I meant to say “derived table” not “subquery”.

should be close to something like - you should probably figure it out by yourself if something is wrong

$subqueryBase = $this->GroupsUsers
            ->find()
            ->select(['Groups.id', 'Site.id', 'Container.id'])
            ->leftJoin([
                'Groups' => 'groups',
                'Groups.id' => 'GroupsUsers.group_id'
            ])
            ->where([
                'GroupsUsers.user_id' => 1
            ]);

        $toUnion = (clone $subqueryBase)
            ->leftJoin([
                'GroupsSites' => 'groups_sites',
                'GroupsSites.group_id' => 'Groups.id'
            ])
            ->leftJoin([
                'Sites' => 'sites',
                'Sites.id' => 'GroupsSites.site_id'
            ])
            ->leftJoin([
                'Containers' => 'containers',
                'Containers.site_id' => 'Sites.id'
            ]);

        $subqueryBase
            ->leftJoin([
                'ContainersGroups' => 'containers_groups',
                'ContainersGroups.group_id' => 'Groups.id'
            ])
            ->leftJoin([
                'Containers' => 'containers',
                'Containers.id' => 'ContainersGroups.container_id'
            ])
            ->leftJoin([
                'Sites' => 'sites',
                'Sites.id' => 'Containers.site_id'
            ]);

        $subqueryBase->union($toUnion);

        $main = $this->Containers
            ->find()
            ->select([
                'Containers.id', 'Containers.site_id', 'Sites.name',
            ])
            ->leftJoin([
                'UserSites' => $subqueryBase, ['UserSites.container_id' => 'Containers.id']
            ])
            ->leftJoin([
                'Sites' => 'sites', ['Sites.id' => 'UserSites.site_id']
            ])
            ->where([
                'UserSites.group_id IS NOT' => null
            ])
            ->all();

Yes, that does work, @Graziel. That is almost exactly the solution that I finished seconds prior to your post. :smiley:

I’ll post the version that I have, so that if someone else encounters the same difficulties that I had, then they’ll have both solutions.

I truly appreciate your assistance. Your initial post steered me in the correct direction.

Thanks.

As promised, here’s the solution that I have, as I mentioned above:

class ContainersController extends AppController
{
    ...
    
    public function index(
    {
        ...
        
        $groups_users = TableRegistry::get('GroupsUsers');
        $groups_users_sites = $groups_users->find('all')
            ->select([
                'group_id'     => 'g.id',
                'customer_id'  => 'g.customer_id',
                'site_id'      => 's.id',
                'container_id' => 'c.id',
            ])
            /* LEFT JOIN groups g ON  g.id = gu.group_id */
            ->join([
                'table' => 'groups',
                'alias' => 'g',
                'type' => 'LEFT',
                'conditions' => 'g.id = GroupsUsers.group_id',
            ])
            /* LEFT JOIN groups_sites gs ON gs.group_id =  g.id */
            ->join([
                'table' => 'groups_sites',
                'alias' => 'gs',
                'type' => 'LEFT',
                'conditions' => 'gs.group_id = g.id',
            ])
            /* LEFT JOIN sites s ON  s.id = gs.site_id */
            ->join([
                'table' => 'sites',
                'alias' => 's',
                'type' => 'LEFT',
                'conditions' => 's.id = gs.site_id',
            ])
            /* LEFT JOIN containers c ON c.site_id =  s.id */
            ->join([
                'table' => 'containers',
                'alias' => 'c',
                'type' => 'LEFT',
                'conditions' => 'c.site_id = s.id',
            ])
            ->where(['GroupsUsers.user_id' => $user['id']])
        ;
        $groups_users_containers = $groups_users->find('all')
            ->select([
                'group_id'     => 'g.id',
                'customer_id'  => 'g.customer_id',
                'site_id'      => 's.id',
                'container_id' => 'c.id',
            ])
            /* LEFT JOIN groups g ON  g.id = gu.group_id */
            ->join([
                'table' => 'groups',
                'alias' => 'g',
                'type' => 'LEFT',
                'conditions' => 'g.id = GroupsUsers.group_id',
            ])
            /* LEFT JOIN containers_groups cg ON cg.group_id = g.id */
            ->join([
                'table' => 'containers_groups',
                'alias' => 'cg',
                'type' => 'LEFT',
                'conditions' => 'cg.group_id = g.id',
            ])
            /* LEFT JOIN containers c ON  c.id = cg.container_id */
            ->join([
                'table' => 'containers',
                'alias' => 'c',
                'type' => 'LEFT',
                'conditions' => 'c.id = cg.container_id',
            ])
            /* LEFT JOIN sites s ON  s.id = c.site_id */
            ->join([
                'table' => 'sites',
                'alias' => 's',
                'type' => 'LEFT',
                'conditions' => 's.id = c.site_id',
            ])
            ->where(['GroupsUsers.user_id' => $user['id']])
        ;
        $groups_users_sites->union($groups_users_containers);
        $containers = $this->containers->find('all')
            ->select([
                'containers.id'
                ,'containers.site_id'
                ,'containers.name'
                ,'containers.descr'
                ,'customer_name' => 'cus.name'
                ,'group_name'    => 'g.name'
                ,'site_name'     => 's.name'
            ])
            /* This jois our derived table (i.e. the 'UNION' query. */
            ->join([
                'table' => $groups_users_sites,
                'alias' => 'u',
                'type' => 'LEFT',
                'conditions' => 'u.container_id = containers.id',
            ])
            /* LEFT JOIN groups g ON g.id = u.group_id */
            ->join([
                'table' => 'groups',
                'alias' => 'g',
                'type' => 'LEFT',
                'conditions' => 'g.id = u.group_id',
            ])
            ->join([
                'table' => 'sites',
                'alias' => 's',
                'type' => 'LEFT',
                'conditions' => 'containers.site_id = s.id',
            ])
            ->join([
                'table' => 'customers',
                'alias' => 'cus',
                'type' => 'LEFT',
                'conditions' => 'cus.id = u.customer_id',
            ])
            /* WHERE u.group_id IS NOT NULL */
            ->where(['u.group_id IS NOT NULL'])
            ->order(['customer_name', 'site_name', 'containers.name'])
        ;
        $containers = $this->paginate( $containers );
        debug($containers); exit;

        ...
    }
    
    ...
}

The debug statement outputs the expected records.

Yay!!! :smiley:

Thanks again, @Graziel, for your help.