我对Codeigniter 3的SQL JOIN有问题。数据库中有3个表。
胶片表：
id title image ....
---- -------------------- ----------
206 The Maltese Falcon image-link ..
流派表：
genres_id genres_name
--------- -----------
1 Crime
2 Drama
Film_types表：
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
)
)
我该如何解决这个问题？
谢谢。。
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;
}
试试这个
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;
}