{"action":"opened","issue":{"url":"https://api.github.com/repos/sequelize/sequelize/issues/4772","labels_url":"https://api.github.com/repos/sequelize/sequelize/issues/4772/labels{/name}","comments_url":"https://api.github.com/repos/sequelize/sequelize/issues/4772/comments","events_url":"https://api.github.com/repos/sequelize/sequelize/issues/4772/events","html_url":"https://github.com/sequelize/sequelize/issues/4772","id":113960349,"number":4772,"title":"Eager loading max (newest, oldest, etc) association only","user":{"login":"khanlou","id":850492,"avatar_url":"https://avatars.githubusercontent.com/u/850492?v=3","gravatar_id":"","url":"https://api.github.com/users/khanlou","html_url":"https://github.com/khanlou","followers_url":"https://api.github.com/users/khanlou/followers","following_url":"https://api.github.com/users/khanlou/following{/other_user}","gists_url":"https://api.github.com/users/khanlou/gists{/gist_id}","starred_url":"https://api.github.com/users/khanlou/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/khanlou/subscriptions","organizations_url":"https://api.github.com/users/khanlou/orgs","repos_url":"https://api.github.com/users/khanlou/repos","events_url":"https://api.github.com/users/khanlou/events{/privacy}","received_events_url":"https://api.github.com/users/khanlou/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":0,"created_at":"2015-10-29T01:34:09Z","updated_at":"2015-10-29T01:34:09Z","closed_at":null,"body":"I've done a fair bit of googling for this and can't seem to find anything about how to do it in Sequelize (or really in Rails's ORM either). Basically, I have a `hasMany` parent/child association, and I'd like to be able to load the first child only.\r\n\r\nMy service has a concept of threads and messages; when fetching the 20 most recent threads, I also want to be able to fetch the most recent message in each (so I can display a little preview). As far as I can tell, these are the only ways to do this:\r\n\r\n1. Duplicate the data from the most recent message in the thread model (lame)\r\n2. Fetch the 20 most recent threads, then fetch the most recent message of each with a separate query (n+1 query problem)\r\n3. Fetch the 20 most recent threads, then fetch all of the most recent messages with a single `WHERE..IN`-type query (complicated)\r\n4. Use Sequelize's normal eager loading to load every message in all 20 threads and then limit them once they're in memory (doesn't scale well)\r\n4. Totally custom SQL (ugly/not portable)\r\n\r\nIn regular SQL (MSSQL variant) I might do this with a subquery:\r\n\r\n  SELECT * FROM threads\r\n  INNER JOIN messages ON threads.id = messages.thread_id\r\n  AND messages.created_at = (\r\n    SELECT MAX(created_at) FROM messages WHERE thread_id = threads.id\r\n  )\r\n  ORDER BY posted_at DESC\r\n  OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY\r\n\r\nAs far as I've seen, this isn't a common feature in ORMs, even though it seems like it would be super useful. Are there any better ways to do it than the ones I've listed here?"}}