I''d like to search for invoices in the database, having the date range set (from, to). The problem is that invoices whose issue dates match the ''from'' or ''to'' dates will not show up in results. For example: Created an invoice today ("2009-02-09"), and I''m searching by issue_date between from and to, these are the params: "filter"=>{"from"=>"2009-02-09", "to"=>"2009-02-09"} In the model I have the sql conditions, as well as a scope, looks like this: self.cond = Array.new self.cond << ["invoices.issue_date >= ?", from] unless from.blank? self.cond << ["invoices.issue_date <= ?", to] unless to.blank? result = join_conditions --- named_scope :filter, lambda { |invoice_filter| { :conditions => invoice_filter.sql_conditions }} The scope gets a filter object that has both ''from'' and ''to'' fields set. If I set the ''to'' field to tomorrow''s date, then the invoice will appear, but I don''t understand why it wouldn''t work as is since there are ''<='' & ''>='' comparisons in conditions. Any help or suggestion would be appreciated! -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
How about: ... require ''time'' ... invoices = Invoice.find(:all, :conditions=>[''issue_date is not null and (issue_date >= ? or issue_date <= ?)'', Time.parse(''2009-02-09 00:00:00''), Time.parse(''2009-02-09 23:59:59'')] :order=>''issue_date'') ... Jeff On Feb 9, 9:29 am, Gabi Ge <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> I''d like to search for invoices in the database, having the date range > set (from, to). The problem is that invoices whose issue dates match the > ''from'' or ''to'' dates will not show up in results. > > For example: > Created an invoice today ("2009-02-09"), and I''m searching by issue_date > between from and to, these are the params: > > "filter"=>{"from"=>"2009-02-09", "to"=>"2009-02-09"} > > In the model I have the sql conditions, as well as a scope, looks like > this: > > self.cond = Array.new > self.cond << ["invoices.issue_date >= ?", from] unless from.blank? > self.cond << ["invoices.issue_date <= ?", to] unless to.blank? > result = join_conditions > --- > named_scope :filter, lambda { |invoice_filter| > { :conditions => invoice_filter.sql_conditions }} > > The scope gets a filter object that has both ''from'' and ''to'' fields set. > If I set the ''to'' field to tomorrow''s date, then the invoice will > appear, but I don''t understand why it wouldn''t work as is since there > are ''<='' & ''>='' comparisons in conditions. > > Any help or suggestion would be appreciated! > -- > Posted viahttp://www.ruby-forum.com/.--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
things = Thing.find_all_by_field(''start_at''..''end_at'') On Feb 9, 7:29 am, Gabi Ge <rails-mailing-l...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> I''d like to search for invoices in the database, having the date range > set (from, to). The problem is that invoices whose issue dates match the > ''from'' or ''to'' dates will not show up in results. > > For example: > Created an invoice today ("2009-02-09"), and I''m searching by issue_date > between from and to, these are the params: > > "filter"=>{"from"=>"2009-02-09", "to"=>"2009-02-09"} > > In the model I have the sql conditions, as well as a scope, looks like > this: > > self.cond = Array.new > self.cond << ["invoices.issue_date >= ?", from] unless from.blank? > self.cond << ["invoices.issue_date <= ?", to] unless to.blank? > result = join_conditions > --- > named_scope :filter, lambda { |invoice_filter| > { :conditions => invoice_filter.sql_conditions }} > > The scope gets a filter object that has both ''from'' and ''to'' fields set. > If I set the ''to'' field to tomorrow''s date, then the invoice will > appear, but I don''t understand why it wouldn''t work as is since there > are ''<='' & ''>='' comparisons in conditions. > > Any help or suggestion would be appreciated! > -- > Posted viahttp://www.ruby-forum.com/.--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Jeff Burlysystems wrote:> How about: > > ... > require ''time'' > ... > invoices = Invoice.find(:all, :conditions=>[''issue_date is not null > and (issue_date >= ? or issue_date <= ?)'', Time.parse(''2009-02-09 > 00:00:00''), Time.parse(''2009-02-09 23:59:59'')] :order=>''issue_date'') > ... > > JeffThanks for your reply, and your help! It gave me a hint and solved the problem like this: self.cond << ["invoices.issue_date > ?", from - 1] unless from.blank? self.cond << ["invoices.issue_date < ?", to + 1] unless to.blank? because issue_date is a datetime, and generating an invoice means saving both date and time of the day. This way the search will have the same results as in your example. Once again, thank you for your help! -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
On 9 Feb 2009, at 17:29, Gabi Ge wrote:> > I''d like to search for invoices in the database, having the date range > set (from, to). The problem is that invoices whose issue dates match > the > ''from'' or ''to'' dates will not show up in results. > > For example: > Created an invoice today ("2009-02-09"), and I''m searching by > issue_date > between from and to, these are the params: > > "filter"=>{"from"=>"2009-02-09", "to"=>"2009-02-09"}Is the issue date a date column or a datetime column ? if you have a datetime column containing 2009-02-09 14:00 and compare it to 2009-02-09 then the 2009-02-09 is turned into 2009-02-09 00:00 which can mess things up for you. Fred> > > In the model I have the sql conditions, as well as a scope, looks like > this: > > self.cond = Array.new > self.cond << ["invoices.issue_date >= ?", from] unless from.blank? > self.cond << ["invoices.issue_date <= ?", to] unless to.blank? > result = join_conditions > --- > named_scope :filter, lambda { |invoice_filter| > { :conditions => invoice_filter.sql_conditions }} > > The scope gets a filter object that has both ''from'' and ''to'' fields > set. > If I set the ''to'' field to tomorrow''s date, then the invoice will > appear, but I don''t understand why it wouldn''t work as is since there > are ''<='' & ''>='' comparisons in conditions. > > Any help or suggestion would be appreciated! > -- > Posted via http://www.ruby-forum.com/. > > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
I didn''t know you could pass an array of arrays! Blog: http://random8.zenunit.com/ Learn rails: http://sensei.zenunit.com/ On 10/02/2009, at 6:29 AM, Gabi Ge <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> > Jeff Burlysystems wrote: >> How about: >> >> ... >> require ''time'' >> ... >> invoices = Invoice.find(:all, :conditions=>[''issue_date is not null >> and (issue_date >= ? or issue_date <= ?)'', Time.parse(''2009-02-09 >> 00:00:00''), Time.parse(''2009-02-09 23:59:59'')] :order=>''issue_date'') >> ... >> >> Jeff > > Thanks for your reply, and your help! It gave me a hint and solved the > problem like this: > > self.cond << ["invoices.issue_date > ?", from - 1] unless from.blank? > self.cond << ["invoices.issue_date < ?", to + 1] unless to.blank? > > because issue_date is a datetime, and generating an invoice means > saving > both date and time of the day. This way the search will have the same > results as in your example. > > Once again, thank you for your help! > -- > Posted via http://www.ruby-forum.com/. > > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---