{"action":"closed","issue":{"url":"https://api.github.com/repos/cakephp/cakephp/issues/6770","labels_url":"https://api.github.com/repos/cakephp/cakephp/issues/6770/labels{/name}","comments_url":"https://api.github.com/repos/cakephp/cakephp/issues/6770/comments","events_url":"https://api.github.com/repos/cakephp/cakephp/issues/6770/events","html_url":"https://github.com/cakephp/cakephp/issues/6770","id":86596937,"number":6770,"title":"Q, 3.x) How to filter lots of tables by related data for an API?","user":{"login":"davidyell","id":49889,"avatar_url":"https://avatars.githubusercontent.com/u/49889?v=3","gravatar_id":"","url":"https://api.github.com/users/davidyell","html_url":"https://github.com/davidyell","followers_url":"https://api.github.com/users/davidyell/followers","following_url":"https://api.github.com/users/davidyell/following{/other_user}","gists_url":"https://api.github.com/users/davidyell/gists{/gist_id}","starred_url":"https://api.github.com/users/davidyell/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/davidyell/subscriptions","organizations_url":"https://api.github.com/users/davidyell/orgs","repos_url":"https://api.github.com/users/davidyell/repos","events_url":"https://api.github.com/users/davidyell/events{/privacy}","received_events_url":"https://api.github.com/users/davidyell/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"milestone":null,"comments":1,"created_at":"2015-06-09T13:17:48Z","updated_at":"2015-06-09T13:19:13Z","closed_at":"2015-06-09T13:19:13Z","body":"**Scenario**\r\nI am building an API endpoint, and I need to filter all the data in the query by a certain field. In this scenario end points can be requested with a `site_id` which should filter all the tables and related tables by the passed value.\r\n\r\n**Setup**\r\nI am containing a large number of table classes, and will be looking to filter each if they're related to `Sites`\r\n\r\n```php\r\n// src/Model/Table/PackagesTable.php\r\n        $this->addBehavior('ApiVerbose', [\r\n            'contain' => [\r\n                'Broadbands' => [\r\n                    'Hubs',\r\n                    'BroadbandTypes',\r\n                    'Attributes' => [\r\n                        'AttributeTypes',\r\n                        'Sites'\r\n                    ],\r\n                    'conditions' => [\r\n                        'Broadbands.status_id' => 1,\r\n                        'Broadbands.deleted' => false\r\n                    ]\r\n                ],\r\n                'Calls' => [\r\n                    'Attributes' => [\r\n                        'AttributeTypes',\r\n                        'Sites'\r\n                    ],\r\n                    'conditions' => [\r\n                        'Calls.status_id' => 1,\r\n                        'Calls.deleted' => false\r\n                    ]\r\n                ],\r\n                'Tvs' => [\r\n                    'Attributes' => [\r\n                        'AttributeTypes',\r\n                        'Sites'\r\n                    ],\r\n                    'conditions' => [\r\n                        'Tvs.status_id' => 1,\r\n                        'Tvs.deleted' => false\r\n                    ]\r\n                ],\r\n                'Boxes' => [\r\n                    'conditions' => [\r\n                        'Boxes.status_id' => 1,\r\n                        'Boxes.deleted' => false\r\n                    ]\r\n                ],\r\n                'LineRentals' => [\r\n                    'conditions' => [\r\n                        'LineRentals.status_id' => 1,\r\n                        'LineRentals.deleted' => false\r\n                    ]\r\n                ],\r\n                'Providers' => [\r\n                    'conditions' => [\r\n                        'Providers.status_id' => 1,\r\n                        'Providers.deleted' => false\r\n                    ]\r\n                ],\r\n                'SamknowsProviders',\r\n                'PackageTypes' => [\r\n                    'conditions' => [\r\n                        'PackageTypes.status_id' => 1,\r\n                        'PackageTypes.deleted' => false\r\n                    ]\r\n                ],\r\n                'Pricings' => [\r\n                    'Expiries',\r\n                    'conditions' => [\r\n                        'Pricings.status_id' => 1,\r\n                        'Pricings.deleted' => false\r\n                    ]\r\n                ],\r\n                'PriceTypes',\r\n                'Offers' => [\r\n                    'OfferTypes',\r\n                    'Expiries',\r\n                    'Sites',\r\n                    'conditions' => [\r\n                        'Offers.status_id' => 1,\r\n                        'Offers.deleted' => false\r\n                    ]\r\n                ],\r\n                'Attributes' => [\r\n                    'AttributeTypes',\r\n                    'Expiries',\r\n                    'Sites',\r\n                    'conditions' => [\r\n                        'Attributes.status_id' => 1,\r\n                        'Attributes.deleted' => false\r\n                    ]\r\n                ],\r\n                'Sites'\r\n            ]\r\n        ]);\r\n```\r\n\r\n**Expected behaviour**\r\nI wouldn't expect any Packages which are not related to the passed `site_id` to be returned, and any that are, I would expect their related tables such as `Broadbands.Attributes` would be filtered by the passed `site_id`.\r\n\r\n**What I have currently**\r\n```php\r\n/**\r\n * Filter the result set by site id\r\n *\r\n * @param Query $query\r\n * @return Query\r\n */\r\nprotected function filterBySite(Query $query)\r\n{\r\n    if (!isset($this->_controller()->request->query['verbose'])) {\r\n        return $query;\r\n    }\r\n\r\n    $siteId = $this->_controller()->request->query['site_id'];\r\n\r\n    // TODO: Need to figure out how to filter the data without using matching() as it puts the matched data into the _matchingData array\r\n    if(in_array('sites', $this->_table()->associations()->keys())) {\r\n        $query->contain('Sites')\r\n            ->matching('Sites', function ($q) use ($siteId) {\r\n                return $q->where(['Sites.id' => $siteId]);\r\n            });\r\n    }\r\n\r\n    if (in_array('attributes', $this->_table()->associations()->keys())) {\r\n        $query->matching('Attributes', function ($q) use ($siteId) {\r\n            return $q->where(['Sites.id' => $siteId]);\r\n        });\r\n    }\r\n\r\n    return $query;\r\n}\r\n```\r\n\r\n**Why I am trying to avoid `matching()`**\r\nI am finding that using `matching()` places the matching data into the `_matchingData` array element, which would mean that I would need to use `matching()` on all the tables in the query and then assign the `_matchingData` to the view so that it can be serialized for the response.\r\n\r\n**Best approach?**\r\nSo I'm interested in how to approach this type of problem. How can I filter this much data, across this many table classes consistently and still return an accurate Pagination array along with correct filtered data, and not additional data data the user doesn't need."}}