SQL query builder in rails

Question

We are trying to build a SQL query builder that would allow users to make queries using visual aid .
We have got the front end all worked up with the user being able to select tables add condition etc . However our backend is a mess as we are trying to make these queries as string taking the conditions from the user , and then making appropriate SQL strings in our models .
This would eventually become very buggy and difficult to maintain as we add support for more and more complex queries . Is anybody aware of a sql builder plugin that we can use , or a better way to approach this .

Answer 1

I also tried to build one of these. In fact I am trying to build one right now. It is very frustrating, buggy. These links might be helpful.

• http://vark.com/t/96f89d
• http://aub.github.com/record%5Ffilter/

Using record filter to make complex queries makes sense. I am trying an approach in which I take the query string from the user in Json format. Then convert this string into valid record filter syntax, this will require parsing the whole string. After we have parsed the string and if it is in correct syntax, I just eval it and get the results.

Answer 2

You should look at the condition builder plugin:

http://blog.inquirylabs.com/2007/01/04/condition-builder-10-released/

Not sure if it will answer all your needs, but it's been working well for me.

Answer 3

Hi. I have the same issue. I am a total NOOB (in fact, I think this comment may be misplaced but I can't seem to find another place to make it) but it seems ridiculous that there is no common solution to what I would imagine to be a nearly universal problem (finding a set objects with certain parameters). I can't help but feel like I'm missing something really obvious...

Below are the solutions i've found. Unfortunately, I am too J.V. to identify the best one.

chrisblunt.com/blog/2009/05/12/rails-building-complex-search-filters-with-activerecord-and-ez_where/

brainspl.at/articles/2006/01/30/i-have-been-busy

robots.thoughtbot.com/post/159809435/squirrel-natural-looking-queries-for-rails#faviki_close

github.com/aub/record_filter

blog.inquirylabs.com/2007/06/28/condition-builder-11/

Any one have anything to say about any of these?

Thanks, Brett

Answer 4

Check out arel. A fork of this project is now used in Rails 3 to help with ORM agnosticism.

Answer 5

The railscast about searchlogic for advanced search forms shows how to chain together named_scopes and use them to do complex dynamic queries.

Here's the searchlogic repo, too.