Welcome Guest, Not a member yet? Register   Sign In
mysql union all?
#1

[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...
#2

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

[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




Theme © iAndrew 2016 - Forum software by © MyBB