# Need help converting SQL into query object - RESOLVED

**URL:** <https://discourse.cakephp.org/t/need-help-converting-sql-into-query-object-resolved/2762>\
**Category:** Need Help\
**Created:** [June 22, 2017, 5:08am UTC](https://discourse.cakephp.org/t/need-help-converting-sql-into-query-object-resolved/2762 "2017-06-22T05:08:09Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![caiblack](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/caiblack/32/202_2.png) [@caiblack](https://discourse.cakephp.org/u/caiblack)\
**Post date:** [June 22, 2017, 5:08am UTC](https://discourse.cakephp.org/t/need-help-converting-sql-into-query-object-resolved/2762/1 "2017-06-22T05:08:09Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![caiblack](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/caiblack/32/202_2.png) [@caiblack](https://discourse.cakephp.org/u/caiblack)\
**Post date:** [June 24, 2017, 10:24pm UTC](https://discourse.cakephp.org/t/need-help-converting-sql-into-query-object-resolved/2762/2 "2017-06-24T22:24:12Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![caiblack](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/caiblack/32/202_2.png) [@caiblack](https://discourse.cakephp.org/u/caiblack)\
**Post date:** [June 24, 2017, 10:27pm UTC](https://discourse.cakephp.org/t/need-help-converting-sql-into-query-object-resolved/2762/3 "2017-06-24T22:27:57Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![caiblack](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/caiblack/32/202_2.png) [@caiblack](https://discourse.cakephp.org/u/caiblack)\
**Post date:** [June 25, 2017, 12:06am UTC](https://discourse.cakephp.org/t/need-help-converting-sql-into-query-object-resolved/2762/4 "2017-06-25T00:06:09Z")

</div>

Perhaps Traits?

Anyone?

---

<div class="post-metadata">

**Author:** ![Graziel](https://avatars.discourse-cdn.com/v4/letter/g/71c47a/32.png) [@Graziel](https://discourse.cakephp.org/u/Graziel)\
**Post date:** [June 25, 2017, 4:47am UTC](https://discourse.cakephp.org/t/need-help-converting-sql-into-query-object-resolved/2762/5 "2017-06-25T04:47:17Z")

</div>

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](https://book.cakephp.org/3.0/en/orm/associations.html#belongstomany-associations) with `through` option

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

```

---

<div class="post-metadata">

**Author:** ![caiblack](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/caiblack/32/202_2.png) [@caiblack](https://discourse.cakephp.org/u/caiblack)\
**Post date:** [June 26, 2017, 10:11pm UTC](https://discourse.cakephp.org/t/need-help-converting-sql-into-query-object-resolved/2762/6 "2017-06-26T22:11:08Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![caiblack](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/caiblack/32/202_2.png) [@caiblack](https://discourse.cakephp.org/u/caiblack)\
**Post date:** [June 27, 2017, 10:42pm UTC](https://discourse.cakephp.org/t/need-help-converting-sql-into-query-object-resolved/2762/7 "2017-06-27T22:42:35Z")

</div>

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](http://discourse.cakephp.org/t/need-help-converting-sql-into-query-object/2762))

Thanks.

---

<div class="post-metadata">

**Author:** ![caiblack](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/caiblack/32/202_2.png) [@caiblack](https://discourse.cakephp.org/u/caiblack)\
**Post date:** [June 27, 2017, 11:13pm UTC](https://discourse.cakephp.org/t/need-help-converting-sql-into-query-object-resolved/2762/8 "2017-06-27T23:13:15Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Graziel](https://avatars.discourse-cdn.com/v4/letter/g/71c47a/32.png) [@Graziel](https://discourse.cakephp.org/u/Graziel)\
**Post date:** [June 28, 2017, 12:04am UTC](https://discourse.cakephp.org/t/need-help-converting-sql-into-query-object-resolved/2762/9 "2017-06-28T00:04:04Z")

</div>

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

```php
$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();

```

---

<div class="post-metadata">

**Author:** ![caiblack](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/caiblack/32/202_2.png) [@caiblack](https://discourse.cakephp.org/u/caiblack)\
**Post date:** [June 28, 2017, 12:11am UTC](https://discourse.cakephp.org/t/need-help-converting-sql-into-query-object-resolved/2762/10 "2017-06-28T00:11:07Z")

</div>

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

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.

---

<div class="post-metadata">

**Author:** ![caiblack](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/caiblack/32/202_2.png) [@caiblack](https://discourse.cakephp.org/u/caiblack)\
**Post date:** [June 28, 2017, 12:25am UTC](https://discourse.cakephp.org/t/need-help-converting-sql-into-query-object-resolved/2762/11 "2017-06-28T00:25:56Z")

</div>

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!!! 😃

Thanks again, @Graziel, for your help.