Cakephp 3 ORM contain condition use field from parent table

283 Views Asked by At

Is it possible to use a field from a parent table in the conditions of a contained table in cakephp 3? For example, I have an Entities, Locations, and Stats table. Entities have many Locations, and Locations have many Stats. I want to include all Stats with a stat_date after the Entity start_date.

The most relevant line of code is 'stat_date > ' => 'Entities.start_date' from the EntitiesTable model (code below)

class EntitiesTable extends Table
{
    public function initialize(array $config)
    {
        $this->hasMany('Locations');
    }

    public function getStatAfterStartDate()
    {
        $entities = $this->find()
            ->contain([
                'Locations' => [
                    'Stats' => [
                        'conditions' => [
                            /*** Filter Contained Table By Field From Parent Table ***/
                            'stat_date > ' => 'Entities.start_date'
                        ]
                    ]
                ],
            ])
            ->toArray();

        return $entities;
    }
}
class LocationsTable extends Table {

    public function initialize(array $config) {
        $this->belongsTo('Entities');
        $this->hasMany('Stats');
    }

}
class StatsTable extends Table
{
    public function initialize(array $config)
    {
        $this->belongsTo('Locations');
    }
}

To help clarify things, I have included the sql code that would pull the data I want.

SELECT Entities.*, Locations.*, Stats.*
FROM Entities
LEFT JOIN Locations ON Locations.entity_id = Entity.id
LEFT JOIN Stats ON Stats.location_id = Locations.id AND Stats.stat_date > Entity.start_date;
0

There are 0 best solutions below