CodeIgniter Forums
mysql union all? - 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: mysql union all? (/showthread.php?tid=19081)



mysql union all? - El Forum - 05-27-2009

[eluser]newbie boy[/eluser]
this code works perfectly in mysql..

how will i use this in CI?

or better yet CI using the Doctrine DQL?

Code:
SELECT id, user_id, music_id, comment, createdat FROM tbl_user_music_comment
UNION ALL
SELECT null AS id, user_id, music_id, null AS comment, createdat FROM tbl_user_music_like
UNION ALL
SELECT null AS id, user_id, music_id, null AS comment, createdat FROM tbl_user_music_listen
UNION ALL
SELECT null AS id, user_id, music_id, null AS comment, createdat FROM tbl_user_music_download
UNION ALL
SELECT null AS id, user_id, music_id, null AS comment, createdat FROM tbl_user_music_playlist
UNION ALL
SELECT null AS id, user_id, music_id, null AS comment, createdat FROM tbl_user_music_recommend
ORDER BY createdat DESC

thanks...


mysql union all? - El Forum - 05-27-2009

[eluser]TheFuzzy0ne[/eluser]
Just use that as a string, and pass it to $this->db->query().


mysql union all? - El Forum - 05-27-2009

[eluser]Evil Wizard[/eluser]
Code:
$objQuery = Doctrine_Query::create()
                              ->select('id, user_id, music_id, comment, createdat')
                              ->from('tbl_user_music_comment c')
                              ->where('UNION ALL
SELECT null AS id, user_id, music_id, null AS comment, createdat FROM tbl_user_music_like
UNION ALL
SELECT null AS id, user_id, music_id, null AS comment, createdat FROM tbl_user_music_listen
UNION ALL
SELECT null AS id, user_id, music_id, null AS comment, createdat FROM tbl_user_music_download
UNION ALL
SELECT null AS id, user_id, music_id, null AS comment, createdat FROM tbl_user_music_playlist
UNION ALL
SELECT null AS id, user_id, music_id, null AS comment, createdat FROM tbl_user_music_recommend')
                               ->orderBy('createdat DESC')
                               ->execute();
that should do it for use in DQL but you may have to tweak the field/table names to reflect how Doctrine sees them