RESTful API sorting dilema

Question

Hi folks 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 of those which seems killer performance bottleneck.

Thanks for any pointers

Answer 1

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.

Answer 2

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.

Answer 3

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?