codeigniter 如何在PHP中从单个MySQL表中获取按多列分组的多维数组？

0wi1tuuw 于 2022-12-07 发布在 PHP

关注(0)|答案(1)|浏览(121)

我有一个名为“guids”表，它看起来像这样：

id |survey_type |guid |guid_type |table_id |created_at |
2 |TYPE 1 |6C7251E3-2151-4754-A413-51899FAAF6C2 |question |2 |2022-03-20 16:14:09 |
3 |TYPE 1 |EF5AFA93-C74D-4920-A13A-17A9B43239CD |question |3 |2022-03-20 16:14:09 |
4 |TYPE 1 |5C059148-94BE-4225-B5C2-551A81B65F16 |question |4 |2022-03-20 16:14:09 |
5 |TYPE 1 |356B8A5C-1072-47A5-A508-D9BDCBA92CCC |answer |5 |2022-03-20 16:14:09 |
6 |TYPE 1 |E0CE4C26-7ABD-4162-9C8C-B4DD540AE268 |answer |6 |2022-03-20 16:14:09 |
7 |TYPE 1 |BFBC50FC-892D-43E9-A235-D76E0D0BEF29 |answer |7 |2022-03-20 16:14:09 |
8 |TYPE 2 |B9DCC5C1-CBFB-4589-98EF-4524F3958968 |survey |8 |2022-03-20 16:14:09 |
9 |TYPE 2 |C98FBFF9-6FE3-414E-BB14-08EDC8281E66 |survey |9 |2022-03-20 16:14:09 |
10 |TYPE 2 |8A780B6E-EAE0-47D6-9D05-F52B795AE617 |question |10 |2022-03-20 16:14:09 |
11 |TYPE 2 |E3818D30-BB69-4F03-B56D-B31691F8007E |question |11 |2022-03-20 16:14:09 |
12 |TYPE 2 |24C81BEF-BFCE-4964-AB01-F3579251313D |answer |12 |2022-03-20 16:14:09 |
13 |TYPE 3 |59381701-AFBC-48F8-AECE-DB3702EE2B15 |answer |13 |2022-03-20 16:14:09 |
14 |TYPE 3 |7F4AC694-74DC-4BEA-ACFB-D8F070769FEE |answer |14 |2022-03-20 16:14:09 |
15 |TYPE 3 |B5C405B9-BA7E-471A-87DD-B69D9757276F |survey |15 |2022-03-20 16:14:09 |

我想从那里得到以下格式的数据：

Array
(
    [survey_type] => Array
        (
            [guid_type] => Array
                (
                    [table_id] => guid
                    ...
                )
            ... // all guid types
        )
    ... // all survey types
)

基本上，我需要使用多个列来构建一个包含分组数据的嵌套数组。我知道我可以使用foreach循环来实现这种结构，如下所示：

// select existing guids
$this->db->select('*')
      ->from('guids');
$query = $this->db->get();
$existing_guids = $query->result_array();
$formated_guids = [];

foreach($existing_guids as $key => $guid_data) {
    $formated_guids[$guid_data['survey_type']][$guid_data['guid_type']][$guid_data['table_id']] = $guid_data['guid'];
}

但是如果可能的话，我希望在数据库层有一个更优化的解决方案，因为这个表预计有数百万行，循环通过整个表似乎不是最优化的方式。
我已经使用了一段时间的GROUP_CONCAT mysql命令，但未能生成所需的结构

SELECT `survey_type`, GROUP_CONCAT(`guid_type`) AS `guid_types` FROM guids GROUP BY `survey_type`

模拟数据的预期结果：

Array
(
    [TYPE 1] => Array // survey type column
        (
            [question] => Array // guid_type column
                (
                    // table_id => guid
                    [1] => C8D21BD8-DA62-43C6-8E0D-0524D4F093B6
                    [2] => 6C7251E3-2151-4754-A413-51899FAAF6C2
                    ...
                )

            [survey] => Array
                (
                    [14] => B8B1F361-34A2-430E-9C0E-EB0C515B4E79
                    ...
                )

            [answer] => Array
                (
                    [2906] => 1A2F133E-0491-4117-AE2E-E3A823B66FBD...
                )

        ),
    [TYPE 2] => Array
        (
        ... // same structure as above
)

codeigniter

来源：https://stackoverflow.com/questions/71548335/how-to-get-multidimensional-array-grouped-by-multiple-columns-in-php-from-single

1条答案

按热度按时间

vsnjm48y1#

首先，我建议你坚持使用你的代码解决方案。运行一个简单的SQL查询，处理结果，将它们整理成你想要的嵌套数组结构。你已经编写好了代码，它更容易调试，如果你将来需要修改它，也更容易修改。
我已经实现了你所描述的东西，在SQL查询中创建一个嵌套结构。这是相当困难的，而且SQL查询也足够复杂，如果我们需要修改嵌套结构的内容，这将是一个未来的维护问题。
我在SQL查询中使用的解决方案是使用多个级别的派生表子查询，并使用JSON函数在每个级别生成聚合JSON结果。这需要使用MySQL 5.7或更高版本，因为这些JSON函数在MySQL的早期版本中没有实现。
演示测试数据：

create table if not exists mytable ( id int primary key, survey_type varchar(20), guid char(36), guid_type varchar(20), table_id  int, created_at datetime );
insert into mytable values 
(2 ,'TYPE 1','6C7251E3-2151-4754-A413-51899FAAF6C2','question','2','2022-03-20 16:14:09'), 
(3 ,'TYPE 1','EF5AFA93-C74D-4920-A13A-17A9B43239CD','question','3','2022-03-20 16:14:09'), 
(4 ,'TYPE 1','5C059148-94BE-4225-B5C2-551A81B65F16','question','4','2022-03-20 16:14:09'), 
(5 ,'TYPE 1','356B8A5C-1072-47A5-A508-D9BDCBA92CCC','answer','5','2022-03-20 16:14:09'), 
(6 ,'TYPE 1','E0CE4C26-7ABD-4162-9C8C-B4DD540AE268','answer','6','2022-03-20 16:14:09'), 
(7 ,'TYPE 1','BFBC50FC-892D-43E9-A235-D76E0D0BEF29','answer','7','2022-03-20 16:14:09'), 
(8 ,'TYPE 2','B9DCC5C1-CBFB-4589-98EF-4524F3958968','survey','8','2022-03-20 16:14:09'), 
(9 ,'TYPE 2','C98FBFF9-6FE3-414E-BB14-08EDC8281E66','survey','9','2022-03-20 16:14:09'), 
(10 ,'TYPE 2','8A780B6E-EAE0-47D6-9D05-F52B795AE617','question','10','2022-03-20 16:14:09'), 
(11 ,'TYPE 2','E3818D30-BB69-4F03-B56D-B31691F8007E','question','11','2022-03-20 16:14:09'), 
(12 ,'TYPE 2','24C81BEF-BFCE-4964-AB01-F3579251313D','answer','12','2022-03-20 16:14:09'), 
(13 ,'TYPE 3','59381701-AFBC-48F8-AECE-DB3702EE2B15','answer','13','2022-03-20 16:14:09'), 
(14 ,'TYPE 3','7F4AC694-74DC-4BEA-ACFB-D8F070769FEE','answer','14','2022-03-20 16:14:09'), 
(15 ,'TYPE 3','B5C405B9-BA7E-471A-87DD-B69D9757276F','survey','15','2022-03-20 16:14:09');

示例查询：

select json_pretty(json_objectagg(survey_type, g)) as j
from (
  select survey_type, json_objectagg(guid_type, t) as g
  from (
    select survey_type, guid_type, json_objectagg(table_id, guid) as t
    from mytable
    group by survey_type, guid_type
  ) as t
  group by survey_type
) as g;

输出量：

{
  "TYPE 1": {
    "answer": {
      "5": "356B8A5C-1072-47A5-A508-D9BDCBA92CCC",
      "6": "E0CE4C26-7ABD-4162-9C8C-B4DD540AE268",
      "7": "BFBC50FC-892D-43E9-A235-D76E0D0BEF29"
    },
    "question": {
      "2": "6C7251E3-2151-4754-A413-51899FAAF6C2",
      "3": "EF5AFA93-C74D-4920-A13A-17A9B43239CD",
      "4": "5C059148-94BE-4225-B5C2-551A81B65F16"
    }
  },
  "TYPE 2": {
    "answer": {
      "12": "24C81BEF-BFCE-4964-AB01-F3579251313D"
    },
    "survey": {
      "8": "B9DCC5C1-CBFB-4589-98EF-4524F3958968",
      "9": "C98FBFF9-6FE3-414E-BB14-08EDC8281E66"
    },
    "question": {
      "10": "8A780B6E-EAE0-47D6-9D05-F52B795AE617",
      "11": "E3818D30-BB69-4F03-B56D-B31691F8007E"
    }
  },
  "TYPE 3": {
    "answer": {
      "13": "59381701-AFBC-48F8-AECE-DB3702EE2B15",
      "14": "7F4AC694-74DC-4BEA-ACFB-D8F070769FEE"
    },
    "survey": {
      "15": "B5C405B9-BA7E-471A-87DD-B69D9757276F"
    }
  }
}

将结果提取到您的客户端应用程序中。这是一个长字符串，所以我希望您的数据不会超过MySQL的max_allowed_packet长度。
使用json_decode()将字符串转换为所需的嵌套数组。

赞(0）回复(0）举报 2022-12-07

我来回答

codeigniter 如何在PHP中从单个MySQL表中获取按多列分组的多维数组？

1条答案

相关问题

热门标签

最新问答