php - codeigniter 3 连接表并获得一条记录

Question

我在使用 Codeigniter 3 进行 SQL JOIN 时遇到问题。数据库中有 3 个表。

电影表:

id    title                 image       ....
----  --------------------  ----------  
206   The Maltese Falcon    image-link  ..

流派表:

genres_id  genres_name
---------  -----------
1          Crime
2          Drama

电影流派表:

fg_id  film_id  genres_id
-----  -------  ---------
1      206      1
2      206      2

我的单片模型方法

public function getFilm($id){
    $this->db->select()
            ->from('film'); 
    $this->db->join('film_genres','film_genres.film_id = id','left');
    $this->db->join('genres','genres.genres_id = film_genres.genres_id','left');
    $this->db->where('id',$id);
    $this->db->group_by('id');
    $query = $this->db->get();
    $result = $query->result_array();
    return $result;

}

代码外

Array
(
    [0] => Array
        (
            [id] => 206
            [title] => Malta Kartali 
            [original_title] => The Maltese Falcon
            [year] => 1941
            [link] => http://www.imdb.com/title/tt0033870/
            [rating] => 8.1
            [directors] => 
            [writers] => 
            [stars] => 
            [musicians] => 
            [languages] => 
            [countries] => 
            [time] => 100
            [imdb_id] => tt0033870
            [image] => http://localhost/works/work/sipoyler.com/public/images/filmposter/tt0033870.jpg
            [slug] => the-maltese-falcon
            [date_added] => 2016-04-05 16:11:32
            [fc_id] => 31
            [film_id] => 206
            [genres_id] => 1
            [genres_name] => Crime
        )
)

只有一种类型从这个代码中得到，犯罪

我想像这样把犯罪和戏剧放在一起

Array
(
    [0] => Array
        (
            [id] => 206
            [title] => Malta Kartali 
            [original_title] => The Maltese Falcon
            [year] => 1941
            [link] => http://www.imdb.com/title/tt0033870/
            [rating] => 8.1
            [directors] => 
            [writers] => 
            [stars] => 
            [musicians] => 
            [languages] => 
            [countries] => 
            [time] => 100
            [imdb_id] => tt0033870
            [image] => http://localhost/works/work/sipoyler.com/public/images/filmposter/tt0033870.jpg
            [slug] => the-maltese-falcon
            [date_added] => 2016-04-05 16:11:32
            [fc_id] => 31
            [film_id] => 206
            [genres_id] => 1,2
            [genres_name] => Crime,Drama
        )
)

我该如何解决这个问题？

谢谢你..

Answer 1

 public function getFilm($id){

      $result = $this->db->query("SELECT a.*,b.*,GROUP_CONCAT(c.genres_name ORDER BY c.genres_name ASC SEPARATOR ', ') AS genres_name ,GROUP_CONCAT(c.genres_id ORDER BY c.genres_id ASC SEPARATOR ', ') AS genres_id  FROM film_genres a
                                 LEFT JOIN film b ON a.film_id = b.id
                                 LEFT JOIN genres c ON a.genres_id = c.genres_id
                                 WHERE a.film_id=$id")->row();
      return $result;

    }