在连接(代码点火器）中获取多行 - Fetch multiple rows in join (codeigniter)

Fetch multiple rows in join (codeigniter)

本文关键字：获取点火器连接代码 | 更新日期: 2023-09-27

我无法从下面的代码中获得我期望的结果。

第一步是：

从一个表中获取一行，从另一个表中连接多行...RSULT 数组应如下所示：

array(
  field_1, 
  field_2, 
  field_3, 
  joined_array(
    field_a_array(
      field_a_a, 
      field_a_b, 
      field_a_c
    ), 
    field_b_array(
      field_b_a, 
      field_b_b, 
      field_b_c
    )
  ) 
)

我的查询如下所示：

（似乎位置、加入等位置对于 Codigniters DB 类并不重要）

$this->db->select('events.*, genres_x_events.*');
$this->db->from('events');
$this->db->where('events.slug', $slug);
$this->db->where('events.deleted', 0);
$this->db->join('genres_x_events', 'genres_x_events.event_slug = events.slug');
$query = $this->db->get();

我

面临的问题是，当我期望每个联接检索多行时，我只从联接中获取一行。

编辑：

last_query（）：

SELECT `events`.*, `genres_x_events`.`genre_slug`
FROM (`events`)
LEFT OUTER JOIN `genres_x_events` ON `genres_x_events`.`event_slug` = `events`.`slug`
WHERE `events`.`slug` =  'test'
AND `events`.`deleted` =  0

输出：

Array(
    [0] => Array
        (
            [id] => 25
            [headline] => test
            [subheadline] => 
            [slug] => test
            [date] => 2012-08-10
            [start_time] => 00:00:00
            [end_time] => 00:00:00
            [price] => 
            [body] => 
            [location_id] => 5
            [genre_id] => 0
            [creation_date] => 2012-08-10 14:26:33
            [update_date] => 2012-08-10 14:26:41
            [deleted] => 0
            [genre_slug] => rock
        )
    [1] => Array
        (
            [id] => 25
            [headline] => test
            [subheadline] => 
            [slug] => test
            [date] => 2012-08-10
            [start_time] => 00:00:00
            [end_time] => 00:00:00
            [price] => 
            [body] => 
            [location_id] => 5
            [genre_id] => 0
            [creation_date] => 2012-08-10 14:26:33
            [update_date] => 2012-08-10 14:26:41
            [deleted] => 0
            [genre_slug] => metal
        )
    [2] => Array
        (
            [id] => 25
            [headline] => test
            [subheadline] => 
            [slug] => test
            [date] => 2012-08-10
            [start_time] => 00:00:00
            [end_time] => 00:00:00
            [price] => 
            [body] => 
            [location_id] => 5
            [genre_id] => 0
            [creation_date] => 2012-08-10 14:26:33
            [update_date] => 2012-08-10 14:26:41
            [deleted] => 0
            [genre_slug] => indie
        )
)

想要输出（这样的东西会很酷）：

Array(
    [0] => Array
        (
            [id] => 25
            [headline] => test
            [subheadline] => 
            [slug] => test
            [date] => 2012-08-10
            [start_time] => 00:00:00
            [end_time] => 00:00:00
            [price] => 
            [body] => 
            [location_id] => 5
            [genre_id] => 0
            [creation_date] => 2012-08-10 14:26:33
            [update_date] => 2012-08-10 14:26:41
            [deleted] => 0
            [genres] => Array(
                [0] => rock
                [1] => metal
                [2] => indie
            )
        )
)

尝试按 event.id 对其进行分组，然后使用group_concat，如下所示：

SELECT `events`.*, group_concat(`genres_x_events`.`genre_slug`)
FROM (`events`)
LEFT OUTER JOIN `genres_x_events` ON `genres_x_events`.`event_slug` = `events`.`slug`
WHERE `events`.`slug` =  'test'
AND `events`.`deleted` =  0
GROUP BY events.id

这将为您提供给定 event.id 的所有流派的逗号分隔列表。您可以使用类似以下内容更改分隔符（类似于管道）

SELECT `events`.*, group_concat(`genres_x_events`.`genre_slug` SEPARATOR '|')
FROM (`events`)
LEFT OUTER JOIN `genres_x_events` ON `genres_x_events`.`event_slug` = `events`.`slug`
WHERE `events`.`slug` =  'test'
AND `events`.`deleted` =  0
GROUP BY events.id