Is it possible to recreate this complex MySQL statement using the ActiveRecord pattern in CI?

[eluser]Marcus Hodges[/eluser]
I have this complicated MySQL statement in the project I'm working on to learn CI, but I haven't got a clue how I'd convert it to ActiveRecord methods. I can do $this->db->query($sql), but I'd love to learn more about how to handle these things more gracefully inside CI. Any suggestions?

Here's the SQL:

Code:

SELECT t.id, t.tag, COALESCE(bt.count, 0) AS count
FROM tags AS t
LEFT OUTER JOIN (
    SELECT tag_id, COUNT(*) as count
    FROM books_tags
    GROUP BY tag_id
) AS bt
ON t.id = bt.tag_id
ORDER BY count DESC, tag ASC

Thanks much,
Marcus

[eluser]techgnome[/eluser]
Oh, easily... and it's not as hard as you might think. Here's how I went about it... built the query using mySQL Query tool... tweaked it until I got the data I wanted... then copied the query and pasted it into my model class. Then I wrap it in comment /* */ tags so that the PHP will ignore it... then I copy each line of the query, converting it to the AQ version as needed.

Keeping the original query helped because I have several that are similar, and yet just different enough. Also, if I need to add something, I have the original query to base it off of.

Any ways, here's a real, live example of the query:

Code:

SELECT albumlist.id albumid, albumlist.album, albumlist.albumyear, albumlist.label, albumlist.genre,
alb.artistid, artistlist.website, count(song_album_artist.songid) songcount,
CASE WHEN alb.artistid = -1 THEN 'Various Artists' ELSE artistlist.artist END artist,
CASE WHEN songlist.picture = '' THEN 'na_01.jpg' ELSE songlist.picture END albumcover, DATE(songlist.date_added) date_added
FROM song_album_artist
INNER JOIN songlist ON song_album_artist.songid = songlist.id
INNER JOIN (SELECT albumid, CASE WHEN COUNT(DISTINCT artistid) > 1 THEN -1 ELSE artistid END artistid
    FROM song_album_artist
    GROUP BY albumid) alb
ON song_album_artist.albumid = alb.albumid
INNER JOIN albumlist ON alb.albumid = albumlist.id
LEFT JOIN artistlist ON alb.artistid = artistlist.id
WHERE albumlist.album LIKE '%'
GROUP BY albumlist.id , albumlist.album, albumlist.albumyear, albumlist.label, albumlist.genre,
alb.artistid, artistlist.website,
CASE WHEN alb.artistid = -1 THEN 'Various Artists' ELSE artistlist.artist END,
CASE WHEN songlist.picture = '' THEN 'na_01.jpg' ELSE songlist.picture END, DATE(songlist.date_added)
ORDER BY songlist.album

and here's the AQ version it converted into:

Code:

$this->db->select('albumlist.id albumid, albumlist.album, albumlist.albumyear, albumlist.label, albumlist.genre');
        $this->db->select('alb.artistid, artistlist.website, count(song_album_artist.songid) songcount');
        $this->db->select("CASE WHEN alb.artistid = -1 THEN 'Various Artists' ELSE artistlist.artist END artist", FALSE);
        $this->db->select("CASE WHEN songlist.picture = '' THEN 'na_01.jpg' ELSE songlist.picture END albumcover", FALSE);
        $this->db->select("DATE(songlist.date_added) date_added", FALSE);
        $this->db->from('song_album_artist');
        $this->db->join('songlist', 'song_album_artist.songid = songlist.ID', 'inner');
        $this->db->join('(SELECT albumid, CASE WHEN COUNT(DISTINCT artistid) > 1 THEN -1 ELSE artistid END artistid FROM song_album_artist GROUP BY albumid) alb', 'song_album_artist.albumid = alb.albumid', 'inner');
        $this->db->join('albumlist', 'alb.albumid = albumlist.id', 'inner');
        $this->db->join('artistlist', 'alb.artistid = artistlist.ID', 'left');
        $this->db->like('albumlist.album', $searchfor);
        $this->db->group_by(array('albumlist.id', 'albumlist.album', 'albumlist.albumyear',
                                    'albumlist.label', 'albumlist.genre','alb.artistid', 'artistlist.website',
                                    'artistlist.id', "CASE WHEN alb.artistid = -1 THEN 'Various Artists' ELSE artistlist.artist END",
                                    'artistlist.website', "CASE WHEN songlist.picture = '' THEN 'na_01.jpg' ELSE songlist.picture END",
                                    'DATE(songlist.date_added)'));
                                    
        $this->db->order_by('albumlist.album', 'ASC');

EEEEP! As for complexity levels, it's about the middle of the road of what I'm doing.... but it shows nearly every db command needed... select... from... where.... like... group by.... order by.... and shows sub queries, aggregate, and even a SELECT CASE in there.

Did this help at all? or did it just confuse things even more?

-tg