CODEWITHSUNDEEP
javascriptnode.jssequelize.js
Rob
Rob

Reputation: 11368

Sequelize.js query to get total count through relationship

I'm using sequelize to get a total count through a relationship. I need it by a customerId that is in a parent table joined through a pivot table. The plain query looks something like this:

SELECT count(p.*) FROM parcels as p
LEFT JOIN orders_parcels as op ON op."parcelId" = p.id
LEFT JOIN orders as o ON op."orderId" = o.id
WHERE o."customerId"=1

This works fine. But not sure how to get the sequelize query.

Parcel.findAndCountAll();

EDIT: OrderParcel

var OrderParcel = service.sequelize.define('OrderParcel', {

    id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
    }
}, {
    tableName: 'orders_parcels',
    freezeTableName: true,
    paranoid: true
});

module.exports = OrderParcel;

var Order = require('./Order');

OrderParcel.belongsTo(Order, {
    as: 'Order',
    foreignKey: 'orderId'
});

var Parcel = require('../parcel/Parcel');

OrderParcel.belongsTo(Parcel, {
    as: 'Parcel',
    foreignKey: 'parcelId'
});

Upvotes: 4

Views: 6371

Answers (3)

Yaroslav Bai
Yaroslav Bai

Reputation: 1086

I totally agree with Evan Siroky's approach yet the code has to be simplified to work properly: 

Parcel.findAndCountAll({
include: [{
  model: Order,
  where: {
    customerId: idNum
  },
  duplicating: false // Add this line for retrieving all objects
}]
}).then(function(result) { 
   console.log('Rows: ' + result.rows + ' Count: ' + result.count)
});

Remember to connect your models with belongsToMany method!

Upvotes: 0

Evan Siroky
Evan Siroky

Reputation: 9408

Assuming that you've defined the associations, you can use Model.findAndCountAll. It'd look something like this:

Parcel.findAndCountAll({
  include: [{
    model: OrderParcel,
    required: true,
    include: [{
      model: Order,
      where: {
        customerId: idNum
      }
    }]
  }]
}).then(function(result) { 

});

Upvotes: 0

BrTkCa
BrTkCa

Reputation: 4783

One way is to use sequelize.query:

As there are often use cases in which it is just easier to execute raw / already prepared SQL queries, you can utilize the function sequelize.query.

var query = "SELECT count(p.*) FROM parcels as p" +
" LEFT JOIN orders_parcels as op ON op."parcelId" = p.id" +
" LEFT JOIN orders as o ON op."orderId" = o.id" +
" WHERE o.customerId=1;";

sequelize.query(query, { type: sequelize.QueryTypes.SELECT}).success(function(count){
    console.log(count); // It's show the result of query          
    res.end();
}).catch(function(error){            
    res.send('server-error', {error: error});
});

Raw Queries docs

Upvotes: 1

Related Questions