如何将SQL子查询结果整合为客户关联销售记录的主数组

我在SQL数据库中有两张表：

• booking表：存储客户详情
• booking_line表：存储客户的购买产品历史

我希望生成一个按客户维度的嵌套数组，每个客户条目下包含其所有购买详情（命名为Sale的子数组），期望的数组格式如下：

array(
        [0] => array(
                 [id] => 67
                 [name] => Neha
                 [mobile_no] => xxxxxxxxx
                 [gender] => Female
                 [Sale] => Array
                    (
                        [id] => 1
                        [booking_id] => 67
                        [barcode] => 1368
                        [product_name] => Custom Print Mattel Rakhi BE
                        [p_price] => 35
                        [price] => 100
                   )
                [Sale] => Array
                    (
                        [id] => 2
                        [booking_id] => 67
                        [barcode] => 1368
                        [product_name] => Custom Print Mattel Rakhi BE
                        [p_price] => 35
                        [price] => 100
                   )
         )
       [1] => array(
                     [id] => 68
                     [name] => Nehaxx
                     [mobile_no] => xxxxxxxxx
                     [gender] => Female
             )
)

但目前得到的输出格式不符合预期，客户和购买记录是平级的数组元素：

array(
        [0] => array(
                 [id] => 67
                 [name] => Neha
                 [mobile_no] => xxxxxxxxx
                 [gender] => Female
         )
        [1] => array(
           [Sale] => Array
                (
                    [id] => 1
                    [booking_id] => 67
                    [barcode] => 1368
                    [product_name] => Custom Print Mattel Rakhi BE
                    [p_price] => 35
                    [price] => 100
               )
         )
        [2] => array(
                 [id] => 68
                 [name] => Nehaxx
                 [mobile_no] => xxxxxxxxx
                 [gender] => Female
         )
         [3] => array(
           [Sale] => Array
                (
                    [id] => 1
                    [booking_id] => 67
                    [barcode] => 1368
                    [product_name] => Custom Print Mattel Rakhi BE
                    [p_price] => 35
                    [price] => 100
               )
         )
)

现有PHP代码如下：

$sqlA = "SELECT * FROM booking";
$resultA = $conn->query($sqlA);
$dataA = array();

while($rowA = $resultA->fetch_assoc()) {
    $sql = "SELECT * FROM booking_line WHERE booking_id=".$rowA["id"]."";
    $result = $conn->query($sql);
    $dataA[] = $rowA;
    
    while($row = $result->fetch_assoc()) {   
        $dataA[] = array("Sale" => $row);
    }
}
print_r($dataA);

原代码的问题是把客户数据和购买记录分别追加到了$dataA的顶层，没有将购买记录嵌套到客户数组内部。同时要注意：PHP数组不允许重复键名，原期望输出中同一个客户下的多个Sale键会被覆盖，所以需要把Sale设为索引数组来存储多条购买记录。

修改后的基础版代码 #

$sqlA = "SELECT * FROM booking";
$resultA = $conn->query($sqlA);
$dataA = array();

while($rowA = $resultA->fetch_assoc()) {
    // 初始化客户数组，包含基础信息
    $customer = $rowA;
    // 初始化Sale子数组，用来存放当前客户的所有购买记录
    $customer['Sale'] = array();
    
    $sql = "SELECT * FROM booking_line WHERE booking_id=".$rowA["id"]."";
    $result = $conn->query($sql);
    
    while($row = $result->fetch_assoc()) {   
        // 将每条购买记录追加到客户的Sale子数组中
        $customer['Sale'][] = $row;
    }
    
    // 将完整的客户数组追加到结果数组
    $dataA[] = $customer;
}
print_r($dataA);

修改后生成的数组结构：

array(
    [0] => array(
        [id] => 67
        [name] => Neha
        [mobile_no] => xxxxxxxxx
        [gender] => Female
        [Sale] => Array(
            [0] => Array(
                [id] => 1
                [booking_id] => 67
                [barcode] => 1368
                [product_name] => Custom Print Mattel Rakhi BE
                [p_price] => 35
                [price] => 100
            ),
            [1] => Array(
                [id] => 2
                [booking_id] => 67
                [barcode] => 1368
                [product_name] => Custom Print Mattel Rakhi BE
                [p_price] => 35
                [price] => 100
            )
        )
    ),
    [1] => array(
        [id] => 68
        [name] => Nehaxx
        [mobile_no] => xxxxxxxxx
        [gender] => Female
        [Sale] => Array() // 无购买记录时为空数组
    )
)

性能优化版（解决N+1查询问题） #

原代码每个客户都执行一次查询，会导致性能问题，建议用LEFT JOIN一次性获取所有数据，再在PHP中分组：

// 用LEFT JOIN一次性获取客户及对应购买记录
$sql = "SELECT b.*, bl.id AS bl_id, bl.booking_id, bl.barcode, bl.product_name, bl.p_price, bl.price 
        FROM booking b 
        LEFT JOIN booking_line bl ON b.id = bl.booking_id";
$result = $conn->query($sql);

$dataA = array();
while($row = $result->fetch_assoc()) {
    $bookingId = $row['id'];
    // 如果客户未加入结果数组，先初始化基础信息和Sale子数组
    if(!isset($dataA[$bookingId])) {
        $dataA[$bookingId] = array(
            'id' => $row['id'],
            'name' => $row['name'],
            'mobile_no' => $row['mobile_no'],
            'gender' => $row['gender'],
            'Sale' => array()
        );
    }
    // 仅当存在购买记录时，添加到Sale数组
    if(!empty($row['bl_id'])) {
        $dataA[$bookingId]['Sale'][] = array(
            'id' => $row['bl_id'],
            'booking_id' => $row['booking_id'],
            'barcode' => $row['barcode'],
            'product_name' => $row['product_name'],
            'p_price' => $row['p_price'],
            'price' => $row['price']
        );
    }
}
// 将关联数组转为索引数组
$dataA = array_values($dataA);
print_r($dataA);

内容的提问来源于stack exchange，提问作者Abhinav Goutam