带有NodeJS和SQL结果的类别和子类别

Question

我正在尝试建立一个具有NodeJS和SQL结果的JSON。

数据库是：

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;

预期的JSON为：

Category:
  Sub-Categories:
    Items:

如果类别没有任何子类别，它将打印：

Category:
  Items:

我们有多个类别，每个类别可以有多个子类别。每个子类别可以有多个项目。

如何使用NodeJS with SQL Query构建JSON结果？

预期JSON：

{
  "menu": {
    "categories": [
      {
        "id_category": 1,
        "category_title": "Colazione",
        "items": [
          {
            "id_item": 1,
            "title": "Cornetto"
          }
        ]
      },
      {
        "id_category": 2,
        "category_title": "Pranzo",
        "subcategories": [
          {
            "title_subcategories": "Primi piatti",
            "items": [
              {
                "id_item": 1,
                "title": "Pasta al sugo 1"
              },
              {
                "id_item": 2,
                "title": "Pasta al sugo 2"
              }
            ]
          },
          {
            "title_subcategories": "Secondi piatti",
            "items": [
              {
                "id_item": 1,
                "title": "Pasta al sugo 3"
              }
            ]
          }
        ]
      }
    ]
  }
}

Answer 1

这是我想出来的，尽管我还没有测试过：

async function buildJson(categories, items) {
    // `query` is some function that queries the DB
    const categries = await query('SELECT id AS id_category, title AS category_title, parent_id FROM categories');
    const items = await query('SELECT id AS id_item, title AS item_title, cat_id FROM items');

    const data = {
        menu: {
            categories: [],
        },
    };

    const subcategories = categories.filter(category => category.parent_id !== null);

    categories.filter(category => category.id_parent === null)
        .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.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;
}