Welcome Guest, Not a member yet? Register   Sign In
Add subquery to main query result instead of running lots of foreachs
#1

[eluser]gr0uch0mars[/eluser]
This is what I HAVE:
I want to show a Disc, with its Songs, and each Version of the songs: Disc > Song > Versions
So far, I have a songs query on my controller that gets all Songs of a Disc. That's OK.
Then I pass it to a view and there I do:
Code:
foreach($songs_query->result() as $song_row) { "search Versions of this song" }
I'm worried about performance, as could be lots of queries to run on each song (12 songs/disc, 4 takes/song = 12*4 queries to see version info)

My QUESTION is this:
Could I alter the songs_query like altering an array to do a single (or at least less) query?

I don't know much about handling this, so any help would be appreciated! Thanks
#2

[eluser]JoostV[/eluser]
You have every right to be worried about performance Smile

I reckon a join query here would solve the problem.

Code:
$this->db->from('discs');
$this->db->join('songs', 'discs.id = songs.disc_id', 'left');
$this->db->join('versions', 'songs.id = versions.song_id', 'left');
$this->db->where(discs.id, 1);
$versions = $this->db->get();
#3

[eluser]gr0uch0mars[/eluser]
Thanks, I had thought about a JOIN, but this would return only "Versions".
I'd like to present something like:

Disc
- Song 1
· Version 1
· Version 2
· Version 3
- Song 2
· Version 1
- Song 3
· Version 1
· Version 2

I don't know how to group Versions (fetched through your JOIN) into Songs.
#4

[eluser]JoostV[/eluser]
You should be able to just loop through the results and print them out in your view file, something like this (I did not test this code)

Code:
if (count($versions) > 0) {
    // Store all songs and versions in a nested aray that we can easily print out
    $songs = array();
    foreach ($versions as $version) {
          $songs[$version['song_title']][$version['version_title']] = $version['version_title'];
    }

    // Print out the songs and versions form array
    echo '<h1>'.$versions[0]['disc_title'].'</h1>';
    echo ul($songs);
}
else {
    // Oops, no songs and versions found...
    echo '<h1>Disc not found</h1>';
    echo 'Sorry, this disc does not exist or it doesn not contain any songs';
}
#5

[eluser]gr0uch0mars[/eluser]
Thanks again. I'll try it in some minutes. I'll tell you later if this works, although it seems good ;-)
#6

[eluser]gr0uch0mars[/eluser]
You know what, JoostV? It worked!
And as perfect as I liked. Thank you very much!

I don't work with arrays very often, so every time I have to do it, I don't remember anything. This time I'll keep what you've taught me for future references. :-)

Case solved!

One last thing: I tried to change it to use it like object instead of array (because I'm more accustomed to object-style), and I don't know how to change this:
Code:
echo '<h1>'.$versions[0]['disc_title'].'</h1>';

Do you know how to do it?




Theme © iAndrew 2016 - Forum software by © MyBB