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' => ''
)
);
}
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)
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
$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'));