嵌套关联数组形式的PHP分组查询结果

Question

我正在尝试以一种高效的方式将PHP中的查询结果分组为一个关联数组。我可以使用多个嵌套的foreach循环，检查当前的id字段是否与每个数组的前一个id字段匹配，但我猜有更好/更有效的解决方案来解决这个问题。有没有人有一个聪明的解决方案？理想情况下，一个泛型函数或方法可以对给定关键字段或嵌套关键字段数组的任何查询结果集进行分组？

以下是查询结果数组：

Array
(
    [0] => Array
        (
            [look_id] => 3
            [look_name] => Test Look
            [look_description] => description here
            [clothing_article_id] => 1
            [clothing_article_name] => Coat
            [look_clothing_article_attribute_id] => 1
            [look_clothing_article_attribute_name] => Purple
            [clothing_brand_name] => Gap
            [clothing_article_brand_price] => 40.00
            [clothing_article_brand_attribute_price] => 50.00
            [clothing_article_brand_attribute_name] => Purple
        )

    [1] => Array
        (
            [look_id] => 3
            [look_name] => Test Look
            [look_description] => description here
            [clothing_article_id] => 1
            [clothing_article_name] => Coat
            [look_clothing_article_attribute_id] => 2
            [look_clothing_article_attribute_name] => Black
            [clothing_brand_name] => Gap
            [clothing_article_brand_price] => 40.00
            [clothing_article_brand_attribute_price] => 
            [clothing_article_brand_attribute_name] => 
        )

    [2] => Array
        (
            [look_id] => 3
            [look_name] => Test Look
            [look_description] => description here
            [clothing_article_id] => 2
            [clothing_article_name] => Pants
            [look_clothing_article_attribute_id] => 3
            [look_clothing_article_attribute_name] => Cuffed
            [clothing_brand_name] => 
            [clothing_article_brand_price] => 
            [clothing_article_brand_attribute_price] => 
            [clothing_article_brand_attribute_name] => 
        )

)

这是我想要转换成的数组：

Array
(
    'looks' => Array(
        [0] => Array(
            'id' => 3,
            'name' => 'Test Look',
            'description' => 'description here',
            'clothingArticles' => Array(
                [0] => Array(
                    'id' => 1,
                    'name' => 'Coat',
                    'attributes' => Array(
                        [0] => Array(
                            'id' => 1,
                            'name' => 'Purple'
                            'brands' => Array(
                                [0] => Array(
                                    'name' => 'Gap',
                                    'price' => 50.00
                                )
                            )
                        ),
                        [1] => Array(
                            'id' => 2,
                            'name' => 'Black'
                        )
                    ),
                    'brands' => Array(
                        [0] => Array(
                            'name' => 'Gap',
                            'price' => 40.00
                        )
                    )
                ),
                [1] => Array(
                    'id' => 2,
                    'name' => 'Pants',
                    'attributes' => Array(
                        [0] => Array(
                            'id' => 3,
                            'name' => 'Cuffed'
                            'brands' => Array()
                        )
                    ),
                    'brands' => Array()
                )
            )
        )
    )
)

分组关系说明：

一个look可以有一件或多件衣服。一件服装商品可以有一个或多个服装品牌。look服装文章可以有一个或多个属性。一个服装产品品牌可以有一个或多个属性。

Answer 1

这样做应该不会太可怕；不应该对内部数组使用数字索引，而应该使用唯一标识符，这样就可以对其进行分组。这也不会阻止您在数组本身中使用该标识符。

//seems a little pointless?
$looks = array('looks' => array());
$looks = &$looks['looks'];
while ($row = $result->fetch()) {
    if (!isset($looks[$row->look_id])) {
        $looks[$row->look_id] = array(
            'id' => $row->look_id,
            'name' => $row->look_name,
            'description' => $row->look_description,
            'clothingArticles' => array()
        );
    }
    $look = &$looks[$row->look_id];
    if (!isset($look[$row->clothing_article_id])) {
        //continue this process as needed

Answer 2

执行尽可能多的连接级别的查询，每个表一个，所有查询都在相同的列上排序，并将行编织在一起：

$q1 = "SELECT * FROM look ORDER BY look_id";
$q2 = "SELECT look_id, article.* FROM look INNER JOIN article USING(look_id) ORDER BY look_id, article_id";
$q3 = "SELECT look_id, article_id, attribute.* FROM look INNER JOIN article USING(look_id) INNER JOIN attribute USING(article_id) ORDER BY look_id, article_id, attribute_id";

// loop on looks
// inner loop on article and add to look
// inner loop on attribute and add to article

或者，您可以使用像doctrine这样的现成ORM库。