0

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;
origamifreak2
  • 193
  • 1
  • 1
  • 10
  • You seem to be looking for **https://stackoverflow.com/questions/26799094/how-to-filter-by-conditions-for-associated-models**. – ndm Apr 21 '22 at 17:52
  • @ndm Thanks, but I looked over your answer and I'm not sure how it answers my question. None of the examples show how to filter a contained table using a field from the parent table. The relevant line in my code is 'stat_date > ' => 'Entities.start_date'. – origamifreak2 Apr 22 '22 at 15:43
  • Well, I assumed your problem was how to create those joins, as containing `hasMany` associations will _not_ create joins, but a separate query. So I guess you also want to look at **https://stackoverflow.com/questions/43725445/how-to-compare-two-fields-columns-in-a-condition/43726213#43726213**. – ndm Apr 22 '22 at 15:50

0 Answers0