RESTful API sorting dilemma

I have REST API implemented following the principle that rest just gives back the basic documents and refs in those documents howto get other things etc etc

For example a /car/5 will give me model:blabla , user_id: 1 and then if you need the owner you'll get /user/1 to get user data..

This way JOINS and stuff are avoided in DB..all things are linking between themselves and data interconnected on rest client part - keeping things simple easy to cache/drop cache, scale etc.

But what happens when you need sorting?

Imagine we have some view on frontend to display following data: Car model, User name, etc... and you wanna sort by user name for example.

You can't really tell the /car/5 to sort by username cause it only knows of user ids...

One option I see is sorting from user /user/list?sortby=username and then interconnecting which of those returned ids actually refer to car. but this means we need to get ALL users..and only use fraction开发者_StackOverflow社区 of those which seems killer performance bottleneck.

Thanks for any pointers

I think you're trying to avoid joining for all the wrong reasons, as by taking this approach, you're going to have to service a lot more requests.

If you instead bring back all the information that you'd show (i.e. do the joins database side), then the clients are going to have to make less queries, and you could do your sort without having to (lazy) load all the child objects.

The other option would be to bring back the children objects as child XML elements (in a similar manner to how OpenStreetMap's RESTful interface works). You still have a single hit from the client, and you should be able to optimise the queries to minimise load on the DB.

If you are avoiding all joins in the server and leaving it up to the client then in this case you must leave sorting to the client too. This is because a join has to take place in order to sort the cars by Username.

You can create another resource called CarsAndOwners or similar which returns the joined list, at which point it becomes reasonable to sort on the server.

First of all, your example /car/5 should be the plural version of the resource in order to be RESTful:

/cars/5
/users/1

Now, in general if you want a RESTful approach to sorting you can do this:

/cars?sort=make&order=asc

In your example, you mention that you want to sort cars by username. However, like you mention, the cars collection should know nothing about users. So if you want to display a list of cars and group them by user, what do you do? You need a way to retrieve this information. One way of doing this is to loop through the users and request the cars associated with each user:

# pseudo code

users.each do |user|
  # assuming the user id is 5,
  # to display the user's cars, 
  # we need to request /users/5/cars
end

So we can request the list of cars for each individual at a URL like /users/5/cars. This is known as a nested resource. We are only requesting the cars that are related to user 5.

However, then you have the issue of potentially making lots of requests to your API (which may be okay depending on your use case).

Another approach, which is probably better, is to do this in one request to:

/users/cars?sort=username&order=asc

This would return an array of users and their cars ordered by username.

And yet another approach could be:

/users?include=[cars]&sort=username&order=asc

but I dont like this approach as much as the previous. I would recommend the previous approach of /users/cars.

Side note: your lists should be paginated so that there is not the overhead of requesting all of the users when you are only displaying a partial list.

/users?page=1&per_page=50

I'd start by imagining the ideal RESTful query that I'd like to write, for instance:

/car/list?sortby=username

And then figure out how to implement that.

Now your list method gives you car objects that don't expose username as you said, but I don't think that matters. All the sorting should take place on the server before the list is prepared.

You are going to need to join your car objects to user objects at some point in order to do this, and there's no way of dodging that issue. The only choice you have is where you do it.

If you are working with a traditional RDBMS it makes sense to do that at the DB level.

Are you using an ORM layer between your application API and the database?

Databases are designed to do joins quickly, so leaving it up to the client or your server application does not make sense to me.

Almost everything I've read on REST says that relationships between entities/resources should be a resource also. Therefore you should have a resource /mydomain.com/CarsAndUsers that represents that join/relationship.

Then you can do sorting on that resource just like any other, and it will be handled by the database:

/mydomain.com/CarsAndUsers?sortby=username

You can also apply filtering to that resource (and paging) just like any other resource.

EDIT:

As per the comments it would be better to do something like this:

/cars?embed=user,sortby=username

Thanks Eric for pointing this out.