So I want to make the relationships for these 5 tables and do a findAll and get some info from each table. Sorry for the ugly table display

Products table

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

Types table

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

Specs table

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

JctProductColors table

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

This is the relationships I have right now

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

So I want to make the relationships for these 5 tables and do a findAll and get some info from each table. Sorry for the ugly table display

Products table

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

Types table

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

Specs table

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

JctProductColors table

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

This is the relationships I have right now

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

I want to make a findAll to display this

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
					

• javascript
• mysql
• sequelize.js

• I don't know what this mean but +1 for the word sequelize – paparazzo Commented Mar 17, 2016 at 3:00
• @Frisbee sequelize is a nodejs ORM library – George Mauer Commented Mar 17, 2016 at 15:36

1 Answer 1

I know that you asked this a year ago, but I was searching for same the thing and found your unanswered question.

You could do something like this:

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

For more information check here:
http://docs.sequelizejs./en/latest/docs/querying/