Specifying record criteria on more than one model in one pagination call.

Question

Hi all,

I am stuck on pagination in CakePHP 1.3. I am trying to paginate feePayment records based on certain criteria.

feePayments belongs to Students which in turn belongs YearGroups.

I want to paginate 'unpaid' feePayments for each year group. The problem I am having is that the SQL query seems to only take into account the conditions I specified for the FeePayment model and ignores the YearGroup criteria so only overdue unpaid records are returned regardless of the year group specified.

Here is my code:

function unpaidClass($id) {
    $this->paginate = array(
    'FeePayment' => array ('recursive' => 1, 'conditions' => array('FeePayment.status' => 'Unpaid', 'FeePayment.due_date <= ' => date("Y-m-d"))),
    'YearGroup' => array ('recursive' => 1, 'conditions' => array('YearGroup.school_year' => $id))
    );

    $this->set('feePayments', $this->paginate());
}

Hope this makes sense, appreciate any help.

Thanks,

Sid.

Answer 1

You should consider using the Containable behavior. This behavior allows you to group the necessary data you want without relaying on the "recursiveness" of your query. You can place conditions on your contained data similar to the way you would in your queries and is a more permanent way to structure data across your application instead of specifying conditions over and over again in each query.

Paginate should automatically pick up these associations when you Paginate your main model. Here's an example of what I mean here: http://cakephp.1045679.n5.nabble.com/Paginate-with-Containable-td1300971.html#a1300971

These should make your task easier.

Answer 2

After a lot of searching the net and reading CakePHP's documentation, here is the solution I came up with:

    function unpaidClass($id) {
    $this->FeePayment->unbindModel(array(
        'belongsTo' => array('Student')
    ), $reset = 0);

    $this->FeePayment->bindModel(array(
        'belongsTo' => array(
            'Student' => array(
                'foreignKey' => false,
                'conditions' => array('Student.id = FeePayment.student_id')
            ),
            'YearGroup' => array(
                'foreignKey' => false,
                'conditions' => array('YearGroup.id = Student.year_group_id')
            )
        )
    ), $reset = 0);

    $this->paginate = array(
        'contain' => array('Student','YearGroup'),
        'conditions' => array('YearGroup.school_year' => $id, 
            'FeePayment.status' => 'Unpaid',
            'FeePayment.due_date <= ' => date("Y-m-d")));

    $this->set('feePayments', $this->paginate());
}