{"action":"created","issue":{"url":"https://api.github.com/repos/sequelize/sequelize/issues/9208","repository_url":"https://api.github.com/repos/sequelize/sequelize","labels_url":"https://api.github.com/repos/sequelize/sequelize/issues/9208/labels{/name}","comments_url":"https://api.github.com/repos/sequelize/sequelize/issues/9208/comments","events_url":"https://api.github.com/repos/sequelize/sequelize/issues/9208/events","html_url":"https://github.com/sequelize/sequelize/issues/9208","id":307029242,"number":9208,"title":"Querying Many-Many does not include duplicate records with the same foreign key","user":{"login":"kkotwal94","id":8924340,"avatar_url":"https://avatars3.githubusercontent.com/u/8924340?v=4","gravatar_id":"","url":"https://api.github.com/users/kkotwal94","html_url":"https://github.com/kkotwal94","followers_url":"https://api.github.com/users/kkotwal94/followers","following_url":"https://api.github.com/users/kkotwal94/following{/other_user}","gists_url":"https://api.github.com/users/kkotwal94/gists{/gist_id}","starred_url":"https://api.github.com/users/kkotwal94/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/kkotwal94/subscriptions","organizations_url":"https://api.github.com/users/kkotwal94/orgs","repos_url":"https://api.github.com/users/kkotwal94/repos","events_url":"https://api.github.com/users/kkotwal94/events{/privacy}","received_events_url":"https://api.github.com/users/kkotwal94/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2018-03-20T20:37:51Z","updated_at":"2018-03-22T04:56:43Z","closed_at":"2018-03-22T04:56:43Z","author_association":"NONE","body":"<!--\r\n\r\nPlease note this is an issue tracker, not a support forum.\r\nFor general questions, please use StackOverflow or Slack.\r\n\r\nFor bugs, please fill out the template below.\r\n\r\n-->\r\n\r\n## What are you doing?\r\nI'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.\r\n\r\nLook at the line where I include Companies which is a belongsToMany Relationship\r\n\r\nUnique, and constraint dont do much they were attempts, paranoid worked though. I was able to fetch deletedAt records then.\r\n\r\n```js\r\nexport function getStudent(req, res, next) {\r\n  Students.find({\r\n    where: {\r\n      id: req.params.id\r\n    },\r\n    order: [\r\n      [{model: Addresses}, 'id', 'DESC'],\r\n      [{model: Companies}, 'id', 'DESC'],\r\n    ],\r\n    include: [{model: Schools},\r\n              {model: ClassificationTypes},\r\n              {model: Companies,\r\n                as: 'Companies',\r\n                paranoid: false,\r\n                through: { paranoid: false, unique: false, constraints: false }\r\n              },\r\n              {model: Drivers,\r\n                as: 'Drivers'},\r\n              {model: Addresses,\r\n                as: 'Addresses',\r\n                through: { paranoid: false },\r\n                include: [{model: AddressTypes},\r\n                          {model: States,\r\n                          paranoid: false }]\r\n              }\r\n            ]\r\n  }).then((students) => {\r\n    return res.json(students);\r\n  }).catch((err) => {\r\n    res.sendStatus(500);\r\n    next(err);\r\n  });\r\n}\r\n```\r\n\r\nHere are my models: \r\n\r\n```js\r\nexport default (sequelize, DataTypes) => {\r\n  const CompaniesStudents = sequelize.define('CompaniesStudents', {\r\n    companyId: DataTypes.INTEGER,\r\n    studentId: DataTypes.INTEGER,\r\n    extraFees: DataTypes.FLOAT,\r\n    createdAt: {\r\n        type: DataTypes.DATE\r\n    },\r\n    updatedAt: {\r\n        type: DataTypes.DATE\r\n    },\r\n    deletedAt: {\r\n        type: DataTypes.DATE\r\n    }\r\n  }, {\r\n    timestamps: true,\r\n    paranoid: true,\r\n  }\r\n);\r\n  return CompaniesStudents;\r\n};\r\n```\r\n```js\r\nexport default (sequelize, DataTypes) => {\r\n  const Companies = sequelize.define('Companies', {\r\n    name: DataTypes.STRING,\r\n    description: DataTypes.STRING,\r\n    phone: DataTypes.STRING,\r\n    email: DataTypes.STRING,\r\n    createdAt: {\r\n        allowNull: false,\r\n        type: DataTypes.DATE\r\n    },\r\n    updatedAt: {\r\n        allowNull: false,\r\n        type: DataTypes.DATE\r\n    },\r\n    deletedAt: {\r\n        type: DataTypes.DATE\r\n    }\r\n  }, {\r\n    timestamps: true,\r\n    paranoid: true,\r\n  }\r\n);\r\n\r\n  Companies.associate = (models) => {\r\n    Companies.belongsToMany(models.Drivers, {through: 'CompaniesDrivers', as: 'Drivers', foreignKey: 'companyId', otherKey: 'driverId'});\r\n    Companies.belongsToMany(models.Students, {through: 'CompaniesStudents', as: 'Students', foreignKey: 'companyId', otherKey: 'studentId'});\r\n    Companies.belongsToMany(models.Zones, {through: 'CompaniesZones', as: 'Zones', foreignKey: 'companyId', otherKey: 'zoneId'});\r\n    Companies.belongsToMany(models.User, {through: 'CompaniesUsers', as: 'Users', foreignKey: 'companyId', otherKey: 'userId'});\r\n    Companies.belongsToMany(models.Addresses, {through: 'CompaniesAddresses', as: 'Addresses', foreignKey: 'companyId', otherKey: 'addressId'});\r\n  };\r\n\r\n  return Companies;\r\n};\r\n```\r\n\r\n```js\r\nexport default (sequelize, DataTypes) => {\r\n  const Students = sequelize.define('Students', {\r\n    studentIdentification: DataTypes.INTEGER,\r\n    firstName: DataTypes.STRING,\r\n    lastName: DataTypes.STRING,\r\n    schoolId: {\r\n      type: DataTypes.INTEGER,\r\n        references: {\r\n          model: 'Schools',\r\n          key: 'id'\r\n        }\r\n    },\r\n    classificationTypeId: {\r\n      type: DataTypes.INTEGER,\r\n          references: {\r\n            model: 'ClassificationTypes',\r\n            key: 'id',\r\n          }\r\n      },\r\n    zoneId: {\r\n      type: DataTypes.INTEGER,\r\n        references: {\r\n          model: 'Zones',\r\n          key: 'id'\r\n        }\r\n    },\r\n    desiredPickUpTime: DataTypes.DATE,\r\n    desiredDropOffTime: DataTypes.DATE,\r\n    createdAt: {\r\n        allowNull: false,\r\n        type: DataTypes.DATE\r\n    },\r\n    updatedAt: {\r\n        allowNull: false,\r\n        type: DataTypes.DATE\r\n    },\r\n    deletedAt: {\r\n        type: DataTypes.DATE\r\n    }\r\n  }, {\r\n    timestamps: true,\r\n    paranoid: true,\r\n  }\r\n);\r\n  Students.associate = (models) => {\r\n    Students.belongsTo(models.ClassificationTypes, { foreignKey: 'classificationTypeId'});\r\n    Students.belongsTo(models.Schools, {foreignKey: 'schoolId'});\r\n    Students.belongsTo(models.Zones, {foreignKey: 'zoneId'});\r\n    Students.belongsToMany(models.Companies, {through: 'CompaniesStudents', as: 'Companies', foreignKey: 'studentId', otherKey: 'companyId'});\r\n    Students.belongsToMany(models.Drivers, {through: 'DriversStudents', as: 'Drivers', foreignKey: 'studentId', otherKey: 'driverId'});\r\n    Students.belongsToMany(models.Addresses, {through: 'StudentsAddresses', as: 'Addresses', foreignKey: 'studentId', otherKey: 'addressId'});\r\n  };\r\n\r\n  return Students;\r\n};\r\n```\r\n\r\n\r\n\r\n## What do you expect to happen?\r\nI expect all records to return, whether they are soft deleted or not.\r\n\r\n## What is actually happening?\r\nOnly the Last record pertaining it is returned\r\n\r\nHere is a example of two different companies a student has belonged to\r\nHere are the records:\r\n![image](https://user-images.githubusercontent.com/8924340/37681171-47f23654-2c5c-11e8-88be-4a2cb17cf18f.png)\r\n\r\n```js\r\n\"Companies\": [\r\n        {\r\n            \"id\": 19,\r\n            \"name\": \"Suspect Icecream truck\",\r\n            \"description\": \"\",\r\n            \"phone\": \"21312314\",\r\n            \"email\": \"koawewa\",\r\n            \"createdAt\": \"2018-03-20T19:41:03.578Z\",\r\n            \"updatedAt\": \"2018-03-20T19:41:16.500Z\",\r\n            \"deletedAt\": null,\r\n            \"CompaniesStudents\": {\r\n                \"companyId\": 19,\r\n                \"studentId\": 6,\r\n                \"extraFees\": null,\r\n                \"createdAt\": \"2018-03-20T20:05:03.239Z\",\r\n                \"updatedAt\": \"2018-03-20T20:05:03.239Z\",\r\n                \"deletedAt\": null\r\n            }\r\n        },\r\n        {\r\n            \"id\": 18,\r\n            \"name\": \"Karans Pickup Company\",\r\n            \"description\": \"\",\r\n            \"phone\": \"917321312\",\r\n            \"email\": \"a21@yasd.com\",\r\n            \"createdAt\": \"2018-03-20T19:40:39.012Z\",\r\n            \"updatedAt\": \"2018-03-20T19:40:54.042Z\",\r\n            \"deletedAt\": null,\r\n            \"CompaniesStudents\": {\r\n                \"companyId\": 18,\r\n                \"studentId\": 6,\r\n                \"extraFees\": null,\r\n                \"createdAt\": \"2018-03-20T19:42:50.399Z\",\r\n                \"updatedAt\": \"2018-03-20T19:42:50.399Z\",\r\n                \"deletedAt\": \"2018-03-20T19:43:02.784Z\"\r\n            }\r\n        }\r\n    ],\r\n```\r\n\r\n__Dialect:__ mysql / postgres / sqlite / mssql / any\r\n__Dialect version:__ latest\r\n__Database version:__ 10\r\n__Sequelize version:__ latest\r\n__Tested with latest release:__ yes: 4.37.3\r\n\r\n**Note :** _Your issue may be ignored OR closed by maintainers if it's not tested against latest version OR does not follow issue template._\r\n\r\nIf 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."},"comment":{"url":"https://api.github.com/repos/sequelize/sequelize/issues/comments/375179889","html_url":"https://github.com/sequelize/sequelize/issues/9208#issuecomment-375179889","issue_url":"https://api.github.com/repos/sequelize/sequelize/issues/9208","id":375179889,"user":{"login":"sushantdhiman","id":9989487,"avatar_url":"https://avatars1.githubusercontent.com/u/9989487?v=4","gravatar_id":"","url":"https://api.github.com/users/sushantdhiman","html_url":"https://github.com/sushantdhiman","followers_url":"https://api.github.com/users/sushantdhiman/followers","following_url":"https://api.github.com/users/sushantdhiman/following{/other_user}","gists_url":"https://api.github.com/users/sushantdhiman/gists{/gist_id}","starred_url":"https://api.github.com/users/sushantdhiman/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/sushantdhiman/subscriptions","organizations_url":"https://api.github.com/users/sushantdhiman/orgs","repos_url":"https://api.github.com/users/sushantdhiman/repos","events_url":"https://api.github.com/users/sushantdhiman/events{/privacy}","received_events_url":"https://api.github.com/users/sushantdhiman/received_events","type":"User","site_admin":false},"created_at":"2018-03-22T04:56:42Z","updated_at":"2018-03-22T04:56:42Z","author_association":"OWNER","body":"Thank you for following issue template, this is an already known bug  #5077, #3220, #6906 and #3493"}}
{"actor":{"display_login":"sushantdhiman"}}