Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Sequelize complex join query to use a findAll

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
					
like image 483
Jamie Keelen Avatar asked Mar 17 '16 02:03

Jamie Keelen


People also ask

How do I create a JOIN query in Sequelize?

There are two ways you can create JOIN queries and fetch data from multiple tables with Sequelize: Create raw SQL query using sequelize. query() method. Associate related Sequelize models and add the include option in your Sequelize query method.

What does findAll return Sequelize?

The Sequelize findAll() method is used to query data from your SQL table to your JavaScript application. The method will return your table rows as an array of objects. The findAll() method can be called from a Model that represents the table in your database.

How do I JOIN two tables in node JS?

Join Two or More Tables You can combine rows from two or more tables, based on a related column between them, by using a JOIN statement.


1 Answers

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.com/en/latest/docs/querying/

like image 85
Rafael Delboni Avatar answered Oct 07 '22 17:10

Rafael Delboni