0

First of all, I have following data returned from database. I will have two different data as below respectively

sum1 count1 sm__state_name__ om__order_date__year om__order_date__quarter om__order_date__month
5645000 4 Luanda 2017 3 8
213985939.8600001 1606 Luanda 2017 3 9
7729331.52 119 Benguela 2017 3 9
1012936 17 Zaire 2017 3 9
1054883 19 Bie 2017 3 9
2347944 26 Cuando Cubango 2017 3 9
428769.6000000001 60 Bengo 2017 3 9
6444569 86 Huila 2017 3 9
4914030 25 Cunane 2017 3 9
1167200 26 Cuanza North 2017 3 9
750080 10 Cuanza Sul 2017 3 9
2178100 6 Huambo 2017 3 9
1099934 25 Lunda North 2017 3 9
410135 12 Malange 2017 3 9

In array format

   array (
  0 => 
  array (
    'sum1' => '5645000',
    'count1' => '4',
    'sm__state_name__' => 'Luanda',
    'om__order_date__year' => '2017',
    'om__order_date__quarter' => '3',
    'om__order_date__month' => '8',
  ),
  1 => 
  array (
    'sum1' => '213985939.8600001',
    'count1' => '1606',
    'sm__state_name__' => 'Luanda',
    'om__order_date__year' => '2017',
    'om__order_date__quarter' => '3',
    'om__order_date__month' => '9',
  ),
  2 => 
  array (
    'sum1' => '352839.60000000003',
    'count1' => '9',
    'sm__state_name__' => NULL,
    'om__order_date__year' => '2017',
    'om__order_date__quarter' => '3',
    'om__order_date__month' => '9',
  ),
  3 => 
  array (
    'sum1' => '7729331.52',
    'count1' => '119',
    'sm__state_name__' => 'Benguela',
    'om__order_date__year' => '2017',
    'om__order_date__quarter' => '3',
    'om__order_date__month' => '9',
  ),
  4 => 
  array (
    'sum1' => '1012936',
    'count1' => '17',
    'sm__state_name__' => 'Zaire',
    'om__order_date__year' => '2017',
    'om__order_date__quarter' => '3',
    'om__order_date__month' => '9',
  ),
  5 => 
  array (
    'sum1' => '1054883',
    'count1' => '19',
    'sm__state_name__' => 'Bie',
    'om__order_date__year' => '2017',
    'om__order_date__quarter' => '3',
    'om__order_date__month' => '9',
  ),
  6 => 
  array (
    'sum1' => '2347944',
    'count1' => '26',
    'sm__state_name__' => 'Cuando Cubango',
    'om__order_date__year' => '2017',
    'om__order_date__quarter' => '3',
    'om__order_date__month' => '9',
  ),
  7 => 
  array (
    'sum1' => '428769.6000000001',
    'count1' => '60',
    'sm__state_name__' => 'Bengo',
    'om__order_date__year' => '2017',
    'om__order_date__quarter' => '3',
    'om__order_date__month' => '9',
  ),
  8 => 
  array (
    'sum1' => '6444569',
    'count1' => '86',
    'sm__state_name__' => 'Huila',
    'om__order_date__year' => '2017',
    'om__order_date__quarter' => '3',
    'om__order_date__month' => '9',
  ),
  9 => 
  array (
    'sum1' => '4914030',
    'count1' => '25',
    'sm__state_name__' => 'Cunane',
    'om__order_date__year' => '2017',
    'om__order_date__quarter' => '3',
    'om__order_date__month' => '9',
  ),
  10 => 
  array (
    'sum1' => '1167200',
    'count1' => '26',
    'sm__state_name__' => 'Cuanza North',
    'om__order_date__year' => '2017',
    'om__order_date__quarter' => '3',
    'om__order_date__month' => '9',
  ),
  11 => 
  array (
    'sum1' => '750080',
    'count1' => '10',
    'sm__state_name__' => 'Cuanza Sul',
    'om__order_date__year' => '2017',
    'om__order_date__quarter' => '3',
    'om__order_date__month' => '9',
  ),
  12 => 
  array (
    'sum1' => '2178100',
    'count1' => '6',
    'sm__state_name__' => 'Huambo',
    'om__order_date__year' => '2017',
    'om__order_date__quarter' => '3',
    'om__order_date__month' => '9',
  ),
  13 => 
  array (
    'sum1' => '1099934',
    'count1' => '25',
    'sm__state_name__' => 'Lunda North',
    'om__order_date__year' => '2017',
    'om__order_date__quarter' => '3',
    'om__order_date__month' => '9',
  ),
  14 => 
  array (
    'sum1' => '410135',
    'count1' => '12',
    'sm__state_name__' => 'Malange',
    'om__order_date__year' => '2017',
    'om__order_date__quarter' => '3',
    'om__order_date__month' => '9',
  ),
)

Array
(
    "sm__state_name__",
    "om__order_date__year",
    "om__order_date__quarter",
    "om__order_date__month",
)

ABOBE ARRAY INCLUDES CAN BE ANY NUMBER OF FIELDS

Below is sample output of What I want in return

{
    "data": [
        {
            "key": "Luanda",
            "items": [
                {
                    "key": 2017,
                    "items": [
                        {
                            "key": 3,
                            "items": [
                                {
                                    "key": 8,
                                    "items": null,
                                    "count": 4,
                                    "summary": [
                                        438380.9935
                                    ]
                                },
                                {
                                    "key": 9,
                                    "items": null,
                                    "count": 1606,
                                    "summary": [
                                        438380.9935
                                    ]
                                },
                            ],
                            "summary": [
                                1285085.9636
                            ]
                        }
                    ],
                    "summary": [
                        1285085.9636
                    ]
                }
            ],
            "summary": [
                1285085.9636
            ]
        },
        {
            "key": "Benguela",
            "items": [
                {
                    "key": 2017,
                    "items": [
                        {
                            "key": 3,
                            "items": [
                                {
                                    "key": 9,
                                    "items": null,
                                    "count": 679,
                                    "summary": [
                                        4781987.8575
                                    ]
                                },
                            ],
                            "summary": [
                                15017212.0305
                            ]
                        }
                    ],
                    "summary": [
                        15017212.0305
                    ]
                }
            ],
            "summary": [
                15017212.0305
            ]
        },
        {...},
        {...},
        {...},
    ],
    "totalCount": 22854
}

Don't mind the summary value. I just put dummy values there.

Is this kind of process even possible? Because I think of many different things, recursion, multiple loops, triple loop but couldn't think of way this could work.

I know it's not an issue or bug. Sorry for that. But it would be great if someone could point me to right direction.

Improve this question
8
  • yes it possible though, you may use nested loop for that if the data is fixed, also paste the data of the sql in array format so we can test it without copying your table data into array Commented Dec 30, 2021 at 6:40
  • @Jerson I have edited the code with array format for table data. Commented Dec 30, 2021 at 6:56
  • can you use var_export instead of print_r for printing Commented Dec 30, 2021 at 6:58
  • where is summary comes from in the deep nested? Commented Dec 30, 2021 at 7:14
  • It is the sum of sum1 column from data base. For example, if we take Luanda first 2 entries then summary of Luanda will be 214550439.8600001 Commented Dec 30, 2021 at 7:18

2 Answers 2

Reset to default
2

With a variable array of columns you need to group in hierarchical order, you'll certainly want a recursive solution to this problem. For each step in your recursive calls, check to see if a particular grouping level exists yet, and if not, then initialize it. Group using associative arrays for your items, then convert to flat arrays after. It's very simple conceptually, although perhaps a little confusing to look at:

function aggregateData($data, $db_row, $columns, $first_column = true) {
    // Base case: with no more columns left, we just take the sum and return.
    if(empty($columns)) {
        $data['summary'] += $db_row['sum1'];
        return $data;
    }

    $column = array_shift($columns);
    $value = $db_row[$column];
    if($first_column) {
        // First column is a special case. We don't add anything here because every level's summary is the sum of its nested items.
        if(!array_key_exists($value, $data)) {
            $data[$value] = [
                'key'=>$value,
                'items'=>empty($columns) ? null : [],
                'summary'=>0
            ];
        }

        $data[$value] = aggregateData($data[$value], $db_row, $columns, false);
    } else {
        // For all other columns, we add the sum to each nested level.
        if(!array_key_exists($value, $data['items'])) {
            $data['items'][$value] = [
                'key'=>$value,
                'items'=>empty($columns) ? null : [],
                'summary'=>0
            ];
        }

        $data['summary'] += $db_row['sum1'];
        $data['items'][$value] = aggregateData($data['items'][$value], $db_row, $columns, false);
    }

    return $data;
}

function flattenData($data) {
    foreach($data as $key=>$value) {
        if(is_null($value['items'])) {
            break;
        }

        $data[$key]['items'] = flattenData($value['items']);
    }

    return array_values($data);
}

$db_rows = /* your DB retrieval code here */;
$columns = /* columns to group by in hierarchical order */;
$data = [];
foreach($db_rows as $db_row) {
    $data = aggregateData($data, $db_row, $columns);
}

$data = flattenData($data);

To help understand what's going on, consider the top-most level, grouping by state. After the aggregateData() calls, before flattening the arrays, it will produce a structure that looks like the following:

{
    "Luanda": {
        "key": "Luanda",
        "items": {...},
        "summary": ...,
    },
    "Benguela": {
        "key": "Benguela",
        "items": {...},
        "summary": ...,
    }
}

Notice that because each entry is associated with its key in an object, instead of an index in an array, this allows for easy lookups so we can aggregate information at each level. After flattening, we instead get this:

[
    {
        "key": "Luanda",
        "items": [...],
        "summary": ...,
    },
    {
        "key": "Benguela",
        "items": [...],
        "summary": ...,
    }
]

Each entry is no longer associated with its key, instead being the desired flat array. We lose the ability to do simple lookups, but we no longer need that capability at the end of our calculations.

The above doesn't solve the entirety of your problem as there are points of data not being included in this result, but as stackoverflow is not a free coding service and you have not provided any of your own code, I will be leaving the necessary modifications as an exercise. This should, however, remove the bulk of the work required and serve as a strong starting point for your solution.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I'm sorry maybe I didn't ask my question properly. The four fields (state, year, quarter, month) are not fixed. It can be any number of fields. It will be given in an array;
@VivekMakwana This is an important piece of information to your question. Please provide it in the original question to avoid confusion.
@VivekMakwana I've modified my solution to work on a dynamic list of columns. You will note, as my answer states, that this is not the full solution to your problem, but it will get you 85% or more of the way there. I would encourage you to read over the solution to grasp exactly how it works.
0

Ok, so basically if I understand this correctly, you have a database with a bunch of records. Then you want to create some massive JSON based off the database that will have 4 dimensions based on the columns for the state, year, quarter, and month. Then the outer-most array will contain the records of the database grouped by the state, then the items for any specific state will further constrict into a set of items based on the key for the year, etc.

I'm thinking the most efficient way to do this is a single loop where you'd pull all the records in the database then loop through it in PHP and construct some new arrays based on the current record iteration which can be referred to later for look-up purposes.

Begin by setting:

$dataItems = [];

This would be used for storing and structuring data for easily look up and calculations.

Iterate through the list. The first item would be:

sum1 count1 state_name order_year order_quarter order_month
5645000 4 Luanda 2017 3 8

Then you'd run code for the iteration that would look something like this to help populate the array or arrays you'd be constructing:

if (empty($dataItems['Luanda'])) {
    $dataItems['Luanda'] = [];
}
if (empty($dataItems['Luanda'][2017])) {
    $dataItems['Luanda'][2017] = [];
}
if (empty($dataItems['Luanda'][2017][3])) {
    $dataItems['Luanda'][2017][3] = [];
}
if (empty($dataItems['Luanda'][2017][3][8])) {
    $dataItems['Luanda'][2017][3][8] = [];
}
$dataItems['Luanda'][2017][3][8][] = ['sum1' => 5645000, 'count1' => 4];

The second item would be:

sum1 count1 state_name order_year order_quarter order_month
213985939.8600001 1606 Luanda 2017 3 9

The PHP code for this iteration would look like this:

if (empty($dataItems['Luanda'])) {
    $dataItems['Luanda'] = [];
}
if (empty($dataItems['Luanda'][2017])) {
    $dataItems['Luanda'][2017] = [];
}
if (empty($dataItems['Luanda'][2017][3])) {
    $dataItems['Luanda'][2017][3] = [];
}
if (empty($dataItems['Luanda'][2017][3][9])) {
    $dataItems['Luanda'][2017][3][9] = [];
}
$dataItems['Luanda'][2017][3][9][] = ['sum1' => 213985939.8600001, 'count1' => 1606];

Etc. Then you'd loop through the $dataItems structure once it's built and do stylizing logic like having a key called "items", etc and create your desired output structure, then finally output in JSON format by using json_encode.

If you need something like the count for the entire year, in your initial loop, you can write to a separate array to help keep track of it, adding it together along the way, then refer to it later when creating an array with the desired output structure. Things like summary you can easily add into the loop and keep track of that along the way.

Sounds like some fun, but yeah you'd only need one loop to create a lookUp or multiple lookUp sort of arrays then a second loop to refer to the loopUp array(s) to get your data in the desired output format.

Improve this answer

2 Comments

I'm sorry maybe I didn't ask my question properly. The four fields (state, year, quarter, month) are not fixed. It can be any number of fields. It will be given in an array;
@VivekMakwana I believe the approach would remain basically the same. Whichever columns we're grouping by would just wind-up being the key in the array we check to make sure it exists, then finally write to it. When checking if it exists, would probably need to do this in a loop though, to make sure we have the right nesting depth, before writing to it. Might start off using the 4 columns you mentioned and get that working, then move on to making it dynamic afterwards by adding a loop for the depth.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.