Adding conditions to Containable in CakePHP

Question

Previously I was relying on recursive, but I didn't get solution for some, then I found that Containable works fine for these.

I am developing a movie review website. In that I need to show the list of movies which is related to a particular Genre.

I have this below code:

//example
$genre = "drama";

$options = array(
    'contain' => array(
        'Movie',
        'MoveiGenre.Genre' => array(
            'conditions' => array('MovieGenre.Genre.name = "'.$genre.'"')
        ),
        'MovieGenre.Genre.name'
    ),
    'recursive' => 2,
    'limit' => 10
);
$this->paginate = $options;
$this->set('movies',$this->paginate());

The real problem starts here, I get all the movies, even if its not related to the genre "drama". Where am I going wrong ?

Let me explain the database table:

Table: movies

 ----------------------------
 | id | title | description |
 ----------------------------
 | 1  | mov1  | something1  |
 | 2  | mov2  | something2  |
 | 3  | mov3  | something3  |
 ----------------------------

Table: genres

 ---------------
 | id | name   |
 ---------------
 | 1  | drama  |
 | 2  | sci-fi |
 | 3  | comedy |
 ---------------

Table: movie_genres

 -------------------------------
 | id | movie_id | genre_id    |
 -------------------------------
 | 1  | 1        | 1           |
 | 2  | 1        | 2           |
 | 3  | 2        | 2           |
 -------------------------------

Here you can see that one movie_id has multiple genre_id. I should get only mov1 but I'm getting both movies in an array.

~~ EDIT ~~ oops!! sorry forgot to mention, I'm using this code in MoviesController. All 3 table has respective controller. So pls suggest me in which controller I can use.

EDIT:2

class Movie extends AppModel {
    public $displayField = 'title';

    public $actsAs = array('Containable');

    public $hasMany = array(
        'MovieGenre' => array(
            'className' => 'MovieGenre',
            'foreignKey' => 'movie_id',
            'dependent' => false,
            'conditions' => '',
            'fields' => '',
            'order' => '',
            'limit' => '',
            'offset' => '',
            'exclusive' => '',
            'finderQuery' => '',
            'counterQuery' => ''
        ),
        'MovieLanguage' => array(
            'className' => 'MovieLanguage',
            'foreignKey' => 'movie_id',
            'dependent' => false,
            'conditions' => '',
            'fields' => '',
            'order' => '',
            'limit' => '',
            'offset' => '',
            'exclusive' => '',
            'finderQuery' => '',
            'counterQuery' => ''
        ),
    );

}

Model: Genre

class Genre extends AppModel {

    public $displayField = 'name';

    public $hasAndBelongsToMany = array(
        'Movie' => array(
            'className' => 'Movie',
            'joinTable' => 'movie_genres',
            'foreignKey' => 'genre_id',
            'associationForeignKey' => 'movie_id',
            'unique' => 'keepExisting',
            'conditions' => '',
            'fields' => '',
            'order' => '',
            'limit' => '',
            'offset' => '',
            'finderQuery' => '',
            'deleteQuery' => '',
            'insertQuery' => ''
        )
    );

}

Model: MovieGenre

class MovieGenre extends AppModel {

    public $belongsTo = array(
        'Movie' => array(
            'className' => 'Movie',
            'foreignKey' => 'movie_id',
            'conditions' => '',
            'fields' => '',
            'order' => ''
        ),
        'Genre' => array(
            'className' => 'Genre',
            'foreignKey' => 'genre_id',
            'conditions' => '',
            'fields' => '',
            'order' => ''
        )
    );
}

Answer 1

TLDR: do your find on Genre and contain it's Movies, or use joins() - doing your search on Movies and containing Genre with conditions won't work for the results you want.

---

Explanation:

Below is your corrected 'contain' code, but more importantly, doing a 'contain' on Genre won't return the results you're looking for.

What it does - limits the contained genres based on your condition... so it will pull ALL movies, and contain the genres that match $genre.

---

Solutions (depending on your needs):

Solution 1)

• Do a find() on Genre, with the condition, and contain it's movies. This will pull the genre that matches, then only the movies that are related to it.

Solution 2) - the one I'd recommend

• Use 'joins()':

---

$conditions = array();
$conditions['joins'] = array(
    array(
        'table' => 'genres_movies', //or movie_genres if you've specified it as the table to use
        'alias' => 'GenresMovie',
        'type' => 'INNER'
        'conditions' => array(
            'GenresMovie.movie_id = Movie.id'
        )
    ),
    array(
        'table' => 'genres',
        'alias' => 'Genre',
        'type' => 'INNER',
        'conditions' => array(
            'Genre.id = GenresMovie.genre_id',
            'Genre.name = "' . $genre . '"'
        )
    )
);
$this->Movie->find('all', $conditions);

---

Your edited (corrected imo) 'contain' example

//edited example
$genre = "drama";

$options = array(
    'contain' => array(
        'Genre' => array(
            'conditions' => array('Genre.name' => $genre)
        )
    ),
    'recursive' => -1,
    'limit' => 10
);
$this->paginate = $options;
$this->set('movies', $this->paginate('Movie'));

1. you don't "contain" the model you're doing the find on (ie I removed 'Movie' from your contain array).
2. you only contain models, not things like "MovieGenre.Genre" (I can't think of any time you would use '.' concatenated models)
3. recursive needs to be -1 to use Containable - you should set this to -1 in the AppModel and forget the concept of recursive - it's bad