I always used Ezra's ez-where plugin for finds, especially complex finds
but I see it hasn't been updated since 2006 so I am real leery to start
with it now on a new project in rails 2.3.2
What I want to do is rather complex and ez-where handled this for me
simply. I want to...
@debtor = Debtortrans.find(:all,
:conditions => ["trandate > ? and trandate < ? AND taxauthid = ?",
@per1, @per2, "24"],
:joins => 'LEFT JOIN debtortranstaxes ON
debtortranstaxes.debtortransid=id')
but I actually want
trandate > ? AND trandate < ? AND (
taxauthid = ?
or
taxauthid = ?
or
taxauthid = ?
or
taxauthid = ? )
Is there a modern plug-in for complex finds like this - ala ez-where or
how would I construct this in a single 'conditions' statement if I go
without plugin?
ez-where doesn't seem to work correctly with 2.3.2 and I have written an
e-mail to Ezra to see if he has noticed problems with current rails.
So I am once again looking for search utilities that make it easier to
build complex searches and so far I have found Squirrel but don't want
to have to install every search plugin I see.
If anyone has recommendations on Rails search plugins for current Rails,
I would gladly welcome.
Craig White wrote:
[...]
> What I want to do is rather complex and ez-where handled this for me
> simply. I want to...
>
> @debtor = Debtortrans.find(:all,
> :conditions => ["trandate > ? and trandate < ? AND taxauthid = ?",
> @per1, @per2, "24"],
> :joins => 'LEFT JOIN debtortranstaxes ON
> debtortranstaxes.debtortransid=id'
The simple case is easy. But you don't need a plugin even for the
complex case.
>
> but I actually want
>
> trandate > ? AND trandate < ? AND (
> taxauthid = ?
> or
> taxauthid = ?
> or
> taxauthid = ?
> or
> taxauthid = ? )
So do :conditions => ['trandate BETWEEN :begin AND :end AND taxauthid IN
:ids', {:begin => start_date, :end => end_date, :ids => [id1, id2,
id3]}]. Simple.
(You might need parentheses around :ids in the query. I'm not sure.)
> Craig White wrote:
> Note that I said:
> >> (You might need parentheses around :ids in the query. I'm not sure.)
>
> Note that the generated query is
> [...]
> > 1: SELECT * FROM `debtortranstaxes` WHERE (taxauthid IN
> > '24','25','26','27')
>
> So it looks like adding the parentheses will fix your problem.
>
> > So I am still in the creek without a paddle
>
> No you're not. You have a simple syntax error that I even foresaw and
> suggested a fix for in my earlier post.
----
I have traced parens around, inside, outside and nothing works...
>> @taxauthids = ([ "24", "25", "26", "27" ])
=> ["24", "25", "26", "27"]
>> @debts = Debtortranstaxes.find(:all, :conditions => ["taxauthid
IN ?", @taxauthids])
ActiveRecord::StatementInvalid: Mysql::Error: You have an error in your
SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near ''24','25','26','27')' at line
1: SELECT * FROM `debtortranstaxes` WHERE (taxauthid IN
'24','25','26','27')
>> @taxauthids = ([ "24", "25", "26", "27" ])
=> ["24", "25", "26", "27"]
>> @debts = Debtortranstaxes.find(:all, :conditions => ["taxauthid
IN ?", @taxauthids])
ActiveRecord::StatementInvalid: Mysql::Error: You have an error in your
SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near ''24','25','26','27')' at line
1: SELECT * FROM `debtortranstaxes` WHERE (taxauthid IN
'24','25','26','27')
I have traced parens around, inside, outside and nothing works...
Because none of your examples address the problem. The problem is in
your *SQL* syntax, so the parentheses need to be in the SQL query string
-- :conditions => '...and id IN (?)'.
Your Ruby syntax is fine, so messing with it won't help.
also - see previous examples from previous e-mail
I did. Please take the time to understand where your errors are coming
from.
Craig White wrote:
[...]
> I have traced parens around, inside, outside and nothing works...
Because none of your examples address the problem. The problem is in
your *SQL* syntax, so the parentheses need to be in the SQL query string
-- :conditions => '...and id IN (?)'.
Your Ruby syntax is fine, so messing with it won't help.
> also - see previous examples from previous e-mail
I did. Please take the time to understand where your errors are coming
from.
Craig White wrote:
>> > also - see previous examples from previous e-mail
>>
>> I did. Please take the time to understand where your errors are coming
>> from.
> ----
> OK, I understand now...wow, was that every difficult.
If you found that difficult, then *please* spend some time studying AR
and SQL as I suggested in my previous post. The problem you were having
here was actually fairly simple, if not absolutely trivial. I say this
not to brag or insult, but rather to give you an idea about what you
should be able to do -- and do easily -- in order to develop Web
applications effectively.
Craig White wrote:
[...]
> thanks - I have used Rails quite a bit but have always used ez-where
> (Rails 1.1/1.2) and it made it easy for me to do finds.
I'm not familiar with ez-where, but it sounds like it's made it possible
for you to do lots of things without understanding the underlying SQL.
That may not be a good thing: automation is great, but you should always
understand what's being automated -- use it as a tool, not a crutch.
>
> It simply doesn't work in Rails 2.3.2 and thus it has forced me into
> executing finds using Rails code directly which is not where I want to
> spend so many hours.
Spend 30 minutes with your database's SQL docs and another 15 minutes
with the ActiveRecord rdoc, and all will be come clear. It's not
particularly difficult, but it is essential.
