{"action":"created","issue":{"url":"https://api.github.com/repos/sequelize/sequelize/issues/6901","repository_url":"https://api.github.com/repos/sequelize/sequelize","labels_url":"https://api.github.com/repos/sequelize/sequelize/issues/6901/labels{/name}","comments_url":"https://api.github.com/repos/sequelize/sequelize/issues/6901/comments","events_url":"https://api.github.com/repos/sequelize/sequelize/issues/6901/events","html_url":"https://github.com/sequelize/sequelize/issues/6901","id":190585889,"number":6901,"title":"Where on association + limit throws db error","user":{"login":"istrau2","id":8128572,"avatar_url":"https://avatars.githubusercontent.com/u/8128572?v=3","gravatar_id":"","url":"https://api.github.com/users/istrau2","html_url":"https://github.com/istrau2","followers_url":"https://api.github.com/users/istrau2/followers","following_url":"https://api.github.com/users/istrau2/following{/other_user}","gists_url":"https://api.github.com/users/istrau2/gists{/gist_id}","starred_url":"https://api.github.com/users/istrau2/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/istrau2/subscriptions","organizations_url":"https://api.github.com/users/istrau2/orgs","repos_url":"https://api.github.com/users/istrau2/repos","events_url":"https://api.github.com/users/istrau2/events{/privacy}","received_events_url":"https://api.github.com/users/istrau2/received_events","type":"User","site_admin":false},"labels":[{"id":96184457,"url":"https://api.github.com/repos/sequelize/sequelize/labels/waiting","name":"waiting","color":"fad8c7","default":false}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":4,"created_at":"2016-11-20T21:44:20Z","updated_at":"2016-11-22T15:21:28Z","closed_at":null,"body":"I am experiencing the following issue. I am trying to execute the following query:\r\n\r\n```\r\ntransaction.findAll({\r\n            where: {\r\n                '$tenant.privileges.user_pkey$': {\r\n                    $eq: userPkey\r\n                }\r\n            },\r\n            include: [\r\n                {\r\n                    model: this.account,\r\n                    as: 'tenant',\r\n                    attributes: [],\r\n                    include: [\r\n                        {\r\n                            model: this.privilege,\r\n                            as: 'privileges',\r\n                            attributes: []\r\n                        }\r\n                    ]\r\n                }\r\n            ],\r\n            order: 'transactionPkey',\r\n            limit: 10\r\n        });\r\n```\r\n\r\nI was expecting that I would get the top 10 transactions which fit the where clause.\r\n\r\n\r\nHowever, instead I get a db error:\r\n`missing FROM-clause entry for table \"tenant.privileges\"`\r\n\r\nThe actual SQL produced is this:\r\n\r\n> SELECT \"Transaction\".* FROM (SELECT \"Transaction\".\"transaction_pkey\" AS \"transactionPkey\", \"Transaction\".\"global_transaction_pkey\" AS \"globalTransactionPkey\", \"Transaction\".\"tenant_pkey\" AS \"tenantPkey\", \"Transaction\".\"tenant_name\" AS \"tenantName\", \"Transaction\".\"counterparty_pkey\" AS \"counterpartyPkey\", \"Transaction\".\"counterparty_name\" AS \"counterpartyName\", \"Transaction\".\"transaction_direction_pkey\" AS \"transactionDirectionPkey\", \"Transaction\".\"transaction_direction_name\" AS \"transactionDirectionName\", \"Transaction\".\"transaction_status_pkey\" AS \"transactionStatusPkey\", \"Transaction\".\"transaction_status_name\" AS \"transactionStatusName\", \"Transaction\".\"transaction_type_pkey\" AS \"transactionTypePkey\", \"Transaction\".\"transaction_type_name\" AS \"transactionTypeName\", \"Transaction\".\"transaction_timestamp\" AS \"transactionTimestamp\", \"Transaction\".\"gross_quantity\" AS \"grossQuantity\", \"Transaction\".\"tare_quantity\" AS \"tareQuantity\", \"Transaction\".\"seal_identifier\" AS \"sealIdentifier\", \"Transaction\".\"vehicle_identifier\" AS \"vehicleIdentifier\", \"Transaction\".\"note\", \"Transaction\".\"image_path\" AS \"imagePath\" FROM \"v_transaction\" AS \"Transaction\" WHERE (\"tenant.privileges\".\"user_pkey\" = 158) ORDER BY \"Transaction\".\"transaction_pkey\" asc LIMIT '10' OFFSET '0') AS \"Transaction\" LEFT OUTER JOIN \"v_account\" AS \"tenant\" ON \"Transaction\".\"tenantPkey\" = \"tenant\".\"account_pkey\" LEFT OUTER JOIN \"v_privilege\" AS \"tenant.privileges\" ON \"tenant\".\"account_pkey\" = \"tenant.privileges\".\"account_pkey\" ORDER BY \"Transaction\".\"transactionPkey\" asc;\r\n\r\n\r\nI believe this error is occuring because the where clause is in the inner subquery while the joins are done in the outer query.\r\n\r\nIs there any way to have it all get executed as one query as opposed to putting the joins in the outer query and all other stuff (where, limit etc.) in the inner one?\r\n"},"comment":{"url":"https://api.github.com/repos/sequelize/sequelize/issues/comments/262269190","html_url":"https://github.com/sequelize/sequelize/issues/6901#issuecomment-262269190","issue_url":"https://api.github.com/repos/sequelize/sequelize/issues/6901","id":262269190,"user":{"login":"istrau3","id":17746340,"avatar_url":"https://avatars.githubusercontent.com/u/17746340?v=3","gravatar_id":"","url":"https://api.github.com/users/istrau3","html_url":"https://github.com/istrau3","followers_url":"https://api.github.com/users/istrau3/followers","following_url":"https://api.github.com/users/istrau3/following{/other_user}","gists_url":"https://api.github.com/users/istrau3/gists{/gist_id}","starred_url":"https://api.github.com/users/istrau3/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/istrau3/subscriptions","organizations_url":"https://api.github.com/users/istrau3/orgs","repos_url":"https://api.github.com/users/istrau3/repos","events_url":"https://api.github.com/users/istrau3/events{/privacy}","received_events_url":"https://api.github.com/users/istrau3/received_events","type":"User","site_admin":false},"created_at":"2016-11-22T15:21:28Z","updated_at":"2016-11-22T15:21:28Z","body":"@sushantdhiman comments?"}}
{"actor":{"display_login":"istrau3"}}