Add subquery to main query result instead of running lots of foreachs - Printable Version +- CodeIgniter Forums (https://forum.codeigniter.com) +-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20) +--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23) +--- Thread: Add subquery to main query result instead of running lots of foreachs (/showthread.php?tid=33260) |
Add subquery to main query result instead of running lots of foreachs - El Forum - 08-20-2010 [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" } 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 Add subquery to main query result instead of running lots of foreachs - El Forum - 08-20-2010 [eluser]JoostV[/eluser] You have every right to be worried about performance I reckon a join query here would solve the problem. Code: $this->db->from('discs'); Add subquery to main query result instead of running lots of foreachs - El Forum - 08-20-2010 [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. Add subquery to main query result instead of running lots of foreachs - El Forum - 08-20-2010 [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) { Add subquery to main query result instead of running lots of foreachs - El Forum - 08-20-2010 [eluser]gr0uch0mars[/eluser] Thanks again. I'll try it in some minutes. I'll tell you later if this works, although it seems good ;-) Add subquery to main query result instead of running lots of foreachs - El Forum - 08-20-2010 [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? |