将复杂的联接查询转换为使用 findAll

Sequelize complex join query to use a findAll

本文关键字：findAll 转换 查询 复杂      更新时间：2023-09-26

所以我想为这 5 个表建立关系并执行 findAll 并从每个表中获取一些信息。对不起，表格显示丑陋

产品表 

| Field  | Type             | Null | Key | Default | Extra          |  
| id     | int(11) unsigned | NO   | PRI | NULL    | auto_increment |  
| typeId | int(11) unsigned | NO   | MUL | NULL    |                |  
| image  | varchar(255)     | YES  |     | NULL    |                |  
| desc   | text             | YES  |     | NULL    |                |  
| price  | float            | YES  |     | NULL    |                |  
| stock  | int(11)          | YES  |     | NULL    |                |

类型表

| Field | Type         | Null | Key | Default | Extra          |   
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |  
| name  | varchar(255) | NO   |     | NULL    |                |

规格表

| Field     | Type             | Null | Key | Default | Extra |  
| productId | int(11) unsigned | NO   | PRI | NULL    |       |  
| name      | text             | YES  |     | NULL    |       |

Jct产品颜色表

| Field     | Type             | Null | Key | Default | Extra |   
| productId | int(11) unsigned | NO   | PRI | NULL    |       |  
| colorId   | int(11) unsigned | NO   | PRI | NULL    |       |

颜色表

| Field | Type             | Null | Key | Default | Extra          |   
| id  | int(11) unsigned | NO   | PRI | NULL    | auto_increment |   
| name  | varchar(255)     | NO   |     | NULL    |                |

这就是我现在的关系

 Product.belongsTo(Spec, {
   "foreignKey": "id",
   "through": {
     model: "ProductSpec",
     unique: false
   },
   "constraints": false
 });
 Spec.belongsTo(Product, {
   "foreignKey": "productId",
   "through": {
     model: "ProductSpec",
     unique: false
   },
   "constraints": false
 });
 Type.belongsToMany(Product, {
   "constraints": false,
   "foreignKey": "id",
   "through": {
     model: "ProductType",
     unique: false
   }
 });
 Product.belongsTo(Type, {
   "constraints": false,
   "foreignKey": "typeId",
   "through": {
     model: jctProductColor,
     unique: false
   }
 });
 Product.belongsToMany(Color, {
   "constraints": false,
   "foreignKey": "productId",
   "through": {
     model: jctProductColor,
     unique: false
   }
 });
 Color.belongsToMany(Product, {
   "constraints": false,
   "foreignKey": "colorId",
   "through": {
     model: jctProductColor,
     unique: false
   }
 });

我想找一个全部显示这个

select types.name as Type, products.image, products.desc, products.price, products.stock, specs.name as Specs, colors.name as Color from products 
join types 
on types.id = products.typeId 
join specs 
on products.id = specs.productId 
join jctproductcolors 
on jctproductcolors.productId = products.id 
join colors 
on colors.id = jctproductcolors.colorid 
where products.id = :id

我知道

你一年前问过这个问题，但我正在寻找同样的东西，发现了你未回答的问题。

你可以做这样的事情：

models.Product.findAll({
  attributes: ['image', 'desc', 'price', 'stock'],
  include: [{
      model: models.Type,
      attributes: [['name', 'Type']]
    }, {
      model: models.Specs,
      attributes: [['name', 'Specs']]
    }, {
      model: models.JctProductColors,
      include: [{
        model: models.Color,
        attributes: [['name', 'Color']]
      }]
    }
  ],
  where: {
    id: id
  }
});

欲了解更多信息，请查看此处：
http://docs.sequelizejs.com/en/latest/docs/querying/

相关文章：