Rearrange results from database into array of array for duplicate rows-CodePudding

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,
    ];
}

CodePudding user response：

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; }

CodePudding user response：

Still your example input confusing with expected output but here is my answer as I understand.

$array[] = [
    "rota" => "Rota 01",
    "cliente" => "Muralhas Festival",
    "numExpedicao" => "ZG0054",
    "artigo" => "Sardinha Pequena EI"
];

$array[] = [
    "rota" => "Rota 01",
    "cliente" => "Viveiros Monte Real",
    "numExpedicao" => "ZG0032",
    "artigo" => "Sardinha Pequena EI"
];

$array[] = [
    "rota" => "Rota 01",
    "cliente" => "Viveiros Monte Real",
    "numExpedicao" => "ZG0032",
    "artigo" => "Sardinha Pequena EI"
];

$array[] = [
    "rota" => "Rota 02",
    "cliente" => "Viveiros",
    "numExpedicao" => "ZG0033",
    "artigo" => "Sardinha"
];

$output = [];

foreach ($array as $data) {
    foreach ($data as $key => $value) {
        if ($key == "cliente") {
            $output[$data['rota']]["clientes"][] = $value;
        } elseif ($key == "artigo") {
            $output[$data['rota']]["artigos"][] = $value;
        } else {
            $output[$data['rota']][$key] = $value;
        }
    }
}

Check it and let me know if any issue.