{"action":"created","issue":{"url":"https://api.github.com/repos/sequelize/sequelize/issues/5481","repository_url":"https://api.github.com/repos/sequelize/sequelize","labels_url":"https://api.github.com/repos/sequelize/sequelize/issues/5481/labels{/name}","comments_url":"https://api.github.com/repos/sequelize/sequelize/issues/5481/comments","events_url":"https://api.github.com/repos/sequelize/sequelize/issues/5481/events","html_url":"https://github.com/sequelize/sequelize/issues/5481","id":136109820,"number":5481,"title":"Columns of join table are always selected, which breaks aggregation in PostgresSQL","user":{"login":"MrOrz","id":108608,"avatar_url":"https://avatars3.githubusercontent.com/u/108608?v=4","gravatar_id":"","url":"https://api.github.com/users/MrOrz","html_url":"https://github.com/MrOrz","followers_url":"https://api.github.com/users/MrOrz/followers","following_url":"https://api.github.com/users/MrOrz/following{/other_user}","gists_url":"https://api.github.com/users/MrOrz/gists{/gist_id}","starred_url":"https://api.github.com/users/MrOrz/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/MrOrz/subscriptions","organizations_url":"https://api.github.com/users/MrOrz/orgs","repos_url":"https://api.github.com/users/MrOrz/repos","events_url":"https://api.github.com/users/MrOrz/events{/privacy}","received_events_url":"https://api.github.com/users/MrOrz/received_events","type":"User","site_admin":false},"labels":[{"id":28979678,"url":"https://api.github.com/repos/sequelize/sequelize/labels/bug","name":"bug","color":"d7e102","default":true}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":17,"created_at":"2016-02-24T16:22:48Z","updated_at":"2017-10-10T22:15:59Z","closed_at":null,"author_association":"NONE","body":"Dialect: PostgresSql\nSequelize version: 3.19.3\n\nI have two tables that are n:m associated with a joining table. Whenever I use `.findAll` and include the other table, all fields in the joining table would also be selected in the generated SQL queries.  The fields in the joining table would cause problems when I try to use the aggregate functions.\n\n`include[].through.attributes: []` does not stops the columns from going into the SQL query.\n\nThe example below is modified from the one in #3938.\n\nSetup:\n\n``` js\n// MODELS\nvar Group = sequelize.define('Group', {\n  name: sequelize.Sequelize.STRING\n});\n\nvar Person = sequelize.define('Person', {\n  name: sequelize.Sequelize.STRING\n});\n\nvar GroupPerson = sequelize.define('GroupPerson', {\n  status: sequelize.Sequelize.STRING\n});\n\n// RELATIONS\nGroup.belongsToMany(Person, {through: GroupPerson, as: 'Persons'});\nPerson.belongsToMany(Group, {through: GroupPerson, as: 'Groups'});\n\nsequelize.sync({force: true}).then(function(){\n  // EXAMPLE\n  Person.findAll({\n    attributes: [[sequelize.fn('ARRAY_AGG', sequelize.col('Groups.id')), 'groupIds']],\n    include: [{\n      model: Group,\n      as: 'Groups',\n      through:{\n        attributes: [],\n        where: {status: 'done'}\n      },\n      attributes: []\n    }],\n    group: [sequelize.col('Person.id')]\n  });\n})\n```\n\n**Expected** SQL generated:\n\n``` sql\nSELECT ARRAY_AGG(\"Groups\".\"id\") AS \"groupIds\"\nFROM \"People\" AS \"Person\"\nLEFT OUTER JOIN (\n  \"GroupPeople\" AS \"Groups.GroupPerson\" \n  INNER JOIN \n    \"Groups\" AS \"Groups\" ON \"Groups\".\"id\" = \"Groups.GroupPerson\".\"GroupId\" AND \"Groups.GroupPerson\".\"status\" = 'done'\n) ON \"Person\".\"id\" = \"Groups.GroupPerson\".\"PersonId\"\nGROUP BY \"Person\".\"id\";\n```\n\n**Actual** SQL generated:\n\n``` sql\nSELECT \"Person\".\"id\", ARRAY_AGG(\"Groups\".\"id\") AS \"groupIds\",\n  \"Groups.GroupPerson\".\"status\" AS \"Groups.GroupPerson.status\", \"Groups.GroupPerson\".\"createdAt\" AS \"Groups.GroupPerson.createdAt\", \n  \"Groups.GroupPerson\".\"updatedAt\" AS \"Groups.GroupPerson.updatedAt\", \"Groups.GroupPerson\".\"PersonId\" AS \"Groups.GroupPerson.PersonId\",\n  \"Groups.GroupPerson\".\"GroupId\" AS \"Groups.GroupPerson.GroupId\"\nFROM \"People\" AS \"Person\"\nLEFT OUTER JOIN (\n  \"GroupPeople\" AS \"Groups.GroupPerson\"\n  INNER JOIN\n    \"Groups\" AS \"Groups\" ON \"Groups\".\"id\" = \"Groups.GroupPerson\".\"GroupId\" AND \"Groups.GroupPerson\".\"status\" = 'done'\n) ON \"Person\".\"id\" = \"Groups.GroupPerson\".\"PersonId\"\nGROUP BY \"Person\".\"id\";\n```\n\n...which gives the following error:\n\n```\nUnhandled rejection SequelizeDatabaseError: column \"Groups.GroupPerson.status\" must appear in the GROUP BY clause or be used in an aggregate function\n    at Query.formatError (/Users/mrorz/workspace/gis-inner/node_modules/sequelize/lib/dialects/postgres/query.js:443:14)\n    at null.<anonymous> (/Users/mrorz/workspace/gis-inner/node_modules/sequelize/lib/dialects/postgres/query.js:118:19)\n    at emitOne (events.js:77:13)\n    at emit (events.js:169:7)\n    at Query.handleError (/Users/mrorz/workspace/gis-inner/node_modules/pg/lib/query.js:108:8)\n    at null.<anonymous> (/Users/mrorz/workspace/gis-inner/node_modules/pg/lib/client.js:171:26)\n    at emitOne (events.js:77:13)\n    at emit (events.js:169:7)\n    at Socket.<anonymous> (/Users/mrorz/workspace/gis-inner/node_modules/pg/lib/connection.js:109:12)\n    at emitOne (events.js:77:13)\n    at Socket.emit (events.js:169:7)\n    at readableAddChunk (_stream_readable.js:146:16)\n    at Socket.Readable.push (_stream_readable.js:110:10)\n    at TCP.onread (net.js:523:20)\n```\n"},"comment":{"url":"https://api.github.com/repos/sequelize/sequelize/issues/comments/335624465","html_url":"https://github.com/sequelize/sequelize/issues/5481#issuecomment-335624465","issue_url":"https://api.github.com/repos/sequelize/sequelize/issues/5481","id":335624465,"user":{"login":"rich171","id":25567084,"avatar_url":"https://avatars3.githubusercontent.com/u/25567084?v=4","gravatar_id":"","url":"https://api.github.com/users/rich171","html_url":"https://github.com/rich171","followers_url":"https://api.github.com/users/rich171/followers","following_url":"https://api.github.com/users/rich171/following{/other_user}","gists_url":"https://api.github.com/users/rich171/gists{/gist_id}","starred_url":"https://api.github.com/users/rich171/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/rich171/subscriptions","organizations_url":"https://api.github.com/users/rich171/orgs","repos_url":"https://api.github.com/users/rich171/repos","events_url":"https://api.github.com/users/rich171/events{/privacy}","received_events_url":"https://api.github.com/users/rich171/received_events","type":"User","site_admin":false},"created_at":"2017-10-10T22:15:59Z","updated_at":"2017-10-10T22:15:59Z","author_association":"NONE","body":"It seems that includeIgnoreAttributes: false removes all attributes from nested includes that you may be using, so it's only a solution if you don't need those fields returned to you."}}
{"actor":{"display_login":"rich171"}}