{"action":"created","issue":{"url":"https://api.github.com/repos/sequelize/sequelize/issues/4446","labels_url":"https://api.github.com/repos/sequelize/sequelize/issues/4446/labels{/name}","comments_url":"https://api.github.com/repos/sequelize/sequelize/issues/4446/comments","events_url":"https://api.github.com/repos/sequelize/sequelize/issues/4446/events","html_url":"https://github.com/sequelize/sequelize/issues/4446","id":105183365,"number":4446,"title":"Limit causing \"missing FROM-clause entry for table\"","user":{"login":"osteenbergen","id":1105741,"avatar_url":"https://avatars.githubusercontent.com/u/1105741?v=3","gravatar_id":"","url":"https://api.github.com/users/osteenbergen","html_url":"https://github.com/osteenbergen","followers_url":"https://api.github.com/users/osteenbergen/followers","following_url":"https://api.github.com/users/osteenbergen/following{/other_user}","gists_url":"https://api.github.com/users/osteenbergen/gists{/gist_id}","starred_url":"https://api.github.com/users/osteenbergen/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/osteenbergen/subscriptions","organizations_url":"https://api.github.com/users/osteenbergen/orgs","repos_url":"https://api.github.com/users/osteenbergen/repos","events_url":"https://api.github.com/users/osteenbergen/events{/privacy}","received_events_url":"https://api.github.com/users/osteenbergen/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":3,"created_at":"2015-09-07T09:41:47Z","updated_at":"2015-09-07T11:23:38Z","closed_at":null,"body":"Hi,\r\n\r\nGot an issue with sequelize, tried 3.5 and 3.7.1, using postgres. I added as much detail as I could below, but the short version is that adding _limit_ to a query with an include results in a different query than expected.\r\n\r\nI simplified the models for readability, but its just a simple job system. Lets say the job is to run ```echo 'Hello World'``` than a ```Run``` would be status/result of the execution. You can run a job multiple times resulting in a new ```Run``` model. During execution the system may add ```RunMessages``` to the ```Run```, which can be the output of the command.\r\n\r\nWhat I'm trying to query is the answer to the following question:\r\n_For a given job what are the last 10 runs and the number of messages for each run_\r\n\r\nThe result should be something like\r\n```js\r\n[\r\n {\"id\":\"...\",\"status\": \"done\",\"jobid\":\"...\", ... , \"msg_count\":3},\r\n {\"id\":\"...\",\"status\": \"done\",\"jobid\":\"...\", ... , \"msg_count\":9},\r\n {\"id\":\"...\",\"status\": \"done\",\"jobid\":\"...\", ... , \"msg_count\":0},\r\n ...\r\n]\r\n```\r\n\r\n## Working query without limit\r\n### Query options:\r\n```js\r\n{\r\n  \"where\" : {\r\n    \"jobid\":req.params.jobid\r\n  },\r\n  \"attributes\":\r\n    Object.keys(this.db.model(\"Run\").attributes).concat([\r\n      [sequelize.fn('COUNT',sequelize.col('messages.id')),\"msg_count\"]\r\n    ]),\r\n  \"include\":[\r\n      {\r\n        \"model\": this.db.model(\"RunMessage\"),\r\n        \"as\" : \"messages\",\r\n        \"attributes\":[]\r\n      }\r\n  ],\r\n  \"group\":['\"Run.id\"'],\r\n  \"order\":[[\"createdAt\",\"DESC\"]],\r\n  \"logging\": console.log,\r\n}\r\n```\r\n### Query\r\n```sql\r\nSELECT \r\n  \"Run\".\"id\", \r\n  \"Run\".\"status\", \r\n  \"Run\".\"jobid\",\r\n  \"Run\".\"createdAt\", \r\n  \"Run\".\"updatedAt\", \r\n  COUNT(\"messages\".\"id\") AS \"msg_count\"\r\nFROM \"Runs\" AS \"Run\" \r\nLEFT OUTER JOIN \"RunMessages\" AS \"messages\" ON \"Run\".\"id\" = \"messages\".\"runid\" \r\nWHERE \"Run\".\"jobid\" = '607169ba-e6ec-4efa-8aca-b70ffc1d2ffb' \r\nGROUP BY \"Run\".\"id\" \r\nORDER BY \"Run\".\"createdAt\" DESC;\r\n```\r\n\r\n## Adding Limit\r\n```js\r\n{\r\n  \"where\" : {\r\n    \"jobid\":req.params.jobid\r\n  },\r\n  \"attributes\":\r\n    Object.keys(this.db.model(\"Run\").attributes).concat([\r\n      [sequelize.fn('COUNT',sequelize.col('messages.id')),\"msg_count\"]\r\n    ]),\r\n  \"include\":[\r\n      {\r\n        \"model\": this.db.model(\"RunMessage\"),\r\n        \"as\" : \"messages\",\r\n        \"attributes\":[]\r\n      }\r\n  ],\r\n  \"group\":['\"Run.id\"'],\r\n  \"order\":[[\"createdAt\",\"DESC\"]],\r\n  \"logging\": console.log,\r\n  \"limit\": 10\r\n}\r\n```\r\n### Query\r\n```sql\r\nSELECT \r\n  \"Run\".* \r\nFROM (\r\n  SELECT \r\n    \"Run\".\"id\", \r\n    \"Run\".\"status\", \r\n    \"Run\".\"jobid\", \r\n    \"Run\".\"createdAt\", \r\n    \"Run\".\"updatedAt\", \r\n    COUNT(\"messages\".\"id\") AS \"msg_count\" \r\n  FROM \"Runs\" AS \"Run\" \r\n  WHERE \"Run\".\"jobid\" = '607169ba-e6ec-4efa-8aca-b70ffc1d2ffb' \r\n  GROUP BY \"Run\".\"id\"\r\n  ORDER BY \"Run\".\"createdAt\" DESC\r\n  LIMIT 10\r\n) AS \"Run\" \r\nLEFT OUTER JOIN \"RunMessages\" AS \"messages\" ON \"Run\".\"id\" = \"messages\".\"runid\"\r\nORDER BY \"Run\".\"createdAt\" DESC;\r\n```\r\n### Error\r\n```\r\nSequelizeDatabaseError: missing FROM-clause entry for table \"messages\"\r\njob_1 |     at Query.formatError (/api-jobs/node_modules/sequelize/lib/dialects/postgres/query.js:433:14)\r\njob_1 |     at null.<anonymous> (/api-jobs/node_modules/sequelize/lib/dialects/postgres/query.js:108:19)\r\njob_1 |     at emit (events.js:107:17)\r\njob_1 |     at Query.handleError (/regressinator-api-jobs/node_modules/pg/lib/query.js:99:8)\r\njob_1 |     at null.<anonymous> (/regressinator-api-jobs/node_modules/pg/lib/client.js:166:26)\r\njob_1 |     at emit (events.js:107:17)\r\njob_1 |     at Socket.<anonymous> (/regressinator-api-jobs/node_modules/pg/lib/connection.js:109:12)\r\njob_1 |     at Socket.emit (events.js:107:17)\r\njob_1 |     at readableAddChunk (_stream_readable.js:163:16)\r\njob_1 |     at Socket.Readable.push (_stream_readable.js:126:10)\r\njob_1 |     at TCP.onread (net.js:538:20)\r\n```\r\n## Expected Query\r\n```sql\r\nSELECT \r\n  \"Run\".\"id\", \r\n  \"Run\".\"status\", \r\n  \"Run\".\"jobid\",\r\n  \"Run\".\"createdAt\", \r\n  \"Run\".\"updatedAt\", \r\n  COUNT(\"messages\".\"id\") AS \"msg_count\"\r\nFROM \"Runs\" AS \"Run\" \r\nLEFT OUTER JOIN \"RunMessages\" AS \"messages\" ON \"Run\".\"id\" = \"messages\".\"runid\" \r\nWHERE \"Run\".\"jobid\" = '607169ba-e6ec-4efa-8aca-b70ffc1d2ffb' \r\nGROUP BY \"Run\".\"id\" \r\nORDER BY \"Run\".\"createdAt\" DESC\r\nLIMIT 10;\r\n```\r\n## Models\r\n### Run\r\n```js\r\ndb.define(\r\n    // Model name\r\n    \"Run\",\r\n    // Properties\r\n    /** @lends Run.prototype */\r\n    {\r\n      /**\r\n      * Identifier of the Job/Run\r\n      * @readonly\r\n      * @type {UUID}\r\n      */\r\n      id : {\r\n        type: DataTypes.UUID,\r\n        //Use V4 UUIDs\r\n        defaultValue: DataTypes.UUIDV4,\r\n        allowNull: false,\r\n        primaryKey: true\r\n      },\r\n      /**\r\n      * Actual status of the job\r\n      * @type {\"created\"|\"running\"|\"done\"|\"failed\"}\r\n      */\r\n      status : {\r\n        type: DataTypes.ENUM(\"created\",\"running\",\"done\",\"failed\"),\r\n        defaultValue: \"created\",\r\n        allowNull: false\r\n      },\r\n      /**\r\n      * Job that we should run\r\n      * @type {UUID}\r\n      */\r\n      jobid : {\r\n        type: DataTypes.UUID,\r\n        allowNull:false\r\n      },\r\n    }\r\n  );\r\n```\r\n### RunMessage\r\n```js\r\ndb.define(\r\n    // Model name\r\n    \"RunMessage\",\r\n    // Properties\r\n    /** @lends RunMessage.prototype */\r\n    {\r\n      /**\r\n      * Identifier of the Job/RunMessage\r\n      * @readonly\r\n      * @type {UUID}\r\n      */\r\n      id : {\r\n        type: DataTypes.UUID,\r\n        //Use V4 UUIDs\r\n        defaultValue: DataTypes.UUIDV4,\r\n        allowNull: false,\r\n        primaryKey: true\r\n      },\r\n      /**\r\n      * Run associated with this RunMessage\r\n      * @type {UUID}\r\n      */\r\n      runid : {\r\n        type: DataTypes.UUID,\r\n        allowNull:false\r\n      },\r\n      /**\r\n      * A textual description of the notification\r\n      *\r\n      * @type {Text}\r\n      */\r\n      message : {\r\n        type: DataTypes.TEXT,\r\n        allowNull:false\r\n      },\r\n    },\r\n  );\r\n```\r\n### Association\r\n```js\r\ndb.model(\"Run\").hasMany(\r\n  db.model(\"RunMessage\"),\r\n  {\r\n    \"as\" : \"messages\",\r\n    \"foreignKey\" : \"runid\",\r\n    \"onDelete\" : \"cascade\",\r\n    \"hooks\" : true\r\n  }\r\n);\r\n```"},"comment":{"url":"https://api.github.com/repos/sequelize/sequelize/issues/comments/138274223","html_url":"https://github.com/sequelize/sequelize/issues/4446#issuecomment-138274223","issue_url":"https://api.github.com/repos/sequelize/sequelize/issues/4446","id":138274223,"user":{"login":"mickhansen","id":699049,"avatar_url":"https://avatars.githubusercontent.com/u/699049?v=3","gravatar_id":"","url":"https://api.github.com/users/mickhansen","html_url":"https://github.com/mickhansen","followers_url":"https://api.github.com/users/mickhansen/followers","following_url":"https://api.github.com/users/mickhansen/following{/other_user}","gists_url":"https://api.github.com/users/mickhansen/gists{/gist_id}","starred_url":"https://api.github.com/users/mickhansen/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mickhansen/subscriptions","organizations_url":"https://api.github.com/users/mickhansen/orgs","repos_url":"https://api.github.com/users/mickhansen/repos","events_url":"https://api.github.com/users/mickhansen/events{/privacy}","received_events_url":"https://api.github.com/users/mickhansen/received_events","type":"User","site_admin":false},"created_at":"2015-09-07T11:23:38Z","updated_at":"2015-09-07T11:23:38Z","body":"The subquery is needed because the joining of messages produces a cartesian product making an outer limit effectively useless.\r\n\r\nYou won't be able to do this query with a limit and a count on a join, you'll need to count from a subquery if you wish to limit the overall result."}}