API: Order parameter

[ErisDS]

As mentioned in #5463, we want to add a new order top-level parameter to API browse requests.

The order parameter allows a user to specify which order they'd like their results to be returned in.

The default orders across the 3 key resources are currently:

• Posts: "posts"."status" ASC, "posts"."published_at" DESC, "posts"."updated_at" DESC, "posts"."id" DESC
• Users: "users"."last_login" DESC, "users"."name" ASC, "users"."created_at" DESC
• Tags: none (database default, usually by ID)

The order parameter should accept any valid SQL order by clause, which does not contain quote marks, e.g. users.last_login DESC, users.name ASC, users.created_at DESC. The comma-space separator between rules, and the space separator between property and direction are required.

This means that a valid value for the order parameter can be validated to contain only alphanumeric characters, dots, underscores and spaces, with the property being any string with those characters (except the space), and the direction is always either ASC or DESC (upper or lower) It should be possible to design a regex to match this pattern.

The order parameter should also validate that the provided order properties are valid attributes for the model. Any field with doesn't specify which model it belongs to should default to whichever model is being queried, and this should be set explicitly.

It should be possible to translate this format into a JSON format that matches the one returned from orderDefaultOptions using a split on the comma and space. The model must be included for each property (this will be added to models soon).

E.g. for posts, the default order would be translated to the following JSON objet:

{
    'post.status': 'ASC',
    'post.published_at': 'DESC',
    'post.updated_at': 'DESC',
     'post.id': 'DESC'
};

That object can then be used to apply the order, much as is already done, by reading options.order, and using the value of orderDefaultOptions if it doesn't exist: https://github.com/TryGhost/Ghost/blob/master/core/server/models/base/index.js#L293

[vadimdemedes]

The comma-space separator between rules, and the space separator
between property and direction are required.

I think it would be better, if we did not force spaces between rules. This will cause errors among developers, because they normally don't expect, that a lack of space in url can cause an error. I think we should accept both types of inputs, with and without spaces. And not being strict on lower/upper case should also be a plus.

Ideally, these should be treated equally: last_login desc,name asc, last_login DESC, name ASC.

[vadimdemedes]

Should transformed order object contain the model name prefix?

{
    title: 'DESC'
}

vs:

{
    'post.title': 'DESC'
}

defaultOrderOptions() in Post model returns attributes without this prefix:

[ErisDS]

Yes I think it needs to so that it will play nicely with joins?

[vadimdemedes]

Ok, then orderDefaultOptions() will need to be updated to insert a table prefix.

[ErisDS]

Note: There are one or two 'hard' problems here when it comes to ordering by counts. Counts aren't implemented yet and so the open PR can be merged without support for that, but I'm just thinking out loud about what the solution might look like.

There is one, cheeky line of ordering code in the stuff I added to make filters work: https://github.com/TryGhost/Ghost/blob/master/core/server/models/base/utils.js#L52

It's hardcoded atm, but what it is supposed to is replace the default sort order automatically when a query is made asking for all posts which match more than one tag. This is the special rule for 'IN' defined in here: #5604. It should still be possible to override this. The problem is, that to order by a count you have to call .query('orderByRaw', ...) instead of .query('orderBy', ...) and I'm not sure how to manage getting all of this to work together just yet?

The API for ordering by a count would still be e.g. order="posts.count, ASC" but internally, we might need to use raw again... I'm not sure as I haven't played much with count implementations yet.

This is probably a separate issue - just brain-dumping :)

[vadimdemedes]

@ErisDS Turns out bookshelf is prefixing attributes with table names automatically. This happened when I added table prefixes:

Update: Now I found out that we prefix attributes, not bookshelf: https://github.com/TryGhost/Ghost/blob/master/core/server/models/base/pagination.js#L171

[ErisDS]

I was just about to reply and say, I think this was done by the order-handling code in the pagination plugin and it probably needs updating to only add the table prefix if it is not already present.

[ErisDS]

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

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