{"action":"created","issue":{"url":"https://api.github.com/repos/TryGhost/Ghost/issues/6008","labels_url":"https://api.github.com/repos/TryGhost/Ghost/issues/6008/labels{/name}","comments_url":"https://api.github.com/repos/TryGhost/Ghost/issues/6008/comments","events_url":"https://api.github.com/repos/TryGhost/Ghost/issues/6008/events","html_url":"https://github.com/TryGhost/Ghost/issues/6008","id":113347970,"number":6008,"title":"Allow ordering by columns in outside tables via JOIN","user":{"login":"vdemedes","id":697676,"avatar_url":"https://avatars.githubusercontent.com/u/697676?v=3","gravatar_id":"","url":"https://api.github.com/users/vdemedes","html_url":"https://github.com/vdemedes","followers_url":"https://api.github.com/users/vdemedes/followers","following_url":"https://api.github.com/users/vdemedes/following{/other_user}","gists_url":"https://api.github.com/users/vdemedes/gists{/gist_id}","starred_url":"https://api.github.com/users/vdemedes/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/vdemedes/subscriptions","organizations_url":"https://api.github.com/users/vdemedes/orgs","repos_url":"https://api.github.com/users/vdemedes/repos","events_url":"https://api.github.com/users/vdemedes/events{/privacy}","received_events_url":"https://api.github.com/users/vdemedes/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":3,"created_at":"2015-10-26T12:49:03Z","updated_at":"2015-10-28T11:09:37Z","closed_at":null,"body":"This issue is related to #5602 and #5989.\r\n\r\n### Issue\r\n\r\nSo, while implementing support for ordering by rows from outside tables (when using `join`), I discovered an issue with the way Bookshelf fetches records. It does not use `join`, but uses extra `select` query to fetch additional rows from other tables and then merges them into the final result.\r\n\r\nFor example, I want to fetch posts and include author information in them:\r\n\r\n```js\r\nPostAPI.browse({include: 'author'});\r\n```\r\n\r\nI'd expect a `select` query with a `join` statement. But here's what happens instead:\r\n\r\n<img width=\"677\" alt=\"screen shot 2015-10-26 at 12 56 04 pm\" src=\"https://cloud.githubusercontent.com/assets/697676/10728305/f6e3c350-7be0-11e5-940f-59747cf08476.png\">\r\n\r\nIt finds posts, then looks for `author_id` and finds that there are 3 unique `author_id`s. After that, it executes one more `select` with those ids. When that extra query is finished, author results get merged into posts result (into `author` column).\r\n\r\n### Consequences\r\n\r\nBecause of this issue, it is not possible to order by \"joined\" values, e.g. `author.name`. Because the initial query with `order by` does not know what `author` column is:\r\n\r\n```\r\nselect \"posts.*\" from \"posts\" order by \"title\" DESC\r\n```\r\n\r\n### Solution\r\n\r\nThe proposed solution is to detect, if `order` references any columns in outside tables. If it does, modify the query to `join` that outside table. In that case, queries with `order` will work as expected.\r\n\r\n**Note**: Similar work is done for `filter` in [core/server/models/base/utils.js](https://github.com/TryGhost/Ghost/blob/master/core/server/models/base/utils.js#L29). If `join` is required, it modifies a query to include these statements."},"comment":{"url":"https://api.github.com/repos/TryGhost/Ghost/issues/comments/151804749","html_url":"https://github.com/TryGhost/Ghost/issues/6008#issuecomment-151804749","issue_url":"https://api.github.com/repos/TryGhost/Ghost/issues/6008","id":151804749,"user":{"login":"vdemedes","id":697676,"avatar_url":"https://avatars.githubusercontent.com/u/697676?v=3","gravatar_id":"","url":"https://api.github.com/users/vdemedes","html_url":"https://github.com/vdemedes","followers_url":"https://api.github.com/users/vdemedes/followers","following_url":"https://api.github.com/users/vdemedes/following{/other_user}","gists_url":"https://api.github.com/users/vdemedes/gists{/gist_id}","starred_url":"https://api.github.com/users/vdemedes/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/vdemedes/subscriptions","organizations_url":"https://api.github.com/users/vdemedes/orgs","repos_url":"https://api.github.com/users/vdemedes/repos","events_url":"https://api.github.com/users/vdemedes/events{/privacy}","received_events_url":"https://api.github.com/users/vdemedes/received_events","type":"User","site_admin":false},"created_at":"2015-10-28T11:09:37Z","updated_at":"2015-10-28T11:09:37Z","body":"@sebgie Thanks for the link, I'll try that."}}