如何将酒店床位数据按房间分组生成嵌套JSON结构？

我首次提问，希望获取将酒店床位数据库转换为指定嵌套数组格式的思路。

床位存储格式 #

数据库中床位的Symbol字段格式如下：

101A
101B
101C
102A
102B
103A

其中前三位数字代表房间号，A、B、C等字母代表该房间内的床位。

现有查询语句 #

• 列出所有床位的查询：

$stmt = $db->prepare("SELECT PokojID, Symbol, Opis, Lozka, Active 
                        FROM pokoje 
                        WHERE Lozka = :Lozka 
                        OR :Lozka = '0' 
                        AND Active = '1' 
                        ORDER BY Symbol;");

• 仅显示每个房间首个床位的查询：

$stmt = $db->prepare("SELECT PokojID, 
                            Left(Symbol,3) as Symbol, 
                            Opis, count(Lozka) as Lozka, 
                            Active  
                        FROM pokoje 
                        WHERE Lozka = :Lozka 
                        OR :Lozka = '0' 
                        AND Active = '1' 
                        GROUP BY Left(Symbol, 3) 
                        ORDER BY Symbol;");

目标嵌套数组格式 #

希望生成如下结构的嵌套数组（最终转为JSON）：

dp.resources = [
    { 
        name: "Room 101A", id: "1", expanded: true, children:[
            { name : "Room 101B", id : "2" },
            { name : "Room 101C", id : "3" }
        ] 
    },
    { 
        name: "Room 102A", id: "4", expanded: true, children:[
            {name : "Room 102B", id : "4" },
        ] 
    },
    { name: "Room 103a", id: "5" }
];

现有PHP代码 #

$json = file_get_contents('php://input');
$params = json_decode($json);

$Lozka = isset($params->Lozka) ? $params->Lozka : '0';
//Hostel Mode
$stmt = $db->prepare("SELECT PokojID, Symbol, Opis, Lozka, Active 
                        FROM pokoje      
                        WHERE Lozka = :Lozka 
                        OR :Lozka = '0' 
                        AND Active = '1' 
                        ORDER BY Symbol;");
// Hotel Mode
//$stmt = $db->prepare("SELECT PokojID, Left(Symbol,3) as Symbol, Opis, count(Lozka) as Lozka, Active  FROM pokoje WHERE Lozka = :Lozka OR :Lozka = '0' AND Active = '1' GROUP BY Left(Symbol, 3) ORDER BY Symbol;");

$stmt->bindParam(':Lozka', $Lozka); 
$stmt->execute();
$rooms = $stmt->fetchAll();

class Room {}

$result = array();

foreach($rooms as $room) {
    $r = new Room();
    $r->id = $room['PokojID'];
    $r->name = $room['Symbol'];
    $r->capacity = intval($room['Lozka']);
    $r->status = $room['Opis'];
    $result[] = $r;
}

header('Content-Type: application/json');
echo json_encode($result);

步骤1：调整查询语句（保留所有床位数据） #

继续使用Hostel Mode的查询，但需修复WHERE条件的逻辑优先级问题，确保OR条件先执行：

$stmt = $db->prepare("SELECT PokojID, Symbol, Opis, Lozka, Active 
                        FROM pokoje      
                        WHERE (Lozka = :Lozka OR :Lozka = '0') 
                        AND Active = '1' 
                        ORDER BY Symbol;");

步骤2：按房间号分组构建嵌套结构 #

核心逻辑是通过房间号分组，将同一房间的第一个床位作为父节点，其余床位作为子节点，修改PHP处理代码如下：

$json = file_get_contents('php://input');
$params = json_decode($json);

$Lozka = isset($params->Lozka) ? $params->Lozka : '0';
// 修复逻辑优先级后的查询
$stmt = $db->prepare("SELECT PokojID, Symbol, Opis, Lozka, Active 
                        FROM pokoje      
                        WHERE (Lozka = :Lozka OR :Lozka = '0') 
                        AND Active = '1' 
                        ORDER BY Symbol;");
$stmt->bindParam(':Lozka', $Lozka); 
$stmt->execute();
$rooms = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 1. 按房间号分组床位数据
$groupedRooms = [];
foreach ($rooms as $room) {
    $roomNumber = substr($room['Symbol'], 0, 3);
    if (!isset($groupedRooms[$roomNumber])) {
        $groupedRooms[$roomNumber] = [];
    }
    $groupedRooms[$roomNumber][] = $room;
}

// 2. 构建嵌套结构
$result = [];
foreach ($groupedRooms as $roomBeds) {
    $bedCount = count($roomBeds);
    // 处理第一个床位作为父节点
    $firstBed = $roomBeds[0];
    $parentItem = [
        'id' => $firstBed['PokojID'],
        'name' => "Room {$firstBed['Symbol']}",
        'capacity' => intval($firstBed['Lozka']),
        'status' => $firstBed['Opis']
    ];
    // 存在多个床位时添加子节点
    if ($bedCount > 1) {
        $parentItem['expanded'] = true;
        $parentItem['children'] = [];
        // 遍历剩余床位生成子节点
        for ($i = 1; $i < $bedCount; $i++) {
            $childBed = $roomBeds[$i];
            $parentItem['children'][] = [
                'id' => $childBed['PokojID'],
                'name' => "Room {$childBed['Symbol']}",
                'capacity' => intval($childBed['Lozka']),
                'status' => $childBed['Opis']
            ];
        }
    }
    $result[] = $parentItem;
}

header('Content-Type: application/json');
echo json_encode($result);

关键说明 #

1. 逻辑优先级修复：给Lozka = :Lozka OR :Lozka = '0'添加括号，避免AND优先级高于OR导致的查询结果错误。
2. 分组逻辑：通过substr($room['Symbol'], 0, 3)提取房间号，将同一房间的床位归类到同一组。
3. 嵌套结构生成：每组第一个床位作为父节点，多床位时自动添加children数组和expanded: true属性，单床位则直接作为数组元素。

内容的提问来源于stack exchange，提问作者Trim Qemali