CakePHP find() and related models and conditions

Question

I am calling find() on my Game model in order to get the reviews of that game for a particular platform. The problem is that the find() call doesn't seem to be paying attention to my conditions array. The call returns all the reviews for a game regardless of the platform_id.

I have these model associations:

• User hasMany Review
• Game hasMany Review
• Review belongsTo Game
• Review belongsTo User
• Game HABTM Platform

This is the find call that I created:

    $options = array(
        'group' => array(
            'Game.id'
        ),
        'joins' => array(
            array(
                'table' => 'reviews',
                'type' => 'LEFT',
                'conditions' => array(
                    'Game.id=reviews.game_id'
                )
            ),
            array(
                'table' => 'platforms',
                'type' => 'LEFT',
                'conditions' => array(
                    'reviews.platform_id=platforms.id'
                )
            ),
            array(
                'table' => 'users',
                'type' => 'LEFT',
                'conditions' => array(
                    'reviews.user_id=users.id'
                )
            )
        ),
        'fields' => array(
            'Game.id', 'Game.name',
            'platforms.id', 'platforms.name',
            'users.id', 'users.username'
        ),
        'conditions' => array(
            'AND' => array(
                'NOT' => array('reviews.review_text' => NULL),
                'platforms.id' => $pid,
            )
        ),
        'limit' => 5
    );

    $this->Game->find('all', $options);

Here is a sample return for a find() call for platform_id=2:

array(
(int) 0 => array(
    'Game' => array(
        'id' => '58',
        'name' => 'Bioshock 2'
    ),
    'platforms' => array(
        'id' => '2',
        'name' => 'PlayStation 3'
    ),
    'users' => array(
        'id' => '20',
        'username' => 'pspmaniac'
    ),
    'Review' => array(
        (int) 0 => array(
            'id' => '32',
            'review_text' => 'This is the PC review.',
            'score' => '4',
            'user_id' => '20',
            'game_id' => '58',
            'created' => '2013-04-30 19:59:40',
            'platform_id' => '10'
        ),
        (int) 1 => array(
            'id' => '33',
            'review_text' => 'This is the PS3 review.',
            'score' => '7',
            'user_id' => '20',
            'game_id' => '58',
            'created' => '2013-04-30 20:00:04',
            'platform_id' => '2'
        ),
        (int) 2 => array(
            'id' => '34',
            'review_text' => 'This is the XBOX 360 review.',
            'score' => '6',
            'user_id' => '20',
            'game_id' => '58',
            'created' => '2013-04-30 20:00:22',
            'platform_id' => '1'
        )
    )
)
);

In the 'Review' index, it returns three reviews for the game (platform ids 10, 2, and 1) when it should only return the review with platform_id=2.

This is my reviews table:

CREATE TABLE reviews
(
   id mediumint unsigned not null auto_increment,
   review_text mediumint,
   score int not null,
   user_id mediumint unsigned not null,
   game_id mediumint unsigned not null,
   PRIMARY KEY (id),
   FOREIGN KEY (user_id) REFERENCES users (id),
   FOREIGN KEY (game_id) REFERENCES games (id)
);

So, in the end, the only thing wrong with the returned array is that it contains reviews not only for the condition 'platforms.id=2' but for all platform ids.

Answer 1

Solution

Using Dave's comment, I came up with this solution to my problem using containable:

$this->loadModel('Review');
$this->Review->find('all', array( 'contain' => array('Platform'), 'conditions' => array( 'Review.platform_id' => 2 )
));