加入后分组结果？ [英] Group results after join?

问题描述

所以，我有三张桌子。 电影， movies_genres 和类型。我想通过它的Id获得一部电影，并在结果中加入它的类型。我设法加入了结果，但它不会显示为我想要的结果。我不确定我所问的是否可能。

这是我的查询：

<$ p $ （genres.id）AS genre_id，GROUP_CONCAT（genres.name）AS genre_name
FROM（`movies`）
INNER JOIN`movies_genres `
ON`movies_genres`.`movie_id` =`movies`.`id`
INNER JOIN`genres`
ON`genres`.`id` =`movies_genres`.`genre_id` WHERE`movies`.`id` = 19908
GROUP BY`movies`.`id`

<这个查询是由Codeigniters的Active Record类生成的，这里是Codeigniter的代码（如果有帮助的话）：

  $ this-> db-> select（'movies。*，GROUP_CONCAT（genres.id）AS genre_id，GROUP_CONCAT（genres.name）AS genre_name'）; （'movies'）; 
 $ this-> db-> 
 $ this-> db-> where（'movies.id'，$ movie_id）; 
 $ this-> db-> join（'movies_genres'，'movies_genres.movi​​e_id = movies.id'，'inner'）; 
 $ this-> db-> join（'genres'，'genres.id = movies_genres.genre_id'，'inner'）; 
 $ this-> db-> group_by（'movies.id'）; 
  

这是我目前得到的结果：

 阵列
（
 [id] => 19908 
 [movie_title] => Zombieland 
 [概述] => ;一个容易吓倒的家伙...... 
 [genre_id] => 28,12,35,27 
 [genre_name] =>动作，冒险，喜剧，恐怖
）
  

这就是我想要的：

  Array 
（
 [id] => 19908 
 [movie_title] => Zombieland 
 [overview] =>一个容易被吓坏的家伙。 ... 
 [genres] => array（
 0 => array（
'id'=> 28，
'name'=> Action 
 
 1 =>数组（
'id'=> 12，
'name'=> Adventure 
），
 1 =>数组（
'id'=> 35，
'name'=>喜剧
），
 1 =>数组（
' id'=> 27，
'name'=>恐怖
）
）
）
  

这是否可能，以及如果是这样，怎么做？

您列出的查询将包含 n 行（其中 n =电影数量），而你想要的查询将会有更多的行（movie_genre条目的＃）。你可能最好离开这个查询，并进行一些后期处理。

考虑：

获得后，只需运行你的结果（如 $ result ）数组： > foreach（$ result as& $ row）
{
//逗号分割
$ gi_elements = explode（'，'，$ row ['genre_id' ]）;
$ gn_elements = explode（'，'，$ row ['genre_name']）;

//构建流派
$ row ['genre'] = array（）;
for（$ i = 0; $ i< count（$ gi_elements）; $ i ++）
{
$ row ['genre'] [] = array（'id'=> $ gi_elements [$ i]，'name'=> $ gn_elements [$ i]）;
}

//清理
unset（$ row ['genre_id']）;
unset（$ row ['genre_name']）;
}


之后，$结果看起来完全如您所愿，无需额外的数据库工作。 / p>

编辑： 修正了一些错别字。

So, I have three tables. Movies, movies_genres and genres. I want to get a movie by its Id, and also join its genres in the result. I managed to join the results, but it doesn't display as i want it to. I'm not sure if what I'm asking is possible.

This is my query:

SELECT `movies`.*, GROUP_CONCAT(genres.id) AS genre_id, GROUP_CONCAT(genres.name) AS genre_name
FROM (`movies`)
INNER JOIN `movies_genres`
    ON `movies_genres`.`movie_id` = `movies`.`id`
INNER JOIN `genres`
    ON `genres`.`id` = `movies_genres`.`genre_id` WHERE `movies`.`id` = 19908
GROUP BY `movies`.`id`

The query was generated by Codeigniters Active Record class, here is the Codeigniter code if that helps:

$this->db->select('movies.*, GROUP_CONCAT(genres.id) AS genre_id, GROUP_CONCAT(genres.name) AS genre_name');
$this->db->from('movies');
$this->db->where('movies.id', $movie_id);
$this->db->join('movies_genres', 'movies_genres.movie_id = movies.id', 'inner');
$this->db->join('genres', 'genres.id = movies_genres.genre_id', 'inner');
$this->db->group_by('movies.id');

Here is the result i'm currently getting:

Array
(
    [id] => 19908
    [movie_title] => Zombieland
    [overview] => An easily spooked guy...
    [genre_id] => 28,12,35,27
    [genre_name] => Action,Adventure,Comedy,Horror
)

And this is what I want:

Array
(
    [id] => 19908
    [movie_title] => Zombieland
    [overview] => An easily spooked guy...
    [genres] => array(
        0 => array(
            'id' => 28,
            'name' => Action
        ),
        1 => array(
            'id' => 12,
            'name' => Adventure
        ),
        1 => array(
            'id' => 35,
            'name' => Comedy
        ),
        1 => array(
            'id' => 27,
            'name' => Horror
        )
    )
)

Is this possible, and if so, how?

解决方案

The query you listed will have n rows (where n = # of movies) whereas the query it seems you want will have many more rows (# of movie_genre's entries). You're probably better off leaving that query as it is, and doing some post processing.

Consider:

After you get it, just run your result (e.g. $result) array through something like:

foreach($result as &$row)
{
    // Split over commas
    $gi_elements = explode(',', $row['genre_id']);
    $gn_elements = explode(',', $row['genre_name']);

    // Build genre 
    $row['genre'] = array();
    for($i=0; $i<count($gi_elements); $i++)
    {
        $row['genre'][] = array('id' => $gi_elements[$i], 'name' => $gn_elements[$i]);
    }

     // Cleanup
     unset($row['genre_id']);
     unset($row['genre_name']);
}

Afterwards, $results will look exactly as you wish without extra database work.

EDIT: Fixed some typos.

这篇关于加入后分组结果？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！