php - 从mysql中的多个表获取多个值？

Question

我只是做了一个音乐在线网站。但是当我尝试获取用户按创建时间顺序创建的所有轨道、播放列表、专辑时，我陷入了困境。我有 4 个表，包括:用户、轨道、专辑、播放列表，如下所示:

+---------------+------------------+
| Field         | Type             | 
+---------------+------------------+
| album_id      | int(10) unsigned |
| user_id       | int(10) unsigned |
| album_title   | varchar(255)     |
| album_created | datetime()       |

+---------------+------------------+
| Field         | Type             | 
+---------------+------------------+
| playlist_id   | int(10) unsigned |
| user_id       | int(10) unsigned |
| playlist_title| varchar(255)     |
| plist_created | datetime()       |

+---------------+------------------+
| Field         | Type             | 
+---------------+------------------+
| track_id      | int(10) unsigned |
| user_id       | int(10) unsigned |
| track_title   | varchar(255)     |
| track_created | datetime()       |

+---------------+------------------+
| Field         | Type             | 
+---------------+------------------+
| user_id      | int(10) unsigned |
| user_name    | varchar(255      |
| password     | varchar(255)     |
| email        | varchar(255      |

这是我的代码，但它只是获取 1 个数组中的所有轨道、专辑、播放列表

return Yii::app()->db->createCommand()
        ->select('u.*,t.*,p.*,a.*')
        ->from('user u ')
        ->join('track t', 'u.user_id = s.user_id')
        ->leftjoin('playlist p', 'u.user_id = p.user_id')
        ->leftjoin('album a', 'u.user_id = a.user_id')
        ->andWhere('t.user_id ='.$id.' OR a.user_id ='.$id.' OR p.user_id ='.$id )
        ->queryAll(); 

我想要像这样的例子的结果:

[0] => array([album_id] => 11 , [user_id] => 1 , [album_title] => aaa ,[album_created] => 2014-06-03 )
[1] => array([song_id] => 13 , [user_id] => 1 , [song_title] => aaa ,[song_created] => 2014-06-02 )
[2] => array([song_id] => 22 , [user_id] => 1 , [song_title] => zyx ,[song_created] => 2014-05-30 )
[3] => array([playlist_id] => 1 , [user_id] => 1 , [playlist_title] => xxxx ,[playlist_created] => 2014-05-25 )

Answer 1

要获得所需的输出，您需要的是 UNION轨道、播放列表和专辑的内容类似于:

   id   |   user_id   |   type   |   title   |   created   
--------|-------------|----------|-----------|-----------

在您的情况下，

type 是album、playlist 或song。这可以使用 CDbCommand::union() 来实现和 CDbCommand::getText()如下:

return Yii::app()->db->createCommand()
    ->select("a.album_id, a.user_id, 'Album' as type, a.album_title, a.album_created as created")
    ->from('album a')
    ->andWhere('a.user_id ='.$id)
    ->union(
        Yii::app()->db->createCommand()
            ->select("s.song_id, s.user_id, 'Song' as type, s.song_title, s.song_created as created")
            ->from('song s')
            ->andWhere('s.user_id ='.$id)
            ->getText()
    )->union(
        Yii::app()->db->createCommand()
            ->select("p.playlist_id, p.user_id, 'Playlist' as type, p.playlist_title, p.plist_created as created")
            ->from('playlist p')
            ->andWhere('p.user_id ='.$id)
            ->getText()
    )->queryAll();

为了让生活更轻松，你应该从工会的角度提出看法。经过一些调整，您也许可以将 CActiveRecord(以及 relations() 和 CActiveDataProvider)与此 View 一起使用。