Forum: Ruby on Rails How to write this SQL query?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Szymon N. (Guest)
on 2006-03-22 20:29
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?
François B. (Guest)
on 2006-03-23 02:46
(Received via mailing list)
Hi !

2006/3/22, szymek <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/buildi...
http://opensvn.csie.org/ezra/rails/plugins/dev/ez_...

Hope that helps !
Szymon N. (Guest)
on 2006-03-23 11:47
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?
Craig W. (Guest)
on 2006-03-23 17:44
(Received via mailing list)
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
Ezra Z. (Guest)
on 2006-03-23 19:51
(Received via mailing list)
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
This topic is locked and can not be replied to.