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

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

bVbdCxW.png

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

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

bVbdCvY.png

 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;