Scenario
I 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.
Setup
I am containing a large number of table classes, and will be looking to filter each if they're related to Sites
// src/Model/Table/PackagesTable.php
$this->addBehavior('ApiVerbose', [
'contain' => [
'Broadbands' => [
'Hubs',
'BroadbandTypes',
'Attributes' => [
'AttributeTypes',
'Sites'
],
'conditions' => [
'Broadbands.status_id' => 1,
'Broadbands.deleted' => false
]
],
'Calls' => [
'Attributes' => [
'AttributeTypes',
'Sites'
],
'conditions' => [
'Calls.status_id' => 1,
'Calls.deleted' => false
]
],
'Tvs' => [
'Attributes' => [
'AttributeTypes',
'Sites'
],
'conditions' => [
'Tvs.status_id' => 1,
'Tvs.deleted' => false
]
],
'Boxes' => [
'conditions' => [
'Boxes.status_id' => 1,
'Boxes.deleted' => false
]
],
'LineRentals' => [
'conditions' => [
'LineRentals.status_id' => 1,
'LineRentals.deleted' => false
]
],
'Providers' => [
'conditions' => [
'Providers.status_id' => 1,
'Providers.deleted' => false
]
],
'SamknowsProviders',
'PackageTypes' => [
'conditions' => [
'PackageTypes.status_id' => 1,
'PackageTypes.deleted' => false
]
],
'Pricings' => [
'Expiries',
'conditions' => [
'Pricings.status_id' => 1,
'Pricings.deleted' => false
]
],
'PriceTypes',
'Offers' => [
'OfferTypes',
'Expiries',
'Sites',
'conditions' => [
'Offers.status_id' => 1,
'Offers.deleted' => false
]
],
'Attributes' => [
'AttributeTypes',
'Expiries',
'Sites',
'conditions' => [
'Attributes.status_id' => 1,
'Attributes.deleted' => false
]
],
'Sites'
]
]);
Expected behaviour
I 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
.
What I have currently
/**
* Filter the result set by site id
*
* @param Query $query
* @return Query
*/
protected function filterBySite(Query $query)
{
if (!isset($this->_controller()->request->query['verbose'])) {
return $query;
}
$siteId = $this->_controller()->request->query['site_id'];
// TODO: Need to figure out how to filter the data without using matching() as it puts the matched data into the _matchingData array
if(in_array('sites', $this->_table()->associations()->keys())) {
$query->contain('Sites')
->matching('Sites', function ($q) use ($siteId) {
return $q->where(['Sites.id' => $siteId]);
});
}
if (in_array('attributes', $this->_table()->associations()->keys())) {
$query->matching('Attributes', function ($q) use ($siteId) {
return $q->where(['Sites.id' => $siteId]);
});
}
return $query;
}
Why I am trying to avoid matching()
I 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.
Best approach?
So 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.