node.js - NodeJS 使用 SQL 结果构建 JSON

Question

我得到了这个数据库：

CREATE TABLE category (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  title varchar(255) NOT NULL,
  parent_id int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (parent_id) REFERENCES category (id) 
    ON DELETE CASCADE ON UPDATE CASCADE
);


CREATE TABLE `items` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `cat_id` int unsigned DEFAULT NULL,
  `parent_id` int unsigned DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `cat_id` (`cat_id`),
  KEY `sub_id` (`parent_id`),
  CONSTRAINT `cat_id` FOREIGN KEY (`cat_id`) REFERENCES `category` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `sub_id` FOREIGN KEY (`parent_id`) REFERENCES `category` (`parent_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

BEGIN;
INSERT INTO `category` VALUES (1, 'Colazione', NULL);
INSERT INTO `category` VALUES (2, 'Pranzo', NULL);
INSERT INTO `category` VALUES (3, 'Primi piatti', 2);
INSERT INTO `category` VALUES (4, 'Second dish', 2);
INSERT INTO `category` VALUES (5, 'Other things for lunch', 2);
COMMIT;

-- ----------------------------
-- Records of items
-- ----------------------------
BEGIN;
INSERT INTO `items` VALUES (1, 1, NULL, 'Cornetto');
INSERT INTO `items` VALUES (2, 3, 2, 'Pasta al sugo 1');
INSERT INTO `items` VALUES (3, 3, 2, 'Pasta al sugo 2');
INSERT INTO `items` VALUES (4, 3, 2, 'Pasta al sugo 3');
INSERT INTO `items` VALUES (5, 3, 2, 'Pasta al sugo 1 X');
INSERT INTO `items` VALUES (6, 3, 2, 'Pasta al sugo 2 X');
INSERT INTO `items` VALUES (7, 4, 2, 'Pasta al sugo 3 X');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

我实际用于获取数据的查询是：

    const categories = 'SELECT id AS id_category, title AS category_title, parent_id FROM category';
    const items = 'SELECT id AS id_item, name AS item_title, cat_id FROM items';

创建 JSON 的代码是这样的：

    async function buildJson(categories, items) {
    const data = {
        menu: {
            categories: [],
        },
    };

   //console.log(categories);
   //console.log(items);
    const subcategories = categories.filter(category => category.parent_id !== null);
    
    //categories.filter(category => category.id_parent === null)
        categories.forEach(category => {
           data.menu.categories.push({
                id_category: category.id_category,
                category_title: category.category_title,
            });
            data.menu.categories.forEach(_category => {
                _category.items = items.filter(item => item.cat_id === _category.id_category)
                    .map(item => ({
                        id_item: item.id_item,
                        title: item.title,
                    }));

                _category.subcategories = categories.filter(__category => __category.parent_id === _category.id_category);
                
                _category.subcategories.forEach(subcategory => {
                    subcategory.items = items.filter(item => item.cat_id === subcategory.id_category)
                        .map(item => ({
                            id_item: item.id_item,
                            title: item.title,
                        }));
                });
            });
        });
    return data;
}

显示子类别的 json，但它也将子类别显示为类别，如重复。

现在返回的json是：

{
  "result": {
    "menu": {
      "categories": [
        {
          "id_category": 1,
          "category_title": "Colazione",
          "items": [
            {
              "id_item": 1
            }
          ],
          "subcategories": []
        },
        {
          "id_category": 2,
          "category_title": "Pranzo",
          "items": [],
          "subcategories": [
            {
              "id_category": 3,
              "category_title": "Primi piatti",
              "parent_id": 2,
              "items": [
                {
                  "id_item": 2
                },
                {
                  "id_item": 4
                }
              ]
            },
            {
              "id_category": 4,
              "category_title": "Secondi piatti",
              "parent_id": 2,
              "items": [
                {
                  "id_item": 3
                }
              ]
            },
            {
              "id_category": 5,
              "category_title": "Contorni",
              "parent_id": 2,
              "items": []
            }
          ]
        },
        {
          "id_category": 3,
          "category_title": "Primi piatti",
          "items": [
            {
              "id_item": 2
            },
            {
              "id_item": 4
            }
          ],
          "subcategories": []
        },
        {
          "id_category": 4,
          "category_title": "Secondi piatti",
          "items": [
            {
              "id_item": 3
            }
          ],
          "subcategories": []
        },
        {
          "id_category": 5,
          "category_title": "Contorni",
          "items": [],
          "subcategories": []
        }
      ]
    }
  }
}

我该如何解决？我需要json没有重复数据

标签： node.js