More elaborate way to make model queries from the URL?

[Fredrik Boström]

Hello Railers,

I'm building an API backend using Rails 4 and I'm struggling with finding a more elaborate way to do model queries straight from the URL than a mere ?name=Bob&age=43. What I'm after is some way to make AND, OR, LIKE etc operations straight from the URL query parameter.

Loopback on node.js has something similar:

With Loopback you can define your query as a javascript object {filter: {where: {and: [{name: {like: 'Bob%'}}, {age: 43}]}}, which, translated to query parameters, would be: ?filter[where][and][0][name][like]=Bob%25&filter[where][and][1][age]=43.

I've been looking at using Arel, Squeel, sequel-rails and of course normal ActiveRecord but none of them seem to provide this kind of URL-to-ORM query mapping out of the box. Does anyone have any other pointers or ideas of how to make this happen?

Thanks!

[Dave Aronson]

Other pointers: don't do that. Don't reinvent SQL and something to
implement it as well. There are reasons not everybody has written a
complete database. Plus, it will be a maintenance *and security*
nightmare.

Look instead at the REST standards. Yes, there's some ambiguity...
but pick what works, for you, *simply*.

--
Dave Aronson, consulting software developer of Codosaur.us,
PullRequestRoulette.com, Blog.Codosaur.us, and Dare2XL.com.

[Fredrik Boström]

Thanks Dave.

Maybe I expressed myself a bit unclearly. I'm not trying to reinvent SQL or create a new database implementation, that's insane! The API I'm building is RESTful of course. I don't have a problem of querying models per se, that's done with RESTful routing as it should be done. What I'm after is a way to do more complex filtering of the result set using query parameters than what ActiveRecord now supports.

As of now a request like this

GET /api/users?name=Doris

works fine since I can filter the query parameters through params.permit and throw them into User.where(params) or something to that effect.

However, the ActiveRecord Query Interface doesn't have that elaborate support for hash conditions, as stated in the docs

Only equality, range and subset checking are possible with Hash conditions.

which translates to only using AND, IN and =. Hence, I'm looking for a way to enhance the built in support for simple conditions to support something similar to what I described above, particularly a way to combine AND and OR filtering.

Sure, I could parse the query parameters and use the different parts to build a query using ActiveRecord's other condition methods or by using Arel or some of the other tools I mentioned, but that's just it: nothing I've come across so far supports this kind of "advanced" filtering by passing the filter condition straight from the query parameters to the .where() method. It doesn't seem so far fetched since ActiveRecord already offers support for simple filtering using hashes and all ORM tools I've looked at supports it on a programmatic level.

So what I'm asking is if anyone knows whether something like this exists, or ideas on implementation strategies if I end up doing it myself.

[Dave Aronson]

On Mon, Mar 30, 2015 at 3:55 PM, Fredrik Boström <fbos...@gmail.com> wrote:

> Maybe I expressed myself a bit unclearly. I'm not trying to reinvent SQL or
> create a new database implementation, that's insane!

Okay. Maybe I jumped the gun a bit too; I've seen lots of zealous
people, at the stage where they "know enough to be dangerous", head
down such a path. :-)

> What I'm after is a way to
> do more complex filtering of the result set using query parameters than what
> ActiveRecord now supports.

Perhaps you could take query params that included embedded ANDs and
ORs and some parentheses, and pass them off to some gem that can
handle that on each field. So, you would wind up with a URL like:

GET /api/users?name=Doris OR Fred&location=Stockholm&hobbies=horseback
OR swimming

Then, inside your controller (or better yet, User.search method or
UserSearch.call or whatever), have it take whatever searchable params
were given and hand them off to, say, Solr or ThinkingSphinx or
whatever.

Or-ing THOSE together, so as to look for anyone who's in Stockholm OR
is interested in horseback or swimming, is another story. I think you
can do that with Solr (been a while since I've used it) but of course
the handling and syntax would be different.

In summary, check out the assorted search gems, especially full-text
search, see what they can do for you, and base your syntax on what can
be easily broken up into the kinds of pieces your preferred search gem
expects.

-Dave

[Fredrik Boström]

Thanks Dave.

Yes, ORing over values for the same attribute is not a problem, that can be done with something like name=['Doris', 'Bob'] or similar, which would generate an SQL IN statement (where name in ('Bob', 'Doris')). The problem is when ORing two different attributes or arbitrarily mixing ANDs and ORs.

Anyhow, I ran in to ransack, which seems to be a promising starting point for this kind of functionality, if not supported out of the box. Haven't had time to try it out yet.

 - fred