如何基于数据库生成含分类布尔值的指定格式JSON？

你需要生成的JSON中，每个品牌对象需要包含categories字段——该字段是一个以分类ID为键、true/false为值的对象，用来表示品牌是否关联对应分类。现有代码已经能获取品牌的基础信息，但缺少这部分逻辑，下面是完善后的代码和思路：

解决方案思路 #

1. 先获取所有分类的ID，为每个品牌初始化categories对象，默认所有分类值为false
2. 遍历从数据库查询到的关联数据，将品牌实际关联的分类对应值设为true
3. 将处理好的categories字段添加到每个品牌的数组中

完善后的完整代码 #

public function actionBrand($id = null) {
    if (empty($id)) {
        // Obtiene datos de la base
        $sql = "SELECT DISTINCT objects.id_brand AS id, brands.name AS name, brands.description AS description, brands.icon AS icon, brands.url AS url, objects.id_category " .
        "FROM objects " .
        "LEFT JOIN brands ON objects.id_brand = brands.id " .
        "LEFT JOIN categories ON objects.id_category = categories.id " .
        "ORDER BY objects.id_brand, objects.id_category ";
    } else {
        // Obtiene datos de la base
        $sql = "SELECT DISTINCT objects.id_brand AS id, brands.name AS name, brands.description AS description, brands.icon AS icon, brands.url AS url, objects.id_category " .
        "FROM objects " .
        "LEFT JOIN brands ON objects.id_brand = brands.id " .
        "LEFT JOIN categories ON objects.id_category = categories.id " .
        "WHERE brands.id = " . (int) $id . " " .
        "ORDER BY objects.id_brand, objects.id_category ";
    }
    $data = Yii::$app->db->createCommand($sql)->queryAll();
    // Obtiene todas las categorias para inicializar el objeto
    $categoryIds = Yii::$app->db->createCommand('SELECT id FROM categories ORDER BY id')->queryColumn();
    
    $brands = [];
    if (!empty($data)) {
        // 先初始化每个品牌的基础信息和默认categories
        foreach ($data as $item) {
            $brandId = $item['id'];
            // 如果该品牌还没加入数组，初始化它
            if (!isset($brands[$brandId])) {
                // 初始化categories为所有false
                $categories = [];
                foreach ($categoryIds as $cid) {
                    $categories[(string)$cid] = 'false';
                }
                $brands[$brandId] = [
                    'id' => $brandId,
                    'name' => $item['name'],
                    'description' => $item['description'],
                    'icon' => $item['icon'],
                    'url' => $item['url'],
                    'categories' => $categories
                ];
            }
            // 将当前关联的分类设为true
            if (!empty($item['id_category'])) {
                $brands[$brandId]['categories'][(string)$item['id_category']] = 'true';
            }
        }
        // 将关联数组转为索引数组，符合JSON结构要求
        $brands = array_values($brands);
    }
    
    // Construye y envia JSON
    $json['content']['brands'] = count($brands);
    $json['brands'] = $brands;
    echo json_encode($json);
}

关键修改说明 #

• 优化SQL查询：去掉了不需要的categories.name字段，减少不必要的数据传输
• 初始化categories：通过queryColumn()直接获取所有分类ID数组，为每个品牌生成默认全为false的categories对象
• 遍历关联数据：在遍历查询结果时，先判断品牌是否已初始化，未初始化则创建基础信息和默认categories；然后将当前行的分类ID对应值设为true
• 数组格式转换：最后用array_values()将关联数组转为索引数组，保证JSON输出的结构完全符合需求

这样修改后，生成的JSON就会包含每个品牌对应的categories键值对，完全匹配你需要的格式。

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