Monday, March 14, 2011

Reading Related Tables As Nested Rows

Problem
A problem I used to face with relational databases is how to handle 1-to-many or many-to-many rows in SQL select statements. Let's say you have 2 tables with a many-to-many relationship between them, such as a Movies table and an Actors table. How do I present a list of movies together with their associated actors?

The naive way to do this is by using nested loops with a query for movies in the top loop and a query for actors in the second loop. In PHP it would look something like...

$moviesResult = mysql_query("SELECT id, title FROM movies"); 
while($moviesRow = mysql_fetch_assoc($result)) 
{ 
    echo("<h1>" . $moviesRow['title'] . "</h1>"); 

    echo("<ul>"); 
    $actorsResult = mysql_query("SELECT actors.name FROM actors INNER JOIN movies_actors ON actors.id = movies_actors.actorid WHERE movies_actors.movieid = " . $moviesRow['id'] . ";"); 
    while($actorsRow = mysql_fetch_assoc($actorsResult)) 
    { 
        echo("<li>" . $actorsRow['name'] . "</li>"); 
    } 
    echo("</ul>"); 
}

This approach however will kill your database. Ideally you should minimize the number of queries sent.

Another approach would be to join the movies table with the actors table and then read it with nested loops.

$result = mysql_query("SELECT movies.id AS id movies.title AS title, actors.name AS actor FROM movies INNER JOIN movies_actors ON movies.id = movies_actors.movie INNER JOIN actors ON actors.id = movies_actors.actor"); 
$row = mysql_fetch_assoc($result); 
while($row) 
{ 
    $currMovieId = $row['id']; 
    echo("<h1>" . $row['title'] . "</h1>"); 
    echo("<ul>"); 
    do 
    { 
        echo("<li>" . $row['name'] . "</li>"); 
    } while ($row = mysql_fetch_assoc($result) && $row['id'] == $currMovieId);
    echo("</ul>"); 
}

This works but as soon as you add another table to the join, determining which rows contain new information can be a nightmare, not to mention all the rows which just contain repeated information due to the cartesian product. For example:

IdTitleActorGenre
1The MatrixKeanu ReevesAction
1The MatrixKeanu ReevesAdventure
1The MatrixKeanu ReevesSci-Fi
1The MatrixLaurence FishburneAction
1The MatrixLaurence FishburneAdventure
1The MatrixLaurence FishburneSci-Fi
1The MatrixCarrie-Anne MossAction
1The MatrixCarrie-Anne MossAdventure
1The MatrixCarrie-Anne MossSci-Fi
2The Matrix ReloadedKeanu ReevesAction
2The Matrix ReloadedKeanu ReevesAdventure
2The Matrix ReloadedKeanu ReevesSci-Fi
2The Matrix ReloadedLaurence FishburneAction
2The Matrix ReloadedLaurence FishburneAdventure
2The Matrix ReloadedLaurence FishburneSci-Fi
2The Matrix ReloadedCarrie-Anne MossAction
2The Matrix ReloadedCarrie-Anne MossAdventure
2The Matrix ReloadedCarrie-Anne MossSci-Fi
3The Matrix RevolutionsKeanu ReevesAction
3The Matrix RevolutionsKeanu ReevesAdventure
3The Matrix RevolutionsKeanu ReevesSci-Fi
3The Matrix RevolutionsLaurence FishburneAction
3The Matrix RevolutionsLaurence FishburneAdventure
3The Matrix RevolutionsLaurence FishburneSci-Fi
3The Matrix RevolutionsCarrie-Anne MossAction
3The Matrix RevolutionsCarrie-Anne MossAdventure
3The Matrix RevolutionsCarrie-Anne MossSci-Fi

Are we supposed to receive all those rows just to display the below?

1The MatrixKeanu Reeves, Laurence Fishburne, Carrie-Anne MossAction, Adventure, Sci-Fi
2The Matrix ReloadedKeanu Reeves, Laurence Fishburne, Carrie-Anne MossAction, Adventure, Sci-Fi
3The Matrix RevolutionsKeanu Reeves, Laurence Fishburne, Carrie-Anne MossAction, Adventure, Sci-Fi

Solution
The best solution I found is a hybrid of the above 2 solutions. You make a different query for each table and then read all the rows returned by each query, placing the information in the list it belongs to.

$moviesResult = mysql_query("SELECT id, title FROM movies;"); 
$actorsResult = mysql_query("SELECT movies_actors.movieid AS movieid, actors.name AS name FROM actors INNER JOIN movies_actors ON actors.id = movies_actors.actorid;");
$genresResult = mysql_query("SELECT movies_genres.movieid AS movieid, genres.name AS name FROM genres INNER JOIN movies_genres ON genres.id = movies_genres.genreid;"); 

$actorRow = mysql_fetch_assoc($actorsResult); 
$genreRow = mysql_fetch_assoc($genresResult); 
while($movieRow = mysql_fetch_assoc($moviesResult)) 
{ 
    $currMovieId = $movieRow['id']; 
    echo("<h1>" . $movieRow['title'] . "</h1>"); 

    echo("<ul>"); 
    while ($actorRow && $actorRow['movieid'] == $currMovieId) 
    { 
        echo("<li>" . $row['name'] . "</li>"); 
        $actorRow = mysql_fetch_assoc($actorsResult); 
    } 
    echo("</ul>"); 

    echo("<ul>"); 
    while ($genreRow && $genreRow['movieid'] == $currMovieId) 
    { 
        echo("<li>" . $row['name'] . "</li>"); 
        $genreRow = mysql_fetch_assoc($genresResult); 
    } 
    echo("</ul>"); 
}

In this way we only make 3 queries, equal to the number of tables, and we only read as many rows as needed.

No comments:

Post a Comment