• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
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


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.