问题,想要查出一个类似淘宝购物车的数据集合

一个用户有一个购物车，一个购物车下有多个商店，一个商店下有多个商品 联表查询 1.根据用户id,查出购物车数据， 2.一个购物车下，有多个商店,黑色框 3.商店下有多个商品，蓝色框 4.一个商品，有这个商品的信息，红色框

##现在查出来的集合数据

mysql

根据用户id查询数据

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)
}

根据商店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: "根据用户id查询用户购物车所有数据",
                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之家"
                }
            ],

##我想要的数据集合大概样子， 一个用户有一个购物车，一个购物车下有多个商店，一个商店下有多个商品

            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
                        }
                    ]
                }
            ]

##数据库 ####购物车表 用户id,商品id,商品数量

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;