{"action":"created","issue":{"url":"https://api.github.com/repos/sequelize/sequelize/issues/3623","labels_url":"https://api.github.com/repos/sequelize/sequelize/issues/3623/labels{/name}","comments_url":"https://api.github.com/repos/sequelize/sequelize/issues/3623/comments","events_url":"https://api.github.com/repos/sequelize/sequelize/issues/3623/events","html_url":"https://github.com/sequelize/sequelize/issues/3623","id":71792058,"number":3623,"title":"Adding limit: to findAll() DESTROYS the query","user":{"login":"DelvarWorld","id":79215,"avatar_url":"https://avatars.githubusercontent.com/u/79215?v=3","gravatar_id":"","url":"https://api.github.com/users/DelvarWorld","html_url":"https://github.com/DelvarWorld","followers_url":"https://api.github.com/users/DelvarWorld/followers","following_url":"https://api.github.com/users/DelvarWorld/following{/other_user}","gists_url":"https://api.github.com/users/DelvarWorld/gists{/gist_id}","starred_url":"https://api.github.com/users/DelvarWorld/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/DelvarWorld/subscriptions","organizations_url":"https://api.github.com/users/DelvarWorld/orgs","repos_url":"https://api.github.com/users/DelvarWorld/repos","events_url":"https://api.github.com/users/DelvarWorld/events{/privacy}","received_events_url":"https://api.github.com/users/DelvarWorld/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"milestone":null,"comments":7,"created_at":"2015-04-29T06:31:33Z","updated_at":"2015-06-05T15:37:32Z","closed_at":"2015-05-18T20:51:37Z","body":"I'm trying to return a group of `Model`s, paginated using `limit` and `offset`, including the grouped count of that model's favorites. A fairly trivial thing to attempt.\r\n\r\nHere's my basic query setup with sequelize:\r\n\r\n    var perPage = 12;\r\n    var page = 1;\r\n\r\n    return Model.findAll({\r\n\r\n        group: [ 'model.id', 'favorites.id' ],\r\n        attributes: [\r\n            '*',\r\n            [ Sequelize.fn('count', Sequelize.col('favorites.id')), 'favorites_count' ]\r\n        ],\r\n        include: [\r\n            { attributes: [], model: Favorite },\r\n        ],\r\n        offset: perPage * page\r\n\r\nThis generates the (fairly) expected query:\r\n\r\n    SELECT          \"model\".\"id\",\r\n                    \"model\".*,\r\n                    Count(\"favorites\".\"id\") AS \"favorites_count\",\r\n                    \"favorites\".\"id\"        AS \"favorites.id\"\r\n    FROM            \"model\"                 AS \"model\"\r\n    LEFT OUTER JOIN \"favorite\"              AS \"favorites\"\r\n    ON              \"model\".\"id\" = \"favorites\".\"model_id\"\r\n    GROUP BY        \"model\".\"id\",\r\n                    \"favorites\".\"id\" offset 12;\r\n\r\nIgnoring the fact that it quotes the tables, and that it selects `favorites.id` (forcing me to add it to the group by clause), and that it has randomly aliased things to their exact name or to a nonsensical name like `\"favorites.id\"` (all undesired), it seems to have worked. But now let's complete the pagination and add the limit to the query:\r\n\r\n    ...\r\n    offset: perPage * page\r\n    limit: perPage\r\n\r\nIt now generates this query:\r\n\r\n    SELECT \"model\".*,\r\n        \"favorites\".\"id\" AS \"favorites.id\"\r\n    FROM   (SELECT \"model\".\"id\",\r\n                \"model\".*,\r\n                Count(\"favorites\".\"id\") AS \"favorites_count\"\r\n            FROM   \"model\" AS \"model\"\r\n            GROUP  BY \"model\".\"id\",\r\n                    \"favorites\".\"id\"\r\n            LIMIT  12 offset 12) AS \"model\"\r\n        LEFT OUTER JOIN \"favorite\" AS \"favorites\"\r\n                        ON \"model\".\"id\" = \"favorites\".\"model\";\r\n\r\nIn completely baffling behavior, it has generated an inner query and applied the limit only to that, then aliased that as `\"model\"`.\r\n\r\nAs a sanity check I looked in the docs for findAll, but the docs [do not seem to think that command exists](https://readthedocs.org/elasticsearch/?q=findall&type=file&project=sequelize&version=latest).\r\n\r\nI suspect I am doing something wrong, but I can't figure out what it is. This behavior is quite bizzarre, and I'm hoping my sleep deprivation is the cause of my confusion. \r\n\r\nI'm using version `2.0.6`"},"comment":{"url":"https://api.github.com/repos/sequelize/sequelize/issues/comments/109332318","html_url":"https://github.com/sequelize/sequelize/issues/3623#issuecomment-109332318","issue_url":"https://api.github.com/repos/sequelize/sequelize/issues/3623","id":109332318,"user":{"login":"krisr","id":18091,"avatar_url":"https://avatars.githubusercontent.com/u/18091?v=3","gravatar_id":"","url":"https://api.github.com/users/krisr","html_url":"https://github.com/krisr","followers_url":"https://api.github.com/users/krisr/followers","following_url":"https://api.github.com/users/krisr/following{/other_user}","gists_url":"https://api.github.com/users/krisr/gists{/gist_id}","starred_url":"https://api.github.com/users/krisr/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/krisr/subscriptions","organizations_url":"https://api.github.com/users/krisr/orgs","repos_url":"https://api.github.com/users/krisr/repos","events_url":"https://api.github.com/users/krisr/events{/privacy}","received_events_url":"https://api.github.com/users/krisr/received_events","type":"User","site_admin":false},"created_at":"2015-06-05T15:37:32Z","updated_at":"2015-06-05T15:37:32Z","body":"In my case, the subquery was also generated with incorrect limit semantics.\r\n\r\nI had a model setup like\r\n\r\n    Parent\r\n        belongsToMany(Child, through ParentChild)\r\n     Child\r\n        belongsToMany(Parent through ParentChild)\r\n        belongsToMany(subChild, through ChildSubChild)\r\n     SubChild\r\n        belongsToMany(Child, through ChildSubChild)\r\n\r\nAnd a query like:\r\n\r\n    Parent.getChildren({\r\n        include: {\r\n            model: SubChild,\r\n            where: {id: {$in: [...]}}\r\n        },\r\n        limit: 10\r\n    })\r\n\r\nThe resulting query came out like:\r\n\r\n    SELECT \r\n        Child.*, SubChild.*\r\n    FROM \r\n        (SELECT\r\n            Child.*, Parent.* FROM Child.* INNER JOIN ParentChild ...  WHERE  .... LIMIT 30)\r\n    INNER JOIN\r\n         ChildSubChild\r\n         INNER JOIN\r\n             SubChild ... SubChild.id in [....]\r\n    LIMIT 30\r\n\r\nSo it was limiting the result set before applying the final filter on SubChild - (in my case, SubChild is a form of categorization  / tagging)."}}