Cakephp2 Contain Deeper Association

Question

I need to paginate a table where the I need to see only a project assigned to me, how can I achieve this?

What I have is this

//My Task Pagination
$this->paginate = array(
    'Project' => array(
        'limit' => $limit,
        'contain' => array('ProjectReminderUser.user_id' => $this->Session->read('Auth.User.id')),
        'conditions' => array(
            'User.group_id' => $this->Session->read('Auth.User.group_id'),
            'Project.project_status_id' => PROJECT_STATUS_OPEN,
        ),
    )
);

$this->set('myTasks', $this->paginate('Project'));  
//debug($this->paginate('Project'));

but seems like the contain does not work at all. The result of debug looks like this

array(
    (int) 0 => array(
        'Project' => array(
            'id' => '9',
            'project_type_id' => '0',
            'contact_id' => '2',
            'company_id' => '6',
        ),
        'ProjectReminderUser' => array(
            (int) 0 => array(
                'id' => '11',
                'user_id' => '2',
                'project_id' => '9'
            )
        ),
    (int) 1 => array(
        'Project' => array(
            'id' => '1',
            'project_type_id' => '0',
            'contact_id' => '1',
            'company_id' => '3',
        ),
        'ProjectReminderUser' => array(
            (int) 0 => array(
                'id' => '2',
                'user_id' => '1',
                'project_id' => '1'
            ),
            (int) 1 => array(
                'id' => '1',
                'user_id' => '2',
                'project_id' => '1'
            )
        ),
    )
)

So what I would like to achieve is to have the pagination only show array index 1, because the ProjectReminderUser have user_id = 1 (a project that is assigned to me)

something like that. I tried to use containable but it does not work, maybe something wrong with the way I did it?

vicocamacho · Answer 1 · 2013-11-11T19:31:52.850

The conditions of your contain should go like this:

$this->paginate = array(
    'Project' => array(
        'limit' => $limit,
        'contain' => array(
            'ProjectReminderUser' => array(
                'conditions' => array(
                     'ProjectReminderUser.user_id' => $this->Session->read('Auth.User.id')
                )
             )
        ),
        'conditions' => array(
            'User.group_id' => $this->Session->read('Auth.User.group_id'),
            'Project.project_status_id' => PROJECT_STATUS_OPEN,
        )
    )
);

If you want to contain the User it should go like this:

$this->paginate = array(
    'Project' => array(
        'limit' => $limit,
        'contain' => array(
            'ProjectReminderUser' => array(
                'conditions' => array(
                     'ProjectReminderUser.user_id' => $this->Session->read('Auth.User.id')
                )
             ),
             'User' => array(
                 'conditions' => array(
                     'User.group_id' => $this->Session->read('Auth.User.group_id'),
                 )
             )
        ),
        'conditions' => array(
            'Project.project_status_id' => PROJECT_STATUS_OPEN,
        )
    )
);

Also make sure you have the Behavior in all the involved models.

If you need to use joins:

$this->paginate = array(
    'Project' => array(
        'limit' => $limit,
        'joins' => array(
            'table' => 'project_reminder_users',
            'alias' => 'ProjectReminderUser',
            'type' => 'inner',
            'conditions' => array(
                'ProjectReminderUser.user_id = ' . $this->Session->read('Auth.User.id'),
            )
        ),
        'conditions' => array(
            'User.group_id' => $this->Session->read('Auth.User.group_id'),
            'Project.project_status_id' => PROJECT_STATUS_OPEN,
        )
    )
);

@vicomacho Using this way, it will still return all the array, it's just ProjectReminderUser will be empty array, What should I do so the one with empty array do not get return as result? — Harts, Nov 11 '13 at 18:53
@Harts Unfortunately, that is intended behaviour of 'Containable'. What I've done is just specify my own joins (http://book.cakephp.org/2.0/en/models/associations-linking-models-together.html#joining-tables) within the paginate/find and it has made life much, much easier for these types of queries. — iso27002, Nov 11 '13 at 19:10
@iso27002 is right, updated my answer to show and example of how to do this. — vicocamacho, Nov 11 '13 at 19:35

Cakephp2 Contain Deeper Association

1 Answers1