CODEWITHSUNDEEP
postgresqlsequelize.js
jerrylow
jerrylow

Reputation: 2629

Using parent model in include as condition in Sequelize

I can't find the proper documentation, but what I want to do is limit includes based on an ID from top level model. I can't put it to words but this is what I'm hoping to achieve.

db.A.findAll({
  as: 'A',
  where: {
    ...
  },
  include: [{
    model: B,
    include: [{
      model: C,
      where: {
        color_id: A.color_id
      }
    }]
  }]
}

A is associated to B and B has many C. I'm trying to limit the amount of C's getting fetched because it causes the the query to be really slow.

Edit, add model association:

const A = sequelize.define('A', {
  id: {
    type: DataTypes.INTEGER,
    allowNull: false,
    primaryKey: true,
    autoIncrement: true,
    field: 'id'
  }, {
    tableName: 'a',
    underscored: true,
    indexes: [
      { fields: ['b'] }
    ]
  }
});

A.associate = function (models) {
  A.belongsTo(models.B);
  A.belongsTo(models.D);
}

const B = sequelize.define('B', {
  ...non-related-columns
});

B.associate = function (models) {
  B.hasMany(models.C);
  B.belongsTo(models.D);
  B.hasMany(models.E);
  B.hasMany(models.F);
}

const C = sequelize.define('C', {
  ...non-related-columns
});

C.associate = function (models) {
  C.belongsTo(models.B);
  C.belongsTo(models.D);
}

Upvotes: 0

Views: 2796

Answers (1)

Kevin K
Kevin K

Reputation: 2227

UPDATE: Is this what you are looking for?

B.findOne({
  where: {
    id: a.bId
  },
  include: [{ all: true, nested: true }]
})

My previous answer:

I think this should work. I've simplified the problem a bit.

const A = db.define('a', {
  name: Sequelize.STRING
})

const B = db.define('b', {
  name: Sequelize.STRING
})

const C = db.define('c', {
  type: Sequelize.STRING
})

A.belongsTo(B);
B.hasOne(A);

B.hasMany(C);
C.hasOne(B);

// given an A, find all Cs of type 'foo'

B.findOne({
  where: {
    id: a.bId
  }
})
.then(b => {
  return C.findAll({
    where: {
      bId: b.id,
      type: 'foo'
    }
  })
})
.the(foosWhichBelongToAThrougB => {
  //do something with your array of foos
})

Instead of trying to grab it all with one query, I've split it into two queries. One to find the B associated with A and then one to find all the Cs associated with B and which match something in the C model.

Upvotes: 2

Related Questions