node+mysql,怎么生成如下購(gòu)物車接口（數(shù)組包對(duì)象）,如下圖和代碼

問題,想要查出一個(gè)類似淘寶購(gòu)物車的數(shù)據(jù)集合

一個(gè)用戶有一個(gè)購(gòu)物車，一個(gè)購(gòu)物車下有多個(gè)商店，一個(gè)商店下有多個(gè)商品
聯(lián)表查詢
1.根據(jù)用戶id,查出購(gòu)物車數(shù)據(jù)，
2.一個(gè)購(gòu)物車下，有多個(gè)商店,黑色框
3.商店下有多個(gè)商品，藍(lán)色框
4.一個(gè)商品，有這個(gè)商品的信息，紅色框

現(xiàn)在查出來的集合數(shù)據(jù)

mysql

根據(jù)用戶id查詢數(shù)據(jù)

let cartSelectGroupByUserId = (userId) => {
    let sql = `select a.cart_goods_number,c.goods_id,c.goods_name,c.goods_img,c.goods_price,d.shop_id,d.shop_name from cart a,user b,goods c,shop d where a.user_id = b.user_id and a.goods_id = c.goods_id and c.shop_id = d.shop_id and a.user_id =${userId};`
    return query(sql)
}

根據(jù)商店id查詢商店名

let shopSelectNameById = (id) => {
    let sql = `select shop_name from shop where shop_id=${id};`
    return query(sql)
}

接口

const cartSelectGroupByUserId = async(ctx) => {
    let id = ctx.params.id;
    let shopId;
    await cartModel.cartSelectGroupByUserId(id)
        .then(result => {
            console.log(result)
            shopId = result[0].shop_id
        })
    await cartModel.shopSelectNameById(shopId)
        .then(result => {
            ctx.body = {
                status: 200,
                msg: "根據(jù)用戶id查詢用戶購(gòu)物車所有數(shù)據(jù)",
                data: result
            }
        })
        .catch(error => {
            console.log(error);
            ctx.body = false;
        })
}

            data: [
                {
                    cart_goods_number: 3,
                    goods_id: 3,
                    goods_name: "honor7x",
                    goods_img: "honor7x.png",
                    goods_price: 1299,
                    shop_id: 4,
                    shop_name: "honor之家"
                },
                {
                    cart_goods_number: 2,
                    goods_id: 4,
                    goods_name: "honor6x",
                    goods_img: "honor6x.png",
                    goods_price: 1199,
                    shop_id: 4,
                    shop_name: "honor之家"
                },
                {
                    cart_goods_number: 1,
                    goods_id: 1,
                    goods_name: "iPhonex",
                    goods_img: "iPhonex.png",
                    goods_price: 8888,
                    shop_id: 1,
                    shop_name: "iPhone之家"
                }
            ],

我想要的數(shù)據(jù)集合大概樣子，

一個(gè)用戶有一個(gè)購(gòu)物車，一個(gè)購(gòu)物車下有多個(gè)商店，一個(gè)商店下有多個(gè)商品

            data1: [
                {
                    shop_id: 4,
                    shop_name: "honor之家",
                    children: [
                        {
                            cart_goods_number: 3,
                            goods_id: 3,
                            goods_name: "honor7x",
                            goods_img: "honor7x.png",
                            goods_price: 1299
                        },
                        {
                            cart_goods_number: 2,
                            goods_id: 4,
                            goods_name: "honor6x",
                            goods_img: "honor6x.png",
                            goods_price: 1199
                        }
                    ]
                },
                {
                    shop_id: 1,
                    shop_name: "iPhone之家",
                    children: [
                        {
                            cart_goods_number: 1,
                            goods_id: 1,
                            goods_name: "iPhonex",
                            goods_img: "iPhonex.png",
                            goods_price: 8888
                        }
                    ]
                }
            ]

數(shù)據(jù)庫(kù)

購(gòu)物車表

用戶id,商品id,商品數(shù)量

CREATE TABLE `cart` (
  `cart_id` int(10) NOT NULL AUTO_INCREMENT,
  `user_id` int(10) DEFAULT NULL,
  `goods_id` int(10) DEFAULT NULL,
  `cart_goods_number` int(10) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `last_edit_time` datetime DEFAULT NULL,
  PRIMARY KEY (`cart_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

用戶表

CREATE TABLE `user` (
  `user_id` int(10) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(10) DEFAULT NULL,
  `user_password` varchar(10) DEFAULT NULL,
  `user_img` varchar(1024) DEFAULT NULL,
  `user_phone` varchar(11) DEFAULT NULL,
  `user_sex` int(2) NOT NULL DEFAULT '1' COMMENT '1:男,2:女',
  `user_address` varchar(100) DEFAULT NULL,
  `user_state` int(2) NOT NULL DEFAULT '1' COMMENT '1:允許使用,2:禁止使用',
  `create_time` datetime DEFAULT NULL,
  `last_edit_time` datetime DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `user_name` (`user_name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

商品表

CREATE TABLE `goods` (
  `goods_id` int(10) NOT NULL AUTO_INCREMENT,
  `goods_name` varchar(10) DEFAULT NULL,
  `goods_img` varchar(1024) DEFAULT NULL,
  `goods_price` double DEFAULT NULL,
  `goods_number` int(10) DEFAULT NULL,
  `goods_priority` int(2) NOT NULL DEFAULT '1',
  `goods_state` int(2) NOT NULL DEFAULT '1' COMMENT '1:允許使用,2:禁止使用',
  `shop_id` int(10) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `last_edit_time` datetime DEFAULT NULL,
  `goods_desc` varchar(1024) DEFAULT NULL,
  PRIMARY KEY (`goods_id`),
  UNIQUE KEY `goods_name` (`goods_name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

商店表

CREATE TABLE `shop` (
  `shop_id` int(10) NOT NULL AUTO_INCREMENT,
  `shop_name` varchar(10) DEFAULT NULL,
  `shop_phone` varchar(11) DEFAULT NULL,
  `shop_img` varchar(1024) DEFAULT NULL,
  `shop_category_id` int(10) DEFAULT NULL,
  `shop_priority` int(2) NOT NULL DEFAULT '1',
  `shop_state` int(2) NOT NULL DEFAULT '1' COMMENT '1:允許使用,2:禁止使用',
  `create_time` datetime DEFAULT NULL,
  `last_edit_time` datetime DEFAULT NULL,
  `shop_desc` varchar(1024) DEFAULT NULL,
  PRIMARY KEY (`shop_id`),
  UNIQUE KEY `shop_name` (`shop_name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;