$or on a query with nested associations

[victormoraesgs]

What are you doing?

req.user
				.getPackages({
					through: {
						where: {
							personaId: req.user.id,
							companyId: req.params.companyid
						}
					},
					include: [
						{
							model: Permission,
							as: "permissions",
							attributes: ["id", "description"],
							where: { $or: [ { id : { $eq : 20 } } , { id : { $eq : 31 } } ] },
							include: [
								{
									model: Scope,
									as: "scope",
									attributes: ["id", "label"]
								},
								{
									model: Definition,
									as: "definition",
									attributes: ["id", "label"]
								}
							]
						}
					]

What do you expect to happen?

I wanted to both the permissions (ids 20 and 31) where added to the the response object. Like in this query:

SELECT *
FROM Managers AS M
INNER JOIN Packages AS P ON M.packageId = P.id
INNER JOIN PackagePermissions AS PP ON P.id = PP.packageId
INNER JOIN Permissions AS PE ON PP.permissionId = PE.idtbnfedhEmidhEmi
WHERE (PE.id = 22 OR PE.id = 31) 
AND M.personaId = 1 
AND M.companyId = 1;

What is actually happening?

Query:

SELECT `Manager`.`personaId`,
`Manager`.`companyId`,
`Manager`.`packageId`, `Manager`.`createdAt`, `Manager`.`updatedAt`, `packages`.`id` AS `packages.id`, `packages`.`label` AS `packages.label`, `packages`.`description`
AS `packages.description`, `packages->permissions`.`DefinitionId` AS `packages.permissions.DefinitionId`, `packages->permissions`.`scopeId` AS `packages.permissions.scopeId`, `packages->permissions`.`id` AS `packages.permissions.id`, `packages->permissions`.`description` AS `packages.permissions.description`, `packages->permissions->PackagePermission`.`packageId` AS `packages.permissions.PackagePermission.packageId`, `packages->permissions->PackagePermission`.`permissionId` AS `packages.permissions.PackagePermission.permissionId`, `packages->permissions->PackagePermission`.`createdAt` AS `packages.permissions.PackagePermission.createdAt`, `packages->permissions->PackagePermission`.`updatedAt` AS `packages.permissions.PackagePermission.updatedAt`, `packages->permissions->scope`.`id` AS `packages.permissions.scope.id`, `packages->permissions->scope`.`label` AS `packages.permissions.scope.label`, `packages->permissions->definition`.`id` AS `packages.permissions.definition.id`, `packages->permissions->definition`.`label` AS `packages.permissions.definition.label`
FROM `Managers` AS `Manager` 
LEFT OUTER JOIN (`Packages` AS `packages` 
 INNER JOIN (`PackagePermissions` AS `packages->permissions->PackagePermission`
 INNER JOIN `Permissions` AS `packages->permissions` ON `packages->permissions`.`scopeId` = `packages->permissions->PackagePermission`.`permissionId`) ON `packages`.`id` = `packages->permissions->PackagePermission`.`permissionId` AND (`packages->permissions`.`id` = 22 OR `packages->permissions`.`id` = 31) 
 LEFT OUTER JOIN `Scopes` AS `packages->permissions->scope` ON `packages->permissions`.`scopeId` = `packages->permissions->scope`.`id` 
 LEFT OUTER JOIN `Definitions` AS `packages->permissions->definition` ON `packages->permissions`.`definitionId` = `packages->permissions->definition`.`id` ) ON `Manager`.`packageId` = `packages`.`id` 
 WHERE `Manager`.`personaId` = 1 AND `Manager`.`companyId` = '1';

Sequelize Response:

[
    {
        "id": 5,
        "label": "owner",
        "description": "Dono da Empresa",
        "createdAt": null,
        "updatedAt": null,
        "permissions": [
            {
                "id": 22,
                "description": "Criar Novas Empresas",
                "scope": {
                    "id": 5,
                    "label": "company"
                },
                "definition": {
                    "id": 1,
                    "label": "create"
                },
                "PackagePermission": {
                    "packageId": 1,
                    "permissionId": 5,
                    "createdAt": null,
                    "updatedAt": null
                }
            }
        ],
        "Manager": {
            "personaId": 1,
            "companyId": 1,
            "packageId": 5,
            "createdAt": "2018-03-19T22:02:42.000Z",
            "updatedAt": "2018-03-19T22:02:42.000Z"
        }
    }
]

Dialect: mysql
Sequelize version: 4.36.1

[sushantdhiman]

Please use Github Issue Tracker only for reporting bugs, requesting new features or discussions. Ask questions on Stackoverflow sequelize.js tag or Slack. While I would like to help answer any questions but it still take too much time.

If you are reporting a bug please isolate it as SSCCE, present it nicely and follow issue template. This will help maintainers and contributors understand your issue quickly.

If you are requesting a feature spend some time to properly present your use case with some examples, current and expected outcome.

( What is SSCCE ? You can find template for Sequelize here )

A few good example of SSCCE

1. ResourceRequest timed out - connection not reestablished after db restarted #8014 (comment)
2. removeAttribute('id') results in undefined: null data value #7318 (comment)
3. toJSON converting booleans to "t" (true) or "f" (false) #8749 (comment)