More elaborate way to make model queries from the URL?

Rails
1

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
http://docs.strongloop.com/display/public/LB/Where+filter#Wherefilter-and/or
:

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
https://github.com/activerecord-hackery/squeel, sequel-rails
<goog_33395714> 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!

2

On Mon, Mar 30, 2015 at 12:33 PM, Fredrik Boström [email protected]
wrote:

?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?

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 A., consulting software developer of Codosaur.us,
PullRequestRoulette.com, Blog.Codosaur.us, and Dare2XL.com.

3

On Mon, Mar 30, 2015 at 3:55 PM, Fredrik Boström [email protected]
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. :slight_smile:

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

–
Dave A., consulting software developer of Codosaur.us,
PullRequestRoulette.com, Blog.Codosaur.us, and Dare2XL.com.

4

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
http://guides.rubyonrails.org/active_record_querying.html#hash-conditions

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.

5

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
https://github.com/activerecord-hackery/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