减少和组合数组

Question

I am trying to create an array that succinctly gives me the following:

• ID of a component part
• ID of the supplier for that component part
• The volume breakpoints, and their associated unit costs

I am using Laravel 5.2, although this is a more general PHP question.

So, I have a database table that looks like this:

I have a function, as below, to get some prices of components:

    public function get_component_prices()
    {
        $components = DB::table('component_supplier')
            ->select('component_id', 'supplier_id', 'volume', 'unit_cost')
            ->get();

        $prices = [];

        foreach ($components as $component) {
            array_push($prices, ["component_id" => $component->component_id, "supplier_id" => $component->supplier_id, "volumes" => [$component->volume => $component->unit_cost]]);
        }

        dd($prices);
    }

This gives me the array:

    array:7 [▼
  0 => array:3 [▼
    "component_id" => 3
    "supplier_id" => 1
    "volumes" => array:1 [▼
      100 => "1.5000"
    ]
  ]
  1 => array:3 [▼
    "component_id" => 3
    "supplier_id" => 1
    "volumes" => array:1 [▼
      207 => "1.0100"
    ]
  ]
  2 => array:3 [▼
    "component_id" => 3
    "supplier_id" => 1
    "volumes" => array:1 [▼
      500 => "0.8000"
    ]
  ]
  3 => array:3 [▼
    "component_id" => 3
    "supplier_id" => 1
    "volumes" => array:1 [▼
      1000 => "0.4000"
    ]
  ]
  4 => array:3 [▼
    "component_id" => 3
    "supplier_id" => 2
    "volumes" => array:1 [▼
      10000 => "0.2000"
    ]
  ]
  5 => array:3 [▼
    "component_id" => 4
    "supplier_id" => 2
    "volumes" => array:1 [▼
      100 => "0.1000"
    ]
  ]
  6 => array:3 [▼
    "component_id" => 4
    "supplier_id" => 2
    "volumes" => array:1 [▼
      500 => "0.0700"
    ]
  ]
]

You can see that certain suppliers and components have multiple volumes.

Therefore, I'd like to try to group the array a bit better, combining the repeated parts - perhaps like so, for example:

6 => array:3 [▼
        "component_id" => 4
        "supplier_id" => 2
        "volumes" => array:3 [▼
          100 => "0.1000",
          500 => "0.0700"
        ]
      ]

So that for each component_id and supplier_id group, there's a set of 'volumes'.

Any advice is much appreciated... I have been trying for hours to get the array sorted!

Answer 1

In Mysql, you can do something like this

SELECT component_id, supplier_id,
       GROUP_CONCAT(volume, ':', unit_cost) AS volumes
FROM component_supplier
GROUP BY CONCAT(component_id, supplier_id)

http://sqlfiddle.com/#!9/cb7bb/1

Then you can simply loop this query in PHP and explode volumes field by the comma.