Querying Many-Many does not include duplicate records with the same foreign key

[kkotwal94]

What are you doing?

I'm Querying a specific 'Student', including a many - to many relationship for companies yet dupes are not coming back for records with the same studentId, and companyId. The join table does also have a primary key with id on it.

Look at the line where I include Companies which is a belongsToMany Relationship

Unique, and constraint dont do much they were attempts, paranoid worked though. I was able to fetch deletedAt records then.

export function getStudent(req, res, next) {
  Students.find({
    where: {
      id: req.params.id
    },
    order: [
      [{model: Addresses}, 'id', 'DESC'],
      [{model: Companies}, 'id', 'DESC'],
    ],
    include: [{model: Schools},
              {model: ClassificationTypes},
              {model: Companies,
                as: 'Companies',
                paranoid: false,
                through: { paranoid: false, unique: false, constraints: false }
              },
              {model: Drivers,
                as: 'Drivers'},
              {model: Addresses,
                as: 'Addresses',
                through: { paranoid: false },
                include: [{model: AddressTypes},
                          {model: States,
                          paranoid: false }]
              }
            ]
  }).then((students) => {
    return res.json(students);
  }).catch((err) => {
    res.sendStatus(500);
    next(err);
  });
}

Here are my models:

export default (sequelize, DataTypes) => {
  const CompaniesStudents = sequelize.define('CompaniesStudents', {
    companyId: DataTypes.INTEGER,
    studentId: DataTypes.INTEGER,
    extraFees: DataTypes.FLOAT,
    createdAt: {
        type: DataTypes.DATE
    },
    updatedAt: {
        type: DataTypes.DATE
    },
    deletedAt: {
        type: DataTypes.DATE
    }
  }, {
    timestamps: true,
    paranoid: true,
  }
);
  return CompaniesStudents;
};

export default (sequelize, DataTypes) => {
  const Companies = sequelize.define('Companies', {
    name: DataTypes.STRING,
    description: DataTypes.STRING,
    phone: DataTypes.STRING,
    email: DataTypes.STRING,
    createdAt: {
        allowNull: false,
        type: DataTypes.DATE
    },
    updatedAt: {
        allowNull: false,
        type: DataTypes.DATE
    },
    deletedAt: {
        type: DataTypes.DATE
    }
  }, {
    timestamps: true,
    paranoid: true,
  }
);

  Companies.associate = (models) => {
    Companies.belongsToMany(models.Drivers, {through: 'CompaniesDrivers', as: 'Drivers', foreignKey: 'companyId', otherKey: 'driverId'});
    Companies.belongsToMany(models.Students, {through: 'CompaniesStudents', as: 'Students', foreignKey: 'companyId', otherKey: 'studentId'});
    Companies.belongsToMany(models.Zones, {through: 'CompaniesZones', as: 'Zones', foreignKey: 'companyId', otherKey: 'zoneId'});
    Companies.belongsToMany(models.User, {through: 'CompaniesUsers', as: 'Users', foreignKey: 'companyId', otherKey: 'userId'});
    Companies.belongsToMany(models.Addresses, {through: 'CompaniesAddresses', as: 'Addresses', foreignKey: 'companyId', otherKey: 'addressId'});
  };

  return Companies;
};

export default (sequelize, DataTypes) => {
  const Students = sequelize.define('Students', {
    studentIdentification: DataTypes.INTEGER,
    firstName: DataTypes.STRING,
    lastName: DataTypes.STRING,
    schoolId: {
      type: DataTypes.INTEGER,
        references: {
          model: 'Schools',
          key: 'id'
        }
    },
    classificationTypeId: {
      type: DataTypes.INTEGER,
          references: {
            model: 'ClassificationTypes',
            key: 'id',
          }
      },
    zoneId: {
      type: DataTypes.INTEGER,
        references: {
          model: 'Zones',
          key: 'id'
        }
    },
    desiredPickUpTime: DataTypes.DATE,
    desiredDropOffTime: DataTypes.DATE,
    createdAt: {
        allowNull: false,
        type: DataTypes.DATE
    },
    updatedAt: {
        allowNull: false,
        type: DataTypes.DATE
    },
    deletedAt: {
        type: DataTypes.DATE
    }
  }, {
    timestamps: true,
    paranoid: true,
  }
);
  Students.associate = (models) => {
    Students.belongsTo(models.ClassificationTypes, { foreignKey: 'classificationTypeId'});
    Students.belongsTo(models.Schools, {foreignKey: 'schoolId'});
    Students.belongsTo(models.Zones, {foreignKey: 'zoneId'});
    Students.belongsToMany(models.Companies, {through: 'CompaniesStudents', as: 'Companies', foreignKey: 'studentId', otherKey: 'companyId'});
    Students.belongsToMany(models.Drivers, {through: 'DriversStudents', as: 'Drivers', foreignKey: 'studentId', otherKey: 'driverId'});
    Students.belongsToMany(models.Addresses, {through: 'StudentsAddresses', as: 'Addresses', foreignKey: 'studentId', otherKey: 'addressId'});
  };

  return Students;
};

What do you expect to happen?

I expect all records to return, whether they are soft deleted or not.

What is actually happening?

Only the Last record pertaining it is returned

Here is a example of two different companies a student has belonged to
Here are the records:

"Companies": [
        {
            "id": 19,
            "name": "Suspect Icecream truck",
            "description": "",
            "phone": "21312314",
            "email": "koawewa",
            "createdAt": "2018-03-20T19:41:03.578Z",
            "updatedAt": "2018-03-20T19:41:16.500Z",
            "deletedAt": null,
            "CompaniesStudents": {
                "companyId": 19,
                "studentId": 6,
                "extraFees": null,
                "createdAt": "2018-03-20T20:05:03.239Z",
                "updatedAt": "2018-03-20T20:05:03.239Z",
                "deletedAt": null
            }
        },
        {
            "id": 18,
            "name": "Karans Pickup Company",
            "description": "",
            "phone": "917321312",
            "email": "a21@yasd.com",
            "createdAt": "2018-03-20T19:40:39.012Z",
            "updatedAt": "2018-03-20T19:40:54.042Z",
            "deletedAt": null,
            "CompaniesStudents": {
                "companyId": 18,
                "studentId": 6,
                "extraFees": null,
                "createdAt": "2018-03-20T19:42:50.399Z",
                "updatedAt": "2018-03-20T19:42:50.399Z",
                "deletedAt": "2018-03-20T19:43:02.784Z"
            }
        }
    ],

Dialect: mysql / postgres / sqlite / mssql / any
Dialect version: latest
Database version: 10
Sequelize version: latest
Tested with latest release: yes: 4.37.3

Note : Your issue may be ignored OR closed by maintainers if it's not tested against latest version OR does not follow issue template.

If this is a real bug, and I really do appreciate the project, when do you think something like this might be touched upon? Trying to convince teamates to use this ORM.

[sushantdhiman]

Thank you for following issue template, this is an already known bug #5077, #3220, #6906 and #3493