Sequelize复杂联接查询，使用findAll方法

Question

所以我想为这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    |       |

JctProductColors表

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

Colors table

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

我希望进行findAll操作以显示此内容。

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
     

- Jamie Keelen

---

我不知道这是什么意思，但是对于单词sequelize给一个加1。 - paparazzo

@Frisbee sequelize 是一个 Node.js 的 ORM 库。 - George Mauer

Answer 1

我知道你一年前就提出了这个问题，但我最近也在寻找同样的东西，并发现了你未回答的问题。

你可以像这样做：

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/

（注：该链接是关于查询的Sequelize文档）

- Rafael Delboni