How to write this SQL query?

Rails
1

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?

2

Hi !

2006/3/22, szymek [email protected]:

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://opensvn.csie.org/ezra/rails/plugins/dev/ez_where/README.txt

Hope that helps !

3

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

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?

4

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

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

5

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

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 :wink:

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