How to filter lots of tables by related data for an API?

181 views Asked by At

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.

0

There are 0 answers