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.
Check out http://github.com/ezmobius/ez-where/tree/master. 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. On Jul 28, 3:39 pm, Craig White <craigwh...-BQ75lA0ptkhBDgjK7y7TUQ@public.gmane.org> wrote:> 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.
Thanks...I can''t walk without my crutch. You made my day. Craig On Tue, 2009-07-28 at 14:15 -0700, Rick wrote:> Check out http://github.com/ezmobius/ez-where/tree/master. 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. > > On Jul 28, 3:39 pm, Craig White <craigwh...-BQ75lA0ptkhBDgjK7y7TUQ@public.gmane.org> wrote: > > 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. > > >-- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.
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:> Check out http://github.com/ezmobius/ez-where/tree/master. 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. > > On Jul 28, 3:39 pm, Craig White <craigwh...-BQ75lA0ptkhBDgjK7y7TUQ@public.gmane.org> wrote: > > 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?-- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.
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.) Best, -- Marnen Laibow-Koser http://www.marnen.org marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/.
On Wed, 2009-07-29 at 16:55 +0200, Marnen Laibow-Koser wrote:> 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.)---- 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.
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 paddleNo you''re not. You have a simple syntax error that I even foresaw and suggested a fix for in my earlier post.> > CraigBest, -- Marnen Laibow-Koser http://www.marnen.org marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/.
On Wed, 2009-07-29 at 19:24 +0200, Marnen Laibow-Koser wrote:> 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 => ["taxauthidIN ?", @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 => ["taxauthidIN ?", @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.
On Wed, 2009-07-29 at 10:35 -0700, Craig White wrote:> On Wed, 2009-07-29 at 19:24 +0200, Marnen Laibow-Koser wrote: > > 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'') > > >> @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---- one more...>> @taxauthids = [ "24", "25", "26", "27" ]=> ["24", "25", "26", "27"]>> @debts = Debtortranstaxes.find(:all, :conditions => ["taxauthidIN ?", (@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.
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-mailI did. Please take the time to understand where your errors are coming from.> > Craig >Best, -- Marnen Laibow-Koser http://www.marnen.org marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/.
Craig White 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 marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/.
On Wed, 2009-07-29 at 19:46 +0200, Marnen Laibow-Koser wrote:> 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.---- 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.
Craig White 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 marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/.
On Wed, 2009-07-29 at 20:17 +0200, Marnen Laibow-Koser wrote:> Craig White 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.---- 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.
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. Best, -- Marnen Laibow-Koser http://www.marnen.org marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/.
On Wed, 2009-07-29 at 22:20 +0200, Marnen Laibow-Koser wrote:> 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.---- 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.
On Wed, Jul 29, 2009 at 7:14 AM, Craig White<craigwhite-BQ75lA0ptkhBDgjK7y7TUQ@public.gmane.org> 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 Schroeder ------------------------ hassan.schroeder-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org twitter: @hassan
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 Jones On Jul 29, 1:44 pm, Craig White <craigwh...-BQ75lA0ptkhBDgjK7y7TUQ@public.gmane.org> wrote:> On Wed, 2009-07-29 at 10:35 -0700, Craig White wrote: > > On Wed, 2009-07-29 at 19:24 +0200, Marnen Laibow-Koser wrote: > > > 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'') > > > >> @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 > > ---- > 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.