Allow ordering by columns in outside tables via JOIN

[vadimdemedes]

This issue is related to #5602 and #5989.

Issue

So, 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.

For example, I want to fetch posts and include author information in them:

PostAPI.browse({include: 'author'});

I'd expect a select query with a join statement. But here's what happens instead:

It finds posts, then looks for author_id and finds that there are 3 unique author_ids. 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).

Consequences

Because 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:

select "posts.*" from "posts" order by "title" DESC

Solution

The 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.

Note: Similar work is done for filter in core/server/models/base/utils.js. If join is required, it modifies a query to include these statements.

[sebgie]

I have not tried it out yet, but this SO question shows a possible approach to sorting by related model values. http://stackoverflow.com/questions/22568153/order-bookshelf-js-fetch-by-related-column-value

[ErisDS]

Link to the discussion that lead to us breaking this issue out from #5602: https://ghost.slack.com/archives/dev/p1445860844002641

[vadimdemedes]

@sebgie Thanks for the link, I'll try that.

[ErisDS]

I'm closing most API issues temporarily with the later label.

JSON API Overhaul & OAuth access are currently scheduled next on the roadmap