{"action":"created","issue":{"url":"https://api.github.com/repos/sequelize/sequelize/issues/3268","labels_url":"https://api.github.com/repos/sequelize/sequelize/issues/3268/labels{/name}","comments_url":"https://api.github.com/repos/sequelize/sequelize/issues/3268/comments","events_url":"https://api.github.com/repos/sequelize/sequelize/issues/3268/events","html_url":"https://github.com/sequelize/sequelize/issues/3268","id":59733950,"number":3268,"title":"No array overlap in where of eager load in 1.7.9 (PG)","user":{"login":"Roam-Cooper","id":6518745,"avatar_url":"https://avatars.githubusercontent.com/u/6518745?v=3","gravatar_id":"","url":"https://api.github.com/users/Roam-Cooper","html_url":"https://github.com/Roam-Cooper","followers_url":"https://api.github.com/users/Roam-Cooper/followers","following_url":"https://api.github.com/users/Roam-Cooper/following{/other_user}","gists_url":"https://api.github.com/users/Roam-Cooper/gists{/gist_id}","starred_url":"https://api.github.com/users/Roam-Cooper/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Roam-Cooper/subscriptions","organizations_url":"https://api.github.com/users/Roam-Cooper/orgs","repos_url":"https://api.github.com/users/Roam-Cooper/repos","events_url":"https://api.github.com/users/Roam-Cooper/events{/privacy}","received_events_url":"https://api.github.com/users/Roam-Cooper/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":1,"created_at":"2015-03-04T00:58:13Z","updated_at":"2015-03-04T01:15:49Z","closed_at":null,"body":"We are still on 1.7.9 and running PG (believe me, I am pushing at work for the time to move to your most recent version...)\r\n\r\nAnd I noticed that if I have a column whose type is array of text, in the models where I can check against an array and Sequelize will produce the SQL: \"&& ARRAY['value']::TEXT[]\" (this is what I want, array overlap)\r\n\r\nBut in the where statements in includes (and in nested includes) when I check against an array Sequelize produces the SQL: \"IN ('value')\" which results in PG erroring with \"array value must start with \"{\" or dimension information\"\r\n\r\nI know this is an old version, so I don't expect any sort of solution, but I thought I'd create the issue just to see if anybody else experienced this problem and to learn if/how they managed to fix it. I've checked that the column in question in every table is array of text and I can't think of anything else, Sequelize just seems to be changing the array statement it generates depending on whether it is for an include or not.\r\n\r\nAre array overlaps supported in includes and why do the current includes generate invalid SQL?\r\n\r\nEDIT: the issue also occurs in 1.7.10, which seems to be the best version before I'd have to switch to >=2.0.0 incompatible"},"comment":{"url":"https://api.github.com/repos/sequelize/sequelize/issues/comments/77078361","html_url":"https://github.com/sequelize/sequelize/issues/3268#issuecomment-77078361","issue_url":"https://api.github.com/repos/sequelize/sequelize/issues/3268","id":77078361,"user":{"login":"Roam-Cooper","id":6518745,"avatar_url":"https://avatars.githubusercontent.com/u/6518745?v=3","gravatar_id":"","url":"https://api.github.com/users/Roam-Cooper","html_url":"https://github.com/Roam-Cooper","followers_url":"https://api.github.com/users/Roam-Cooper/followers","following_url":"https://api.github.com/users/Roam-Cooper/following{/other_user}","gists_url":"https://api.github.com/users/Roam-Cooper/gists{/gist_id}","starred_url":"https://api.github.com/users/Roam-Cooper/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/Roam-Cooper/subscriptions","organizations_url":"https://api.github.com/users/Roam-Cooper/orgs","repos_url":"https://api.github.com/users/Roam-Cooper/repos","events_url":"https://api.github.com/users/Roam-Cooper/events{/privacy}","received_events_url":"https://api.github.com/users/Roam-Cooper/received_events","type":"User","site_admin":false},"created_at":"2015-03-04T01:15:49Z","updated_at":"2015-03-04T01:15:49Z","body":"For posterity, each of our models include a column like so:\r\n```javascript\r\nfoo: {\r\n        type: DataTypes.ARRAY(DataTypes.TEXT)\r\n}\r\n```\r\n\r\nAnd the operation in question is something like:\r\n\r\n```javascript\r\nsomeModel.findAll({\r\n            where: {\r\n                someColumn: ['value']\r\n            },\r\n            include: {\r\n                model: associatedModel,\r\n                required: true,\r\n                where: {\r\n                    someColumn: ['value']\r\n                }\r\n            }\r\n        }).success().failure();\r\n```\r\n\r\nWhat happens (as I mentioned initially) is that Sequelize generates the query correctly for the where statement and uses the array overlap operator (\"&&\") along with ARRAY[] syntax, but for where statements in any includes (including nested includes) Sequelize generates SQL that uses the \"IN\" operator and uses \"('value')\" syntax for the array data to compare to.\r\n\r\nHopefully this is enough information to get some sort of lead going. :)"}}