search

RJMetrics / sweet-liberty

A library for building database-backed RESTful services using Clojure
Apache License 2.0
104 stars 6 forks source link

Support for SQL Like commands #6

Open smahood opened 9 years ago

smahood commented 9 years ago

Would it be possible to add in support for SQL Like commands? I use it all the time to enable easy user workflow for searching paged data tables.

karstendick commented 9 years ago

That's a good suggestion. That and inequalities are obvious next steps for filters.

What do you think the syntax should be for LIKE filtering?

smahood commented 9 years ago

The only thing I can come up with that makes sense is adding a keyword or symbol of some sort to adjust the current filter conditions. My gut is telling me that keywords might cause less problems.

The keyword for inequality could be something like GET /dogs?breed=_NOT_corgi

The keywords for like be something like GET /dogs?breed=cor_LIKE_, or GET /dogs?breed=_LIKE_gi. I know from my experience I've used a generic filter where the entire string is wrapped with asterisks, as in SELECT * .. WHERE Field LIKE '%123%', so it might be worth implementing just GET /dogs?breed=_LIKE_cor and just grab everything that contains the string.

I certainly can't think of ever running into data with strings in the form _NOT_ or _LIKE_ in any of the SQL I've used, but there might still need to be an escape character of some sort in case someone wanted to query data that has already used the same strings to log queries or something like that.

For inequality, symbols could potentially be something like GET /dogs?breed=~corgi or GET /dogs?breed=!corgi for breed not equal to "corgi".

For like, the closest thing to SQL syntax would be something along the lines of GET /dogs?breed=cor*and GET /dogs?breed=*gi, with the position of the asterisk determining what gets passed through to the query.

I'm not sure that symbols would work well in practice. I referenced http://stackoverflow.com/questions/1455578/characters-allowed-in-get-parameter to try and figure out which characters wouldn't overlap with much data and would also be allowed. It might be that the keywords would work better with less issues, especially with different hosting environments.

karstendick commented 9 years ago

I'd prefer putting the syntax in the keys of the params rather than the values. For example, something like GET /dogs?_like_breed=cor

This way, you needn't worry about escaping data or getting data in values with different semantics than you're expecting. Generally, params that start with an underscore are reserved for the library to use.

smahood commented 9 years ago

That sounds like a much better syntax, I like it. Should also work well for composing things as well such as ?_not_like_breed=cor