Query parameter name collision

[Mattias Gyllsdorff]

We recently had a situation where our query filter keys had the same name as the our API order by keyword. Does anyone have any good idea how this can be resolved?

Lets say you have a ReST api where you have the url "/items" where a GET request returns the following data;

[  

   {  

      "id":"2bd8cba7-767b-429f-9fa2-59d98f6fd07f",

      "createdAt":"2015-02-21T05:07:23+00:00",

      "modifiedAt":"2015-02-21T05:07:23+00:00",

      "orderBy":"foo",

      "createdByUserId": "47dab17c-10a0-4de6-9a3c-a1164c427342"

   },

   {  

      "id":"f4ddc597-7829-40d2-adb3-24d22ae7e80e",

      "createdAt":"2015-02-21T05:07:23+00:00",

      "modifiedAt":"2015-02-21T05:07:23+00:00",

      "orderBy":"orderBy",

      "createdByUserId": "b13a1020-fb78-4e62-b0a5-5240eb6af146"

   },

   {  

      "id":"65e329a2-8828-415c-870d-e454770ad34e",

      "createdAt":"2015-02-21T05:07:23+00:00",

      "modifiedAt":"2015-02-21T05:07:23+00:00",

      "orderBy":"orderBy desc",

      "createdByUserId": "0df3a37e-9926-4402-a039-29e561f17c9e"

   }

]

Normally, if we want to return items that are newer then 2015-01-01 and we want to order the result by when it was created we use a url like "/items?createdAt=>2015-01-21T05:07:23+00:00&orderBy=createdAt" but in this case we have a filter key that is the same as the api keyword "orderBy". 

Recently we ended up in a situation where we wanted items where the "orderBy" field was "orderBy" and we wanted the result ordered using the "createdAt" field.  "/items?orderBy=orderBy&orderBy=createdAt", which one is the order by parameter and which one is the filter parameter?

Our API have the keywords "orderBy", "fields", "include", "limit" and "page" so a complete GET request can look like 

"/items?orderBy=orderBy&orderBy=createdAt&field=id&field=orderBy&field=createdAt&include=createdByUser"

Right now it looks like we have a few options:

 - Rename the fields in our data model. We can't do this since we have to return data using this exact structure.

 

 - Change the API keywords if there is a name collision. This would make a our API inconsistent.

 - Change the keywords to "apiKeywordX" like "apiKeywordOrderBy" which would decrease the chance of name collisions. Unfortunately we have a endpoint where the user can create custom models where each field can be searchable (thank you Postgresql for the JSON datatype) so there is still a risk of collisions. 

 - Move any api keywords to the request headers. In this case the url would look like 

"/items?orderBy=orderBy" and have headers like 

"OrderBy: createdAt", 

"Include: createdByUser", 

"Fields: id", 

"Fields: orderBy",

"Fields: createdAt". 

We ran a short trial using this but we ran into problems with a few corporate proxies that sometimes stripped unusual headers.

 - Use a Json string as the query parameter. In this case the url would look like 

"/items?parameter={"filters": {"orderBy": "orderBy"}, "orderBy": [{"createdAt": "desc"}], "fields": ["id", "orderBy", "createdAt"], "include": ["createdByUser"}". 

After URL encoding it looks like

"/items?parameter=%7B%22filters%22%3A%20%7B%22orderBy%22%3A%20%22orderBy%22%7D%2C%20%22orderBy%22%3A%20%5B%7B%22createdAt%22%3A%20%22desc%22%7D%5D%2C%20%22fields%22%3A%20%5B%22id%22%2C%20%22orderBy%22%2C%20%22createdAt%22%5D%2C%20%22include%22%3A%20%5B%22createdByUser%22%7D". 

This way the request is still cachable but it forces the consumer to have a json serializer. Our API returns data in XML, json, yaml or bson so we can't guarantee that the consumer have a json library. 

Right now the Json query parameter looks like the best solution but I don't really like it since the url is completely unreadable. On the other hand no normal users are supposed to see those urls. 

We can't be the first one that have a legacy database with a REST api that have this problem. Is there any common way to handle things like this?

[Jørn Wildt]

> "/items?orderBy=orderBy&orderBy=createdAt"

Arghhh, took me a while to grok this one :-) What is the representing? A set of saved searches or something like that?

How about prefixing operators with $ so it becomes "/items?$orderBy=orderBy&orderBy=createdAt". Not so much different from prefixing with "apiKeyword" as you suggest yourself, but perhaps more readable.

Or not allowing API properties as URL query parameters. Use something like "filter=..." instead, yielding "/items?orderBy=orderBy&filter=orderBy+eq+createdAt".

Have fun optimizing the database for all possible combinations of orderBy and filter ... that is somewhat an anti-pattern in terms of performance but albeit very often requested by "business". Are you *sure* you need all combinations? It would be a lot better to figure out a set of standard queries and then optimize for those.

/Jørn

--
You received this message because you are subscribed to the Google Groups "API Craft" group.
To unsubscribe from this group and stop receiving emails from it, send an email to api-craft+...@googlegroups.com.
Visit this group at http://groups.google.com/group/api-craft.
For more options, visit https://groups.google.com/d/optout.

[Mattias Gyllsdorff]

> "/items?orderBy=orderBy&orderBy=createdAt"
Arghhh, took me a while to grok this one :-) What is the representing? A set of saved searches or something like that?

Something like that yes :). The name of the table is something else, but we use it to do calculations on data passing through one of our systems. The request gets feed into a *old* system 24 hours after it is POST:ed to the front REST server. 

How about prefixing operators with $ so it becomes "/items?$orderBy=orderBy&orderBy=createdAt". Not so much different from prefixing with "apiKeyword" as you suggest yourself, but perhaps more readable.
Or not allowing API properties as URL query parameters. Use something like "filter=..." instead, yielding "/items?orderBy=orderBy&filter=orderBy+eq+createdAt".

"/items?filter=orderBy+eq+orderBy&orderBy=createdAt&field=id&field=orderBy&field=createdAt&include=createdByUser"

I think your second suggestion is the best so far. Field name followed by operation and anything after that is the filter value. That way we only need to allow certain keywords as the query parameter keys. It will cause problem if the client misbehaves and forgets to send what the operation (=, !=, <, etc.) is but that is acceptable. 

We have asked some of our clients and so far most of them prefers using a json/xml query parameter instead of the more common "fieldName=value" which is quite surprising to me.

Have fun optimizing the database for all possible combinations of orderBy and filter ... that is somewhat an anti-pattern in terms of performance but albeit very often requested by "business". Are you *sure* you need all combinations? It would be a lot better to figure out a set of standard queries and then optimize for those.

Unfortunately, yes. There is 9 different common queries and a few filter fields that the clients are allowed to use freely. They can also use the full range of filters and parameters for free a few times each hour, after that we charge them depending on the query complexity, run-time and load. 

Performance is only guaranteed if they use one of the common queries and the free queries gets re-routed to two specific read only databases so they won't affect our regular queries.

[Andrew Braae]

To add support to Jørn's first suggestion, OData uses $ to prefix reserved words, e.g. /items?$orderby=name

http://www.odata.org/documentation/odata-version-2-0/uri-conventions/

Presumably they had quite the fun time discussing and investigating the best way to avoid name collisions, so it might be worth investigating their approach.

[Chris Mullins]

You linked to "OData V2" which is pretty old. I had an Atom-pub flashback just seeing the version! :)

OData has come a long way since then, and is now up to V4. Here's the relevant link:

http://docs.oasis-open.org/odata/odata/v4.0/odata-v4.0-part2-url-conventions.html

The OData guys are all really great and their spec is pretty amazing for a huge number of scenarios. IT's also VERY enterprise suitable, given the name spacing, Entity Data Model, and so forth. 

Cheers,

Chris