Sequelize nested eager loading does not load both records associated with the same model

Question

I am trying to have an api route return a large object with nested eager loading in sequelize. My "Lane" model has two associations to the "Location" model as 'origin_location_id' and 'destination_location_id'. I am trying to return both the 'origin_location_id' and 'destination_location_id' records but I am only getting the 'destination_location_id' record.

I have tried reversing the associations in the model definitions, multiple variations of syntax in the model definitions using "as" and "through" and in the query, and I continue to either get no response from the server, a nondescript (not even an error code or error description) sequelize eager loading error, or I only see one Location record loading in the json results.

Here is the query:

(function(req,res) {
models.Trip.findAll({
  attributes: ['id', 'createdAt'],
  include: [
    { model: models.Customer, attributes: ['id', 'name', 'email', 'address', 'phone'] },
    { model: models.Move, attributes: ['id', 'trip_id'], include: [
      { model: models.Lane,  attributes: ['origin_location_id', 'destination_location_id', 'duration', 'distance'], include: [
        { model: models.Location, attributes: ['id', 'tookan_id', 'name', 'address', 'email', 'phone'] }
      ] }
    ] }
 ],
order:[['createdAt', 'DESC']]}).then(trips => {return res.json(trips)}).catch(err => res.status(422).json(err))})

Here is the Lane model definition:

'use strict';
module.exports = (sequelize, DataTypes) => {
  const Lane = sequelize.define('Lane', {
    active: {
      type: DataTypes.TINYINT,
      defaultValue: true },
    customer_id: DataTypes.INTEGER,
    description: DataTypes.TEXT,
    origin_location_id: DataTypes.INTEGER,
    destination_location_id: DataTypes.INTEGER,
    distance: DataTypes.INTEGER,
    duration: DataTypes.INTEGER,
    driver_base_pay: DataTypes.DECIMAL,
    driver_return_pay: DataTypes.DECIMAL,
    tolls: DataTypes.DECIMAL,
    driver_pay_per_minute: DataTypes.DECIMAL,
    driver_pay_per_kilometer: DataTypes.DECIMAL,
    average_drive_speed: DataTypes.DECIMAL
  }, {});
  Lane.associate = function(models) {
    models.Lane.belongsTo(models.Customer, {foreignKey: 'customer_id'});
    models.Lane.belongsTo(models.Location, {foreignKey: 'origin_location_id'});
    models.Lane.belongsTo(models.Location, {foreignKey: 'destination_location_id'});
    models.Lane.hasMany(models.Move, {foreignKey: 'lane_id'});
  };
  return Lane;
};

Here is the Location model definition:

'use strict';
module.exports = (sequelize, DataTypes) => {
  const Location = sequelize.define('Location', {
    tookan_id : DataTypes.INTEGER,
    active: {
      type: DataTypes.TINYINT,
      defaultValue: true },
    customer_id: DataTypes.INTEGER,
    name: DataTypes.TEXT,
    address: DataTypes.TEXT,
    email: DataTypes.TEXT,
    phone: DataTypes.TEXT
  }, {});
  Location.associate = function(models) {
    models.Location.belongsTo(models.Customer, {foreignKey: 'customer_id'});
    models.Location.hasMany(models.Lane, {foreignKey: 'origin_location_id'});
    models.Location.hasMany(models.Lane, {foreignKey: 'destination_location_id'});
  };
  return Location;
};

Here is the current JSON result structure (only one Location record is returned for each Lane record, despite that it is associated with Locations at two fields): { "id": 27, "createdAt": "2018-09-20T12:30:32.000Z", "Customer": { "id": 1, "name": "", "email": "", "address": "", "phone": "" }, "Moves": [{ "id": 29, "trip_id": 27, "Lane": { "id": 4, "origin_location_id": 3, "destination_location_id": 1, "duration": 1260, "distance": 21082, "driver_base_pay": "20", "driver_return_pay": "3", "driver_pay_per_kilometer": "1", "average_drive_speed": "18", "Location": { "id": 1, "tookan_id": null, "name": "", "address": "", "email": "", "phone": "" } } }, { "id": 26, "trip_id": 27, "Lane": { "id": 3, "origin_location_id": 1, "destination_location_id": 3, "duration": 1260, "distance": 21082, "driver_base_pay": "20", "driver_return_pay": "3", "driver_pay_per_kilometer": "1", "average_drive_speed": "18", "Location": { "id": 3, "tookan_id": null, "name": "", "address": "", "email": "", "phone": "" } } } ] }

and here is the error I get:

       {
        "name": "SequelizeEagerLoadingError"
        }

when I try this:

    (function(req,res) {
        models.Trip.findAll({
          include: [
            { model: models.Customer },
            { model: models.Move, include: [
              { model: models.Lane, include: [
                { model: models.Location, as: 'origin_location_id' },
                { model: models.Location, as: 'destination_location_id'}
              ] }
            ] }
         ],
        order:[['createdAt', 'DESC']]
      }).then(trips => {return res.json(trips)})
      .catch(err => res.status(422).json(err))
    })

every time I try to use "as" or "through" in either the query, the model associations, or both, I get no response from the server or the Sequelize Eager Loading Error — Eliza Janus, Oct 01 '18 at 18:11
posted on sequelize github issues: https://github.com/sequelize/sequelize/issues/9976 — Eliza Janus, Oct 01 '18 at 19:06

score 0 · Answer 1 · answered Oct 02 '18 at 14:02

I did the same thing that I've been doing for the past hour, which is trying to add "as" statements to associations and have them correspond to the query and all of the sudden it works for no apparent reason even though it did not work an hour ago. What I did was change the Lane--Location associations to models.Lane.belongsTo(models.Location, { as: 'pickup', foreignKey: 'origin_location_id'}); models.Lane.belongsTo(models.Location, { as: 'delivery', foreignKey: 'destination_location_id'}); and then updated the query to match { model: models.Location, as: 'pickup', attributes: ['id', 'tookan_id', 'name', 'address', 'email', 'phone'] }, { model: models.Location, as: 'delivery', attributes: ['id', 'tookan_id', 'name', 'address', 'email', 'phone'] }

score 0 · Answer 2 · answered Jun 17 '21 at 11:28

I have had similar problems when eager loading with model associations. My problem was that an inner join was performed, where I really needed an outer join.

Changed this: Foo.findAll(include: [ Bar ])

To This: Foo.findAll(include: [ { model: Bar, required: false }])

Sequelize nested eager loading does not load both records associated with the same model

2 Answers2