Best Workflow For Getting Information From A Related Table

I have a site that displays movies. I am displaying the movie details on a page with the name, price, and genre of the movie. The name and price are easily pulled from the db since they are directly inside the movie table. For the genres, i had to create two more tables, a genre table which holds all the names of the genres and a movie_genre table which has the records of which genre’s belong to which movies.



| id | name | price |


| 1 | 127 hours | 9.99 |


| 2 | Batmas | 5.99 |




| id | name |


| 1 | Action |


| 2 | Adventure |




| id | movie_id | genre_id |


| 1 | 1 | 1 |


| 2 | 2 | 1 |


| 3 | 2 | 2 |


On the details page(site/index view) I need to get the genre name for a specific movie, so I first need to loop through the movies, then inside each iteration of the loop I get all the genre id’s for that current movie from the movie_genre table. Then I loop through the array of those returned genre ids to get the actual genre names from the genre table. The code is working as is, but it seems sloppy to me. I am fairly new to OOP and Yii, so i’m assuming there is a better way to go about doing this. Also, I don’t necessarily think all of these loops should be in the view code, but instead in the controller, yet I can’t figure out a way to make that work. Does anyone have any suggestions how I can go about this in a more efficient manner?


public function actionIndex() {

  $movie = Movie::model()->findAll();






<?php foreach($movie as $movie) { ?>

  <div class="details">

    <h2><?php echo $movie->name; ?></h2>

    <h3>$<?php echo 'Price: ' . $movie->price; ?></h3>



                //Getting all the genre id's for the given movie from the movie_genre table

                $criteria = new CDbCriteria();



                $movie_genre_ids = MovieGenre::model()->findAll($criteria);

                $genre_names = '';

                foreach($movie_genre_ids as $movie_genre_ids) {

                    //Getting the names for the genres that were returned in the last results array

                    $criteria = new CDbCriteria();



                    $genre_name = Genre::model()->find($criteria);

                    $genre_names .= $genre_name->name . ', ';


                    //Echoing out the genre names and removing the trailing comma and space

                    echo rtrim($genre_names, ', ');




<?php } ?>

First of all, you’re posting to a wrong forum. This one is for version 2.

Next, use relation eager loading approach, you can find info on it here and here.