项目开始

• 主要结合 egg 与 egg-sequelize 多表关联查询.
• 开始项目 mkdir egg-example && cd egg-example npm init egg --type=simple.
• 安装 yarn add egg-sequelize egg-mysql.
• 然后配置 plugin.js 与 config.default.js .

exports.mysql = {
  enable: true,
  package: 'egg-mysql',
}

exports.sequelize = {
  enable: true,
  package: 'egg-sequelize',
}

config.sequelize = {
  dialect: 'mysql',
  host: 'localhost',
  port: '3306',
  user: 'root',
  password: 'root',
  database: 'egg_house',
  define: {
    timestamps: false,
    freezeTableName: true,
  },
}

项目 SQL

--
-- 数据库： `egg_house`
--

-- --------------------------------------------------------

--
-- 表的结构 `comment`
--

CREATE TABLE `comment` (
  `id` int(11) NOT NULL,
  `userId` int(11) NOT NULL COMMENT '用户表id',
  `houseId` int(11) NOT NULL COMMENT '房屋表id',
  `msg` varchar(500) DEFAULT NULL COMMENT '评论内容',
  `createTime` timestamp NULL DEFAULT NULL COMMENT '创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='评论表';

--
-- 转存表中的数据 `comment`
--

INSERT INTO `comment` (`id`, `userId`, `houseId`, `msg`, `createTime`) VALUES
(1, 2, 1, '123123', '2022-11-30 17:02:52'),
(2, 2, 1, 'asd23实打实地方', '2022-11-30 17:10:34'),
(3, 2, 1, '啊实打实地方', '2022-11-30 17:11:54'),
(4, 2, 1, '阿斯顿发斯蒂芬', '2022-11-30 17:12:02'),
(5, 2, 1, 'as34梵蒂冈', '2022-11-30 17:12:09'),
(6, 2, 1, 'as34梵蒂冈', '2022-11-30 17:12:12'),
(7, 2, 1, '567456阿达水电费', '2022-11-30 17:12:17'),
(8, 2, 1, '稍等发送到发斯蒂芬', '2022-11-30 17:19:34'),
(9, 2, 1, '阿斯顿发斯蒂芬', '2022-11-30 17:20:00');

-- --------------------------------------------------------

--
-- 表的结构 `house`
--

CREATE TABLE `house` (
  `id` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL COMMENT '房屋名称',
  `info` varchar(150) DEFAULT NULL COMMENT '房屋简介',
  `address` varchar(200) DEFAULT NULL COMMENT '房屋地址',
  `price` int(11) DEFAULT NULL COMMENT '房屋价格',
  `publishTime` timestamp NULL DEFAULT NULL COMMENT '发布时间',
  `cityCode` varchar(10) NOT NULL COMMENT '城市编码',
  `showCount` int(5) NOT NULL DEFAULT '0' COMMENT '展示次数',
  `startTime` timestamp NULL DEFAULT NULL COMMENT '开始出租时间',
  `endTime` timestamp NULL DEFAULT NULL COMMENT '出租结束时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='房屋表';

--
-- 转存表中的数据 `house`
--

INSERT INTO `house` (`id`, `name`, `info`, `address`, `price`, `publishTime`, `cityCode`, `showCount`, `startTime`, `endTime`) VALUES
(1, '东城民宿', '东区 临近地铁', '东城区', 200, '2020-08-10 05:37:57', '10001', 55, '2020-08-10 05:37:57', '2020-10-20 05:37:57'),
(2, '西城民宿', '西区 临近地铁', '西城区', 100, '2020-08-10 05:38:23', '10001', 3, '2020-08-10 05:37:57', '2020-11-10 05:37:57'),
(3, '新区民宿', '新区民宿位置优越', '新城区', 150, '2020-08-10 05:38:23', '10001', 1, '2020-08-10 05:37:57', '2020-12-10 05:37:57'),
(4, '老城民宿', '老城区风景秀美', '老城区', 100, '2020-08-10 05:38:23', '10001', 1, '2020-08-10 05:37:57', '2020-12-10 05:37:57'),
(5, '西苑民宿', '西苑风景秀美', '西城区', 100, '2020-08-10 05:38:23', '10001', 0, '2020-08-10 05:37:57', '2020-11-10 05:37:57'),
(6, '紫金巷民宿', '紧邻老城区风景秀美', '东城区', 120, '2020-08-10 05:38:23', '10001', 0, '2020-08-10 05:37:57', '2020-11-10 05:37:57'),
(7, '北戴河民宿', '风景秀美适合放松身心', '北城区', 100, '2020-08-10 05:38:23', '10002', 0, '2020-08-10 05:37:57', '2020-12-10 05:37:57'),
(8, '南苑民宿', '南苑民宿风景秀美', '东城区', 150, '2020-08-10 05:38:23', '10001', 0, '2020-08-10 05:37:57', '2020-10-10 05:37:57'),
(9, '北苑民宿', '北苑民宿风景秀美', '北城区', 100, '2020-08-10 05:38:23', '10002', 0, '2020-08-10 05:37:57', '2020-12-10 05:37:57'),
(10, '三厢和民宿', '紧邻地铁交通方便', '东城区', 200, '2020-08-10 05:38:23', '10003', 0, '2020-08-10 05:37:57', '2020-10-10 05:37:57'),
(11, '老城区民宿', '老城区风景秀美', '老城区', 100, '2020-08-10 05:38:23', '10001', 0, '2020-08-10 05:37:57', '2020-12-10 05:37:57'),
(12, '老城区民宿', '老城区风景秀美', '老城区', 100, '2020-08-10 05:38:23', '10001', 0, '2020-08-10 05:37:57', '2020-12-10 05:37:57'),
(13, '老城区民宿', '老城区风景秀美', '老城区', 100, '2020-08-10 05:38:23', '10001', 0, '2020-08-10 05:37:57', '2020-12-10 05:37:57'),
(14, '老城区民宿', '老城区风景秀美', '老城区', 100, '2020-08-10 05:38:23', '10001', 0, '2020-08-10 05:37:57', '2020-12-10 05:37:57'),
(15, '老城区民宿', '老城区风景秀美', '老城区', 100, '2020-08-10 05:38:23', '10001', 0, '2020-08-10 05:37:57', '2020-12-10 05:37:57');

-- --------------------------------------------------------

--
-- 表的结构 `imgs`
--

CREATE TABLE `imgs` (
  `id` int(11) NOT NULL,
  `url` varchar(500) DEFAULT NULL COMMENT '图片地址',
  `houseId` int(11) NOT NULL COMMENT '房屋id',
  `createTime` timestamp NULL DEFAULT NULL COMMENT '创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='图片表';

--
-- 转存表中的数据 `imgs`
--

INSERT INTO `imgs` (`id`, `url`, `houseId`, `createTime`) VALUES
(1, 'http://img3.mukewang.com/szimg/5d1032ab08719e0906000338-360-202.jpg', 1, '2020-08-11 05:37:57'),
(2, 'http://img2.mukewang.com/szimg/5dc9047a09bae31e12000676-360-202.png', 1, '2020-08-11 05:37:57'),
(3, 'http://img2.mukewang.com/szimg/5ad05dc00001eae705400300-360-202.jpg', 1, '2020-08-11 05:37:57'),
(4, 'http://img1.mukewang.com/szimg/5a1f65a900015d1505400300-360-202.jpg', 2, '2020-08-11 05:37:57'),
(5, 'https://img3.mukewang.com/szimg/5f3cdd710925166812000676-360-202.png', 2, '2020-08-11 05:37:57'),
(6, 'https://img4.mukewang.com/szimg/5f20e20109429b5f12000676-360-202.png', 3, '2020-08-11 05:37:57'),
(7, 'http://img3.mukewang.com/szimg/5d1032ab08719e0906000338-360-202.jpg', 4, '2020-08-11 05:37:57'),
(8, 'http://img2.mukewang.com/szimg/5dc9047a09bae31e12000676-360-202.png', 5, '2020-08-11 05:37:57'),
(9, 'http://img2.mukewang.com/szimg/5ad05dc00001eae705400300-360-202.jpg', 6, '2020-08-11 05:37:57'),
(10, 'http://img1.mukewang.com/szimg/5a1f65a900015d1505400300-360-202.jpg', 7, '2020-08-11 05:37:57'),
(11, 'https://img3.mukewang.com/szimg/5f3cdd710925166812000676-360-202.png', 8, '2020-08-11 05:37:57'),
(12, 'https://img4.mukewang.com/szimg/5f20e20109429b5f12000676-360-202.png', 9, '2020-08-11 05:37:57'),
(13, 'http://img3.mukewang.com/szimg/5d1032ab08719e0906000338-360-202.jpg', 10, '2020-08-11 05:37:57'),
(14, 'http://img2.mukewang.com/szimg/5dc9047a09bae31e12000676-360-202.png', 11, '2020-08-11 05:37:57'),
(15, 'http://img2.mukewang.com/szimg/5ad05dc00001eae705400300-360-202.jpg', 12, '2020-08-11 05:37:57'),
(16, 'http://img1.mukewang.com/szimg/5a1f65a900015d1505400300-360-202.jpg', 13, '2020-08-11 05:37:57'),
(17, 'https://img3.mukewang.com/szimg/5f3cdd710925166812000676-360-202.png', 14, '2020-08-11 05:37:57'),
(18, 'https://img4.mukewang.com/szimg/5f20e20109429b5f12000676-360-202.png', 15, '2020-08-11 05:37:57');

-- --------------------------------------------------------

--
-- 表的结构 `orders`
--

CREATE TABLE `orders` (
  `id` int(11) NOT NULL,
  `orderNumber` varchar(20) DEFAULT NULL COMMENT '订单编号',
  `userId` int(11) NOT NULL COMMENT '用户id',
  `houseId` int(11) NOT NULL COMMENT '房屋id',
  `isPayed` int(11) DEFAULT '0' COMMENT '是否支付，0未支付，1已支付',
  `createTime` timestamp NULL DEFAULT NULL COMMENT '创建时间',
  `updateTime` timestamp NULL DEFAULT NULL COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单表';

--
-- 转存表中的数据 `orders`
--

INSERT INTO `orders` (`id`, `orderNumber`, `userId`, `houseId`, `isPayed`, `createTime`, `updateTime`) VALUES
(6, NULL, 2, 1, 1, '2022-12-01 09:04:33', NULL),
(7, NULL, 2, 2, 1, '2022-12-01 09:04:40', NULL),
(8, NULL, 2, 3, 1, '2022-12-01 09:04:43', NULL),
(9, '1234567890', 2, 4, 1, '2022-12-01 09:04:46', NULL);

-- --------------------------------------------------------

--
-- 表的结构 `user`
--

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `username` varchar(20) NOT NULL COMMENT '用户名',
  `password` varchar(50) NOT NULL COMMENT '密码',
  `avatar` longtext COMMENT '头像',
  `phone` varchar(20) DEFAULT NULL COMMENT '手机号',
  `sign` varchar(300) DEFAULT NULL COMMENT '个性签名',
  `createTime` datetime DEFAULT NULL COMMENT '创建时间',
  `updateTime` datetime DEFAULT NULL COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';

--
-- 转存表中的数据 `user`
--

INSERT INTO `user` (`id`, `username`, `password`, `avatar`, `phone`, `sign`, `createTime`, `updateTime`) VALUES
(1, 'hello', '78d5bab38f73179e420f26d1cfcd916f', 'https://xmllein.github.io/article/logo.png', NULL, NULL, '2022-11-30 02:00:13', NULL),
(2, 'hello1', '78d5bab38f73179e420f26d1cfcd916f', 'https://xmllein.github.io/article/logo.png', '123123123', '撒大声地分散到', '2022-11-30 02:11:14', '2022-11-30 09:56:39'),
(3, 'hello2', '78d5bab38f73179e420f26d1cfcd916f', 'https://xmllein.github.io/article/logo.png', NULL, NULL, '2022-11-30 02:36:19', NULL);

用户列表查询

• 单表查询

router.get('/api/user/lists', controller.user.lists)

// 用户列表
  async lists() {
    const { ctx } = this
    const params = ctx.params()
    const result = await ctx.service.user.getUserList({
      ...params,
      pageSize: parseInt(params.pageSize),
      pageNum: parseInt(params.pageNum),
    })
    if (result) {
      this.success({
        list: result,
      })
    } else {
      this.error('获取失败')
      return
    }
  }

// 用户列表
  async getUserList() {
    return this.run(async () => {
      const { ctx } = this
      const result = await ctx.model.User.findAll({
        attributes: { exclude: ['password'] },
      })
      return result
    })
  }

• 结果如下:

房子列表查询

• 房子列表 关联到 图片表.
• 图片表 houseId 关联房子.
• 表模型如下

module.exports = (app) => {
  const { STRING, INTEGER, DATE } = app.Sequelize

  const House = app.model.define('house', {
    id: { type: INTEGER, primaryKey: true, autoIncrement: true },
    name: STRING(50),
    info: STRING(150),
    address: STRING(200),
    price: INTEGER,
    publishTime: {
      type: DATE,
      get() {
        return new Date(this.getDataValue('publishTime')).getTime()
      },
    },
    cityCode: STRING,
    showCount: INTEGER,
    startTime: {
      type: DATE,
      get() {
        return new Date(this.getDataValue('startTime')).getTime()
      },
    },
    endTime: {
      type: DATE,
      get() {
        return new Date(this.getDataValue('endTime')).getTime()
      },
    },
  })

  // 一个房子对应多个图片， hasmany
  House.associate = () => {
    app.model.House.hasMany(app.model.Imgs, { foreignKey: 'houseId' })
  }

  return House
}

module.exports = (app) => {
  const { STRING, INTEGER, DATE } = app.Sequelize

  const Imgs = app.model.define('imgs', {
    id: { type: INTEGER, primaryKey: true, autoIncrement: true },
    url: STRING(500),
    houseId: INTEGER,
    createTime: DATE,
  })

  return Imgs
}

• 热门房源搜索

router.get('/api/house/hot', controller.house.hot)

 async hot() {
    const { ctx, app } = this
    const result = await ctx.service.house.hot()
    this.success(result)
  }

 commontAttrs(app) {
    return {
      order: [['showCount', 'DESC']],
      attributes: {
        exclude: ['startTime', 'endTime', 'publishTime'],
      },
      include: [
        {
          model: app.model.Imgs,
          limit: 1,
          attributes: ['url'],
        },
      ],
    }
  }
  async hot() {
    return this.run(async (ctx, app) => {
      const result = await ctx.model.House.findAll({
        limit: 4,
        ...this.commontAttrs(app),
      })
      return result
    })
  }

• 结果如下:

订单列表查询

• 订单关联 房源。
• 房源 关联 图片。
• 订单模型：

// 订单模型
module.exports = (app) => {
  const { STRING, INTEGER, DATE } = app.Sequelize

  const Orders = app.model.define('orders', {
    id: { type: INTEGER, primaryKey: true, autoIncrement: true },
    orderNumber: STRING(20),
    userId: INTEGER,
    houseId: INTEGER,
    isPayed: INTEGER,
    createTime: {
      type: DATE,
      get() {
        return new Date(this.getDataValue('createTime')).getTime()
      },
    },
    updateTime: {
      type: DATE,
      get() {
        return new Date(this.getDataValue('updateTime')).getTime()
      },
    },
  })

  // 关联图片信息
  Orders.associate = function () {
    app.model.Orders.belongsTo(app.model.House, {
      foreignKey: 'houseId',
      as: 'house',
    })
  }

  return Orders
}

• 订单列表查询

router.get('/api/orders/lists', userExist, controller.orders.lists)

 // 订单列表
  async lists() {
    const { ctx, app } = this
    const result = await ctx.service.orders.lists({
      ...ctx.params(),
      userId: ctx.userId,
    })
    this.success(result)
  }

// 订单列表
  async lists(params) {
    return this.run(async (ctx, app) => {
      const result = await ctx.model.Orders.findAll({
        where: {
          userId: params.userId,
          isPayed: params.type,
        },
        limit: params.pageSize,
        offset: (params.pageNum - 1) * params.pageSize,
        include: [
          {
            model: app.model.House,
            as: 'house',
            include: [
              {
                model: app.model.Imgs,
                attributes: ['url'],
                limit: 1,
              },
            ],
          },
        ],
      })
      return result
    })
  }

• 结果如下:

参考资料

• Sequelize 中文文档
• Sequelize 英文文档
• Egg.js 官网
• Egg.js 快速入门 200 分钟掌握企业级框架开发和应用
• React+React Hook+Egg 造轮子 全栈开发旅游电商应用