CakePHP - Most efficient deep check

Question

I have a very deep association in Cake:

User
---- Garage
---- ---- Vehicle
---- ---- ---- VehicleAlbum

What is the best way to check if a VehicleAlbum belongs to a user? Because doing a recursive 3 is very expensive. I have looked into contain, is this the best solution?

Thanks, Josh.

score 2 · Accepted Answer · edited May 23 '17 at 12:29

There is no such thing as recursive 3 (see book).

Nor can you use Containable to limit your find results based on a child condition (see reasoning).

I assume you'll want to do something like this (starting with Garage to reduce one query needed, since it has the user id as a field):

$this->Garage->find('all', array(
    'conditions' => array(
        'Garage.user_id' => $userId
    ),
    'joins' => array(
        array(
            'table' => 'vehicles',
            'alias' => 'Vehicle',
            'type' => 'inner',
            'conditions' => array(
                'Vehicle.garage_id = Garage.id'
            )
        ),
        array(
            'table' => 'vehicle_albums',
            'alias' => 'VehicleAlbum',
            'type' => 'inner',
            'conditions' => array(
                'VehicleAlbum.vehicle_id = Vehicle.id',
                'VehicleAlbum.id' => $vehicleAlbumId
            )
        )
    )
));

Should return result(s) if it is the owner or empty if not.

score 0 · Answer 2 · answered Jan 14 '14 at 08:54

No it will not be expensive until unless you are making a wrong query. Well write a query and join all the four tables and run explain...then check whether it is expensive or cheap. One more thing in your case if these tables are connected as shown above then you have to pay for the join query there is no other way out except changing your relations between the tables.

CakePHP - Most efficient deep check

2 Answers2