How to get multidimensional array grouped by multiple columns in PHP from single MySQL table?-CodePudding

I have table called "guids" that looks like this:

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 |

From that I would like to get data in this format:

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

Basically, I need to use multiple columns to build a nested array with grouped data. I know that I can achive that structure using foreach loop like this:

// 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'];
}

But if possible I would like a more optimized solution on the database layer as this table is expected to have millions of rows and looping through the entire table doesn't seem to be the most optimized way to do it.

I have played around with GROUP_CONCAT mysql command for a bit but failed to produce the desired structure

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

Desired result with mock data:

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
)

CodePudding user response：

First, I would recommend you stick with your code solution. Run a simple SQL query, and process the results, collating them into the nested array structure you want. You already have that coded, it's easier to debug and easier to change in the future if you need to change it.

I've implemented something like you describe, to create a nested structure in an SQL query. It was pretty hard, and the SQL query was complex enough that it will be a maintenance problem in the future if we ever need to modify the contents of the nested structure.

The solution I used in the SQL query was to use multiple levels of derived table subqueries, and generate aggregate JSON results at each level using JSON functions. This requires using MySQL 5.7 or later, because these JSON functions are not implemented in earlier versions of MySQL.

Demo test data:

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');

Example query:

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;

Output:

{
  "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"
    }
  }
}

Fetch that result into your client app. It's a single long string, so I hope your data is not longer than MySQL's max_allowed_packet length.

Convert the string into your desired nested array with json_decode().