CakePHP containable conditions not limiting results?

Question

I'm trying to find a User's grocery items in a categorized list. The associations are Category hasMany Item hasMany User through Grocery. I'm using the Containable Behavior and it is not filtering out all other Grocery. It returns every item basically.

My controller function:

function showlist() {
$categories = $this->Category->find('all', array(
    'contain' => array(
        'Item' => array(
            'Grocery' => array(
                'conditions' => array(
                    'Grocery.user_id =' => $this->Auth->user('id')
                )
            )
        )
    )
));

Array that's returned:

Array
(
    [0] => Array
        (
            [Category] => Array
                (
                    [id] => 10
                    [parent_id] => 
                    [name] => Dairy
                    [lft] => 1
                    [rght] => 6
                )

            [Item] => Array
                (
                )

        )

[1] => Array
    (
        [Category] => Array
            (
                [id] => 11
                [parent_id] => 10
                [name] => Milk
                [lft] => 2
                [rght] => 3
            )

        **[Item] => Array
            (
            )**

    )

[2] => Array
    (
        [Category] => Array
            (
                [id] => 12
                [parent_id] => 10
                [name] => Cheese
                [lft] => 4
                [rght] => 5
            )

        [Item] => Array
            (
            )

    )

[3] => Array

I don't want to return any Item arrays that don't have a Grocery(ItemUser) below them.

This works but now it only displays flatly. I need it to show all items of one category in that nested array.

It's showing:

Array
(
[0] => Array
    (
        [Category] => Array **<--This could be the same category as**
            (...)

        [Item] => Array
            (...)

        [Grocery] => Array
            (...)
    )
[1] => Array
    (
        [Category] => Array **<--This**
            (...)

        [Item] => Array
            (...)

        [Grocery] => Array
            (...)
    )
[2]...

And I need it to be:

Array
(
[0] => Array
    (
        [Category] => Array 
            (...
            [Item] => Array
            (...)

            [Item] => Array
            (...)
            )
    )
[1] => Array
    (
        [Category] => Array
            (...)

        [Item] => Array
            (...)
    )
[2]...

Don't even really need the Grocery array, just have to search thru them for criteria.

Dave · Accepted Answer · 2013-04-26T14:22:54.317

6

You cannot limit your main Model's results based on conditions within the Contain. When you use contain(), it actually creates multiple queries - that keeps you from being able to limit based on contained conditions.

To resolve this, you need to use JOINs [ see CakePHP Joining Tables ] instead of contain.

edited Apr 26 '13 at 14:22

answered Mar 30 '13 at 18:31

Dave

28,833
23
113
183

This works but I'm still having some issues. I've noted them in an edit above. – Chris Mar 31 '13 at 04:03
That would be another question, not an addition to this one. "My Join Returns Data In Strange Format" or something. – Dave Mar 31 '13 at 05:22
Have you tried reformatting the data using http://book.cakephp.org/2.0/en/core-utility-libraries/hash.html? – Chuck Burgess Apr 26 '13 at 14:22
@ChuckBurgess - I didn't see the need. – Dave Apr 26 '13 at 14:24
Sorry @Dave for the poor communication, I should have indicated my comment was for Chris to get the array formated as expected. Your JOINs solution is perfect. But if Chris wants them in a different format, the HASH solution will work. – Chuck Burgess Apr 26 '13 at 16:36
@ChuckBurgess - gotcha. That kind of comment is great, but should be on the question, not an answer :) – Dave Apr 26 '13 at 16:41

score 0 · Answer 2 · answered Apr 02 '13 at 13:15

If you want nested formatting, could you move all this to another (e.g Groceries) controller which would allow you to limit by user_id or (but I have to admit that I'm not sure whether this is good Cake practice, although I have successfully used this approach...in 1.3 anyway) something along the lines of:

$groceries = $this->Category->Item->Grocery->find('all', array(
    'conditions' => array(
         Grocery.user_id =' => $this->Auth->user('id')
    )
));

CakePHP containable conditions not limiting results?

2 Answers2

Linked