Multiple References to Associated Table in Cakephp

Question

After associating two tables (many weather radar frames which belong to weather radar sites) with belongsTo and hasMany, I was hoping to generate JSON with two "contains" on the same table (and different conditions) so I can list only the frames which have specific conditions. What I currently have doesn't work; it only writes the second contain to the JSON.

Radar Site Table:

...
public function initialize(array $config)
{
    parent::initialize($config);

    $this->setTable('radar_site');

    $this->setDisplayField('radar_id');
    $this->setPrimaryKey('radar_id');

    $this->hasMany('FutureFrame', [
        'foreignKey' => 'radar_id'
    ]);

    $this->hasMany('RadarFrame', [
        'foreignKey' => 'radar_id'
    ]);
}
...

Radar Frame Table:

public function initialize(array $config)
{
    parent::initialize($config);

    $this->setTable('radar_frame');

    $this->setDisplayField('radar_id');
    $this->setPrimaryKey('radar_id');

    $this->belongsTo('RadarSite', [
        'foreignKey' => 'radar_id'
    ]);
}

Controller:

public function getRadarData()
{
    $radarInfo = 
    $this->RadarSite->find(
        'all',
        [
            'contain' => [
                'RadarFrame' =>[
                    'sort' => ['RadarFrame.frame_time' => 'DESC'],
                    'conditions' => [
                        'RadarFrame.frame_time >' => time() - 60*60,
                        'RadarFrame.file_on_server =' => 1,
                        'RadarFrame.radar_type =' => 'velocity'
                    ]
                ],
                'RadarFrame' =>[
                    'sort' => ['RadarFrame.frame_time' => 'DESC'],
                    'conditions' => [
                        'RadarFrame.frame_time >' => time() - 60*60,
                        'RadarFrame.file_on_server =' => 1,
                        'RadarFrame.radar_type =' => 'reflectivity'
                    ]
                ]                   
            ]
        ]

    );    
    $this->set('radarInfo', $radarInfo);
}

JSON output:

 ...,{
        "radar_id": "KHGX",
        "radar_name": "Houston\/Galveston, TX",
        "elevation": 18,
        "tower_height": 20,
        "max_latitude": 31.6485,
        "min_latitude": 27.2476,
        "max_longitude": -92.4946,
        "min_longitude": -97.6634,
        "latitude": 29.4719,
        "longitude": -95.0792,
        "radar_frame": [
            {
                "radar_id": "KHGX",
                "radar_type": "reflectivity",
                "frame_time": 1495473662,
                "is_analyzed": 1,
                "average_speed": null,
                "average_direction": null,
                "file_on_server": 1
            },
            {
                "radar_id": "KHGX",
                "radar_type": "reflectivity",
                "frame_time": 1495473305,
                "is_analyzed": 1,
                "average_speed": null,
                "average_direction": null,
                "file_on_server": 1
            },
            {
                "radar_id": "KHGX",
                "radar_type": "reflectivity",
                "frame_time": 1495473002,
                "is_analyzed": 1,
                "average_speed": null,
                "average_direction": null,
                "file_on_server": 1
            }
        ]
    },...

What's the right way to do this? Is there an easy way to add an alias to my controller to differentiate between the two contain queries? I am using CakePHP 3.0.

Thank you!!

Rayann Nayran · Accepted Answer · 2017-05-23T12:59:05.800

Since your conditions are statics, you could put them into the relationship:

Radar Site Table:

public function initialize(array $config)
{
    parent::initialize($config);

    $this->setTable('radar_site');

    $this->setDisplayField('radar_id');
    $this->setPrimaryKey('radar_id');

    $this->hasMany('FutureFrame', [
        'foreignKey' => 'radar_id'
    ]);

    $this->hasMany('RadarFrameVelocity', [
       'className' => 'RadarFrame',
       'foreignKey' => 'radar_id',
       'conditions' => [
            'RadarFrame.frame_time >' => time() - 60*60,
            'RadarFrame.file_on_server =' => 1,
            'RadarFrame.radar_type =' => 'velocity'
        ]
    ]);

    $this->hasMany('RadarFrameReflectivity', [
        'className' => 'RadarFrame',
        'foreignKey' => 'radar_id',
        'conditions' => [
            'RadarFrame.frame_time >' => time() - 60*60,
            'RadarFrame.file_on_server =' => 1,
            'RadarFrame.radar_type =' => 'reflectivity'
        ]
    ]);
}

Controller:

public function getRadarData()
{
    $radarInfo = 
    $this->RadarSite->find(
        'all',
        [
            'contain' => [
                'RadarFrameVelocity' =>[
                    'sort' => ['RadarFrameVelocity.frame_time' => 'DESC']
                ],
                'RadarFrameReflectivity' => [
                    'sort' => ['RadarFrameReflectivity.frame_time' => 'DESC']
                ]                   
            ]
        ]

    );    
    $this->set('radarInfo', $radarInfo);
}

See also Associations - Linking Tables Together

Thanks for the response! Unfortunately, I get an error message: "SQLSTATE[42S02]: Base table or view not found: 1146 Table 'radarwarn.radar_frame_velocity' doesn't exist". It seems to be looking for a radar_frame_velocity table, which (rightfully so) doesn't exist. — WXMan, May 23 '17 at 12:39
Thank you thank you thank you!! One tweak that I had to make before it worked for me - I removed the "Radarframe." in the conditions and changed it to either "RadarFrameVelocity." or "RadarFrameReflectivity.". Thanks again! — WXMan, May 23 '17 at 12:54

Multiple References to Associated Table in Cakephp

1 Answers1