Complex finds - part 2

Rails
1

I am learning new, was comfortable with 1.2.x

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?

Craig

–
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

2

Check out GitHub - ezmobius/ez-where: query/conditions builder for ActiveRecord and other ORMS. Most
recent change activity is Feb 2009 - Rails 2.2. Looks to be the same
Ezra and it might be a better place to start from.

3

Thanks…I can’t walk without my crutch. You made my day.

Craig

On Tue, 2009-07-28 at 14:15 -0700, Rick wrote:

how would I construct this in a single ‘conditions’ statement if I go
without plugin?

Craig

–
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

–
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

4

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.

Thanks

Craig

On Tue, 2009-07-28 at 14:15 -0700, Rick wrote:

how would I construct this in a single ‘conditions’ statement if I go
without plugin?

–
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

5

On Wed, 2009-07-29 at 16:55 +0200, Marnen Laibow-Koser wrote:

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.)

I would say that my ignorance notwithstanding, this is a very dismal
state of affairs for current version of Rails. ez-where returns all
records with every search on Rails 2.3.2 where squirrel always returns
no records with searches.

So I try to use Rails without a plugin and it is painful. Even
simplifying to an extreme level…

@taxauthids = [ “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”)]
@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’)

So I am still in the creek without a paddle

Craig

–
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

6

Craig W. 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.

Craig

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

7

Craig W. 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.)

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

8

On Wed, 2009-07-29 at 19:24 +0200, Marnen Laibow-Koser wrote:

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’)

@taxauthids = [( “24”, “25”, “26”, “27”) ]
SyntaxError: compile error
(irb):13: syntax error
@taxauthids = [( “24”, “25”, “26”, “27”) ]
^
(irb):13: syntax error
@taxauthids = [( “24”, “25”, “26”, “27”) ]
^
(irb):13: syntax error
@taxauthids = [( “24”, “25”, “26”, “27”) ]
^
from (irb):13

also - see previous examples from previous e-mail

Craig

–
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

9

Craig W. 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

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

10

On Wed, 2009-07-29 at 10:35 -0700, Craig W. wrote:

So it looks like adding the parentheses will fix your problem.

@debts = Debtortranstaxes.find(:all, :conditions => [“taxauthid
IN ?”, @taxauthids])
^
(irb):13: syntax error
@taxauthids = [( “24”, “25”, “26”, “27”) ]
^
(irb):13: syntax error
@taxauthids = [( “24”, “25”, “26”, “27”) ]
^
from (irb):13

also - see previous examples from previous e-mail

one more…

@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’)

This just simply does not work. I think that even though I am not the
most knowledgeable Rails user, this suggests unacceptable state of
affairs for current Rails.

Craig

–
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

11

On Wed, 2009-07-29 at 19:46 +0200, Marnen Laibow-Koser 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.

Thanks

Craig

–
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

12

Craig W. wrote:
[…]

1: SELECT * FROM debtortranstaxes WHERE (taxauthid IN
‘24’,‘25’,‘26’,‘27’)

This just simply does not work. I think that even though I am not the
most knowledgeable Rails user, this suggests unacceptable state of
affairs for current Rails.

No, it suggests that you don’t yet know enough about SQL syntax, or
about what comes from SQL and what from Ruby. Instead of blaming Rails,
go learn a little more about how ActiveRecord generates SQL (the rdoc is
a good place to start), and read your DB server’s documentation on SQL
syntax.

Craig

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

13

Craig W. wrote:

On Wed, 2009-07-29 at 19:46 +0200, Marnen Laibow-Koser 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.

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

14

Craig W. 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.

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

15

On Wed, 2009-07-29 at 20:17 +0200, Marnen Laibow-Koser wrote:

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.

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.

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. Apparently squirrel also does not work in 2.3.2

Thanks

Craig

–
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

16

On Wed, 2009-07-29 at 22:20 +0200, Marnen Laibow-Koser wrote:

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.

I’m not disagreeing with you but I am furiously trying to do some
reports from mysql legacy database system when I am familiar with Rails
1.2 and postgresql and ez-where. I think if you had been spoiled by a
tool like ez-where, you would understand why I was so lost without it. I
never really had to construct SQL queries…at least not beyond simple.

Craig

–
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

17

On Wed, Jul 29, 2009 at 7:14 AM, Craig W.[email protected]
wrote:

If anyone has recommendations on Rails search plugins for current Rails,
I would gladly welcome.

I have applications running using acts_as_ferret (very easy to set up)
and acts_as_solr (works well for multi-system config).

FWIW,

Hassan S. ------------------------ [email protected]
twitter: @hassan

18

The issue is that your generated query is missing parentheses. Why
have you tried everything EXCEPT putting them in the SQL fragment? To
wit:

@debts = Debtortranstaxes.find(:all, :conditions => [“taxauthid IN
(?)”, @taxauthids])

–Matt J.