'Rearrange results from database into array of array for duplicate rows

I need help rearraning results from database into array of array for duplicate rows, this is an example of what i get , like 2 results , i have alot of results and need it to group by rota,clientes and artigos. i need to do this in api using php.

"dados": [
    {
        "idEncomenda": "1",
        "codigoEncomenda": "06904",
        "codigoArtigo": "601056",
        "numExpedicao": "ZG0054",
        "motorista": "Ricardo Fonseca",
        "cliente": "Muralhas Festival",
        "numCliente": "0067",
        "morada": "Rua 13 de Abril",
        "viatura": "Ferrari",
        "matricula": "22-MM-45",
        "rota": "Rota 01",
        "zonaEntrega": "Aveiro Sul",
        "dataExpedicao": "04-07-2021",
        "observacoes": "Teste asasd agsgjsfsfd",
        "artigo": "Sardinha Pequena EI",
        "qtPalete": "0",
        "caixa": "94",
        "qtExpedida": "0",
        "total": "120",
        "unidade": "KG",
        "qtPicada": "0",
        "lote": "B211215I",
        "dataValidadeArtigo": "16-01-2023",
        "id": "1"
    },
    {
        "idEncomenda": "2",
        "codigoEncomenda": "06904",
        "codigoArtigo": "601034",
        "numExpedicao": "ZG0054",
        "motorista": "Ricardo Fonseca",
        "cliente": "Muralhas Festival",
        "numCliente": "0067",
        "morada": "Rua 13 de Abril",
        "viatura": "Ferrari",
        "matricula": "22-MM-45",
        "rota": "Rota 01",
        "zonaEntrega": "Aveiro Sul",
        "dataExpedicao": "04-07-2021",
        "observacoes": "1233123123",
        "artigo": "Solha",
        "qtPalete": "0",
        "caixa": "100",
        "qtExpedida": "0",
        "total": "100",
        "unidade": "KG",
        "qtPicada": "0",
        "lote": "B213215I",
        "dataValidadeArtigo": "16-01-2023",
        "id": "2"
    },
    {
        "idEncomenda": "3",
        "codigoEncomenda": "06904",
        "codigoArtigo": "606756",
        "numExpedicao": "ZG0054",
        "motorista": "Ricardo Fonseca",
        "cliente": "Muralhas Festival",
        "numCliente": "0067",
        "morada": "Rua 13 de Abril",
        "viatura": "Ferrari",
        "matricula": "22-MM-45",
        "rota": "Rota 01",
        "zonaEntrega": "Aveiro Sul",
        "dataExpedicao": "04-07-2021",
        "observacoes": "1233123123",
        "artigo": "Bacalhau",
        "qtPalete": "0",
        "caixa": "54",
        "qtExpedida": "0",
        "total": "200",
        "unidade": "KG",
        "qtPicada": "0",
        "lote": "B221215I",
        "dataValidadeArtigo": "16-01-2023",
        "id": "3"
    },
    {
        "idEncomenda": "4",
        "codigoEncomenda": "09446",
        "codigoArtigo": "606756",
        "numExpedicao": "ZG0054",
        "motorista": "Ricardo Fonseca",
        "cliente": "Viveiros do Sado",
        "numCliente": "0058",
        "morada": "Rua 23 Marques",
        "viatura": "Ferrari",
        "matricula": "22-MM-45",
        "rota": "Rota 01",
        "zonaEntrega": "Aveiro Sul",
        "dataExpedicao": "04-07-2021",
        "observacoes": "1233123123",
        "artigo": "Bacalhau",
        "qtPalete": "0",
        "caixa": "200",
        "qtExpedida": "0",
        "total": "100",
        "unidade": "KG",
        "qtPicada": "0",
        "lote": "B221215I",
        "dataValidadeArtigo": "16-01-2023",
        "id": "4"
    },
    {
        "idEncomenda": "5",
        "codigoEncomenda": "09446",
        "codigoArtigo": "601034",
        "numExpedicao": "ZG0054",
        "motorista": "Ricardo Fonseca",
        "cliente": "Viveiros do Sado",
        "numCliente": "0058",
        "morada": "Rua 23 Marques",
        "viatura": "Ferrari",
        "matricula": "22-MM-45",
        "rota": "Rota 01",
        "zonaEntrega": "Aveiro Sul",
        "dataExpedicao": "04-07-2021",
        "observacoes": "1233123123",
        "artigo": "Solha",
        "qtPalete": "0",
        "caixa": "200",
        "qtExpedida": "0",
        "total": "140",
        "unidade": "KG",
        "qtPicada": "0",
        "lote": "B213215I",
        "dataValidadeArtigo": "16-01-2023",
        "id": "5"
    },

]

and i need it to check when rota is the same ,i need to group by cliente(client) like each rota can have multiple clients and each client can have multiple artigos(items),i show and example of 2 items but i have a list of 300 items.

This is an example of what i want :

"dados": [
    {
        "idEncomenda": "1",
        "numExpedicao": "ZG0054",
        "motorista": "Ricardo Fonseca",
        "numCliente": "0067",
        "viatura": "Ferrari",
        "matricula": "22-MM-45",
        "rota": "Rota 01",
        "zonaEntrega": "Aveiro Sul",
        "dataExpedicao": "04-07-2021",
        "observacoes": "Teste asasd agsgjsfsfd",
        "id": "1",
        "clientes": [
            {
                "cliente": "Muralhas Festival",
                "numCliente": "0067",
                "morada": "Rua 13 de Abril",
                "codigoEncomenda": "06904",
                "artigos": [
                    {
                        "artigo": "Sardinha Pequena EI",
                        "qtPalete": "0",
                        "caixa": "94",
                        "qtExpedida": "0",
                        "total": "120",
                        "unidade": "KG",
                        "qtPicada": "0",
                        "lote": "B211215I",
                        "dataValidadeArtigo": "16-01-2023",
                        "codigoArtigo": "601056"
                    },
                    {
                        "artigo": "Solha",
                        "qtPalete": "0",
                        "caixa": "100",
                        "qtExpedida": "0",
                        "total": "100",
                        "unidade": "KG",
                        "qtPicada": "0",
                        "lote": "B213215I",
                        "dataValidadeArtigo": "16-01-2023",
                    },
                    {
                        "artigo": "Bacalhau",
                        "qtPalete": "0",
                        "caixa": "54",
                        "qtExpedida": "0",
                        "total": "200",
                        "unidade": "KG",
                        "qtPicada": "0",
                        "lote": "B221215I",
                        "dataValidadeArtigo": "16-01-2023",
                    }
                ]
                "cliente": "Viveiros Monte Real",
                "numCliente": "0067",
                "morada": "Rua 13 de Abril",
                "codigoEncomenda": "06904",
                "artigos": [
                    {
                        "artigo": "Bacalhau",
                        "qtPalete": "0",
                        "caixa": "200",
                        "qtExpedida": "0",
                        "total": "100",
                        "unidade": "KG",
                        "qtPicada": "0",
                        "lote": "B221215I",
                        "dataValidadeArtigo": "16-01-2023",
                    },
                    {
                        "artigo": "Solha",
                        "qtPalete": "0",
                        "caixa": "200",
                        "qtExpedida": "0",
                        "total": "140",
                        "unidade": "KG",
                        "qtPicada": "0",
                        "lote": "B213215I",
                        "dataValidadeArtigo": "16-01-2023",
                    }
                ]
            }
        ]
    },

Here is what i came up with so far:

public static function getExpedicao()
{
    $expedicoes = self::find()->asArray()->all();

    foreach ($expedicoes as $key => $value) {
        $expedicoes[$key]["id"] = $value["idEncomenda"];
    }

    foreach ($expedicoes as $key => $value) {
        $expedicoes[$key]['artigos'] = [array_splice($value, 14, 9)+ array_splice($value, 2, 1)];
    }

    foreach ($expedicoes as &$item) {
        unset($item['artigo'], $item['qtPalete'], $item['caixa'], $item['qtExpedida'], $item['total'], $item['unidade'], $item['qtPicada'], $item['lote'], $item['dataValidadeArtigo'],$item['codigoArtigo']);
    }

    foreach ($expedicoes as $key => $value) {
        $expedicoes[$key]["clientes"] = [array_splice($value, 4, 3) + array_splice($value, 1, 1) + array_splice($value, 10, 12)];
    }

    foreach ($expedicoes as &$item) {
        unset($item['cliente'], $item['num_cliente'], $item['morada'], $item['codigoEncomenda'], $item['artigos']);
    }

    $res = array();

    foreach ($expedicoes as $values) {

        if (!isset($res[$values['rota']])) {
            $res[$values['rota']] = $values;
        }


    }

    $res = array_values($res);
    return [
        "resposta" => true,
        "dados" => $res,
    ];
}

javascript php

Solution 1:^[1]

In Javascript, you could take an array of wanted groups with

grouping property,
an array of properties of their grouping level,
a wanted key name for the children of this level of grouping.

Then create a nested object (this is the same structure of every level) with a property _ which keeps an object for the nested levels and an array of the children of the actual level.

To get a result take only the _ property.

const
    data = [{ idEncomenda: "1", codigoEncomenda: "06904", codigoArtigo: "601056", numExpedicao: "ZG0054", motorista: "Ricardo Fonseca", cliente: "Muralhas Festival", numCliente: "0067", morada: "Rua 13 de Abril", viatura: "Ferrari", matricula: "22-MM-45", rota: "Rota 01", zonaEntrega: "Aveiro Sul", dataExpedicao: "04-07-2021", observacoes: "Teste asasd agsgjsfsfd", artigo: "Sardinha Pequena EI", qtPalete: "0", caixa: "94", qtExpedida: "0", total: "120", unidade: "KG", qtPicada: "0", lote: "B211215I", dataValidadeArtigo: "16-01-2023", id: "1" }, { idEncomenda: "10", codigoEncomenda: "06945", codigoArtigo: "601056", numExpedicao: "ZG0032", motorista: "Filipe Vaz", cliente: "Viveiros Monte Real", numCliente: "0045", morada: "Rua dos Barros", viatura: "Peugeot", matricula: "65-HG-34", rota: "Rota 01", zonaEntrega: "Porto", dataExpedicao: "06-10-2021", observacoes: "asddasd", artigo: "Sardinha Pequena EI", qtPalete: "0", caixa: "54", qtExpedida: "0", total: "200", unidade: "KG", qtPicada: "0", lote: "B211215I", dataValidadeArtigo: "16-01-2023", id: "10" }, { idEncomenda: "10", codigoEncomenda: "06945", codigoArtigo: "601056", numExpedicao: "ZG0032", motorista: "Filipe Vaz", cliente: "Viveiros Monte Real", numCliente: "0045", morada: "Rua dos Barros", viatura: "Peugeot", matricula: "65-HG-34", rota: "Rota 01", zonaEntrega: "Porto", dataExpedicao: "06-10-2021", observacoes: "asddasd", artigo: "Sardinha Pequena EI", qtPalete: "0", caixa: "54", qtExpedida: "0", total: "200", unidade: "KG", qtPicada: "0", lote: "B211215I", dataValidadeArtigo: "16-01-2023", id: "10" }],
    keys = [
        ['rota', ['idEncomenda', 'numExpedicao', 'motorista', 'numCliente', 'viatura', 'matricula', 'rota', 'zonaEntrega', 'dataExpedicao','observacoes','id'], 'clientes'],
        ['cliente', ['cliente', 'numCliente', 'morada', 'codigoEncomenda'], 'artigos'],
    ],
    result = data.reduce((r, o) => {
        keys
            .reduce(function (level, [group, keys, children]) {
                let v;
                const 
                    g = o[group],
                    p = Object.fromEntries(keys.map(k => [k, (({ [k]: v, ...o } = o), v)]));

                if (!level[g]) {
                    level[g] = { _: [] };
                    level._.push({ ...p, [children]: level[g]._ });
                }
                return level[g];
            }, r)
            ._
            .push(o);
        return r;
    }, { _: [] })._;

console.log(result);

.as-console-wrapper { max-height: 100% !important; top: 0; }

Solution 2:^[2]

Managed to fix the issue with the help of @Jaydeep Mor answer by using this:

    public static function getExpedicao()
{
    $expedicoes = self::find()->asArray()->all();

    foreach ($expedicoes as $idkey => $value) {
        $expedicoes[$idkey]["id"] = $value["idEncomenda"]; 
    }

    $output = [];

    $clientFields = [
        "numCliente",
        "cliente",
        "morada",
        "codigoEncomenda"
    ];

    $artigoFields = [
        "codigoArtigo",
        "artigo",
        "qtPalete",
        "caixa",
        "qtExpedida",
        "total",
        "unidade",
        "qtPicada",
        "lote",
        "dataValidadeArtigo"
    ];

    foreach ($expedicoes as $index => $data) {
        foreach ($data as $key => $value) {
            if ($key == 'cliente' || $key == 'artigo') {
                continue;
            }

            $output[$data['rota']][$key] = $value;
        }

        foreach ($clientFields as $clientField) {
            $output[$data['rota']]['clientes'][$data['cliente']][$clientField] = $data[$clientField];
        }

        foreach ($artigoFields as $artigoField) {
            $output[$data['rota']]['clientes'][$data['cliente']]['artigos'][$data['artigo']][$artigoField] = $data[$artigoField];
        }
    }

    foreach ($output as &$item) {
        unset($item['artigo'], $item['qtPalete'], $item['caixa'], $item['qtExpedida'], $item['total'], $item['unidade'], $item['qtPicada'], $item['lote'], $item['dataValidadeArtigo'], $item['codigoArtigo'], $item['numCliente'], $item['morada'], $item['codigoEncomenda']);
    }

    // Remove rota from output array key.
    $output = array_values($output);

    // Remove cliente from output array key.
    foreach ($output as &$out) {
        $out['clientes'] = array_values($out['clientes']);
    }

    foreach ($output as &$out) {
       
        foreach ($out['clientes'] as $indexkey => &$unusedoutput) {
            $out['clientes'][$indexkey]['artigos'] = array_values($out['clientes'][$indexkey]['artigos']);
        }
    }

    return [
        "resposta" => true,
        "dados" => $output,
    ];
}

}

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1
Solution 2	Arti

'Rearrange results from database into array of array for duplicate rows

Solution 1:[1]

Solution 2:[2]

Sources

Related Questions

Solution 1:^[1]

Solution 2:^[2]