cakephp contain filtering by parent model value

Question

I have these relations:

Attendance belongs to Employee

Schedule belongs to Employee

This find() works just fine to get all Attendance records in a date range.

    $data = $this->Attendance->find('all', array(
        'contain' => array(
            'Employee' => array(
                'Schedule' => array(
                    'conditions' => array('sche_status' => 1),
                    'order' => 'sche_valid DESC',
                    'limit' => 1
            ))
        ),
        'conditions' => $conditions,
        'order' => array('employee_id', 'att_date')
            )
    );

However, this Schedule condition

'conditions' => array('sche_status' => 1),

gets the "current" Schedule. Problem is when Employee has more than one Schedule in the date range. In order to get the relevant Schedule for it's parent Attendance record, I need to also condition on Attendance.att_date

Tried

'conditions' => array('sche_status' => 1, 'ho_valid <=' => Attendance.att_date)

and

'conditions' => array('sche_status' => 1, 'ho_valid <=' => $this->Attendance.att_date)

How can I reference Attendance.att_date from the contan Schedule conditions? Is that possible? My current datasets are pretty clean and my find() is dry, wouldn't want to make a mess here.

Any help greatly appreciated.

Answer 1

When you use contain(), it does individual queries. In your example, it will actually do three queries individually that then get combined by CakePHP into the returned data array.

So, you cannot use fields from a different model to condition against a contained model.

Your best bet is to either query the field first, then use it's value in this find, or use joins(), which makes all the results return from a single query instead of three separate queries.

Side note - it appears you're building this query in a Controller - following the MVC structure, best practice is to keep ALL queries in your Model, not your Controller - then just call the Model's method (which has the find()) from the Controller to retrieve the results.