# How to write this SQL query? **URL:** \ **Category:** Rails\ **Created:** [March 22, 2006, 7:29pm UTC](https://www.ruby-forum.com/t/how-to-write-this-sql-query/55652 "2006-03-22T19:29:48Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![g0nzo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/3bc359/32.png) [@g0nzo](https://www.ruby-forum.com/u/g0nzo)\ **Post date:** [March 22, 2006, 7:29pm UTC](https://www.ruby-forum.com/t/how-to-write-this-sql-query/55652/1 "2006-03-22T19:29:48Z")
Hi! Is there rails version of “where column in (value1, value2, …)”? I know i could do OR many times, but this way is shorter. I’m ruby/rails newbie, so i have a problem with converting the hash, which i’m receving from the search form into the string for :conditions in find method. Could i instead of creating one, complicated (for me) query do something like this: - query the database specyfing only one condition (as a condition, i could use parameter, which is necessary; if it’s not specified there’s nothing to search for), - then query obtained result set from the previous query with a new condition (unless it’s nil/empty), - and so on for every received parameter? ---
**Author:** ![g0nzo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/3bc359/32.png) [@g0nzo](https://www.ruby-forum.com/u/g0nzo)\ **Post date:** [March 23, 2006, 1:46am UTC](https://www.ruby-forum.com/t/how-to-write-this-sql-query/55652/2 "2006-03-23T01:46:11Z")
Hi ! 2006/3/22, szymek [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid): > Is there rails version of “where column in (value1, value2, …)”? > I know i could do OR many times, but this way is shorter. Try this: values = %w(a b c) @models = Model.find(:all, :conditions =\> [‘column IN (?)’, values]) > I’m ruby/rails newbie, so i have a problem with converting the hash, > which i’m receving from the search form into the string for :conditions > in find method. This is easy, just unpack the parameters as you go along: Model.find(:all, :conditions =\> [‘expires\_at BETWEEN :start AND :end’, {:start =\> params[:start], :end =\> params[:end]}]) Here are some links you might want to investigate: [http://blog.teksol.info/articles/2005/12/09/building-the-sql-where-clause-dynamically-revisited](http://blog.teksol.info/articles/2005/12/09/building-the-sql-where-clause-dynamically-revisited) [http://opensvn.csie.org/ezra/rails/plugins/dev/ez\_where/README.txt](http://opensvn.csie.org/ezra/rails/plugins/dev/ez_where/README.txt) Hope that helps ! ---
**Author:** ![g0nzo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/3bc359/32.png) [@g0nzo](https://www.ruby-forum.com/u/g0nzo)\ **Post date:** [March 23, 2006, 10:47am UTC](https://www.ruby-forum.com/t/how-to-write-this-sql-query/55652/3 "2006-03-23T10:47:47Z")
Thank you (and Ezra Z. and Fabien F.!) very, very much! This ez\_where plugin is really great! Why it’s not in rails core yet? 🙂 BTW. Would it be possible to create some kind of automatic search plugin based on ez\_where, so that conditions like ==, ===, \<=\> and so on, where deterimned by some input hash naming convention or something like this? I.e. names of form elements would be sth like: name=“form[is][column]” name=“form[like][string\_column]” name=“form[lt][numeric\_column]” and so on. Then it would just generate new condition (adding one at a time using “\<\<”, if received paremter is not empty), which could later be used anywhere using “condition.to\_sql” method? ---
**Author:** ![g0nzo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/3bc359/32.png) [@g0nzo](https://www.ruby-forum.com/u/g0nzo)\ **Post date:** [March 23, 2006, 4:44pm UTC](https://www.ruby-forum.com/t/how-to-write-this-sql-query/55652/4 "2006-03-23T16:44:19Z")
On Thu, 2006-03-23 at 10:47 +0100, szymek wrote: > Thank you (and Ezra Z. and Fabien F.!) very, very much! > > This ez\_where plugin is really great! > Why it’s not in rails core yet? 🙂 * * * ## amen > Then it would just generate new condition (adding one at a time using > “\<\<”, if received paremter is not empty), which could later be used > anywhere using “condition.to\_sql” method? * * * If I understand you correctly, it’s already there…just not in that form, see the examples on his blog. Craig ---
**Author:** ![g0nzo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/3bc359/32.png) [@g0nzo](https://www.ruby-forum.com/u/g0nzo)\ **Post date:** [March 23, 2006, 6:51pm UTC](https://www.ruby-forum.com/t/how-to-write-this-sql-query/55652/5 "2006-03-23T18:51:59Z")
On Mar 23, 2006, at 7:42 AM, Craig W. wrote: > On Thu, 2006-03-23 at 10:47 +0100, szymek wrote: > > > Thank you (and Ezra Z. and Fabien F.!) very, very > > much! > > > > This ez\_where plugin is really great! > > Why it’s not in rails core yet? 🙂 > > * * * > > amen ``` Thanks guys. I don't really feel like this is core material. It ``` works perfect as a plugin. I do use it all the time though because I like the ruby syntax instead of sql. It also needs more polish before it would be appropriate for inclusion. I think this type of AR extension is exactly what plugins are great for. ``` Thanks for using it and please do give feedback or send me a code ``` snippet if you do something cool with it 😉 > > name=“form[like][string\_column]” > > name=“form[lt][numeric\_column]” and so on. > > > > Then it would just generate new condition (adding one at a time using > > “\<\<”, if received paremter is not empty), which could later be used > > anywhere using “condition.to\_sql” method? > > * * * > > If I understand you correctly, it’s already there…just not in that > form, see the examples on his blog. > > Craig ``` Yeah it can already do what you are asking just in a little bit ``` different syntax. Download the plugin and look at the ez\_where\_tests.rb file. That is the best place for now to see all the things its capable of. There are _tons_ of different ways to use it so the tests cover all the ways we have discovered so far. Cheers- -Ezra