Hi everybody, I''ve read lot of thread about this topic but still can''t figure out the best practice to use. I''ve this entry with a Date attribute, in the yyyy-mm-dd form. I need to retrieve all the articles in a data Range, nothing of what I read really worked..what''s the matter? Using find(:all, :conditions .... ) I''ve tryed: - date between ? and ? - date => from..to - year(date) nothing happened..is ruby able to evaluate range for Dates? please show me the way :S thx
On Oct 2, 12:12 pm, adedip <ade...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Hi everybody, > I''ve read lot of thread about this topic but still can''t figure out > the best practice to use. > > I''ve this entry with a Date attribute, in the yyyy-mm-dd form. >is this attribute actually a date or a string containing a representation of a date ? Fred> I need to retrieve all the articles in a data Range, nothing of what I > read really worked..what''s the matter? > > Using find(:all, :conditions .... ) > I''ve tryed: > - date between ? and ? > - date => from..to > - year(date) > > nothing happened..is ruby able to evaluate range for Dates? > please show me the way :S > thx
On 02 Oct 2009, at 13:12, adedip wrote:> I''ve this entry with a Date attribute, in the yyyy-mm-dd form. > > I need to retrieve all the articles in a data Range, nothing of what I > read really worked..what''s the matter? > > Using find(:all, :conditions .... ) > I''ve tryed: > - date between ? and ? > - date => from..to > - year(date) > > nothing happened..is ruby able to evaluate range for Dates? > please show me the way :SIf and only if your date field in the database is a date or a time: Model.all(:conditions => {:date => 7.days.ago..Date.today}) Model.all(:conditions => ["date BETWEEN ? AND ?", 7.days.ago ,Date.today]) both generate and return valid results: SELECT * FROM `models` WHERE (`models`.`date` BETWEEN ''2009-09-25 12:35:21'' AND ''2009-10-02'') I prefer the first notation, but tastes might differ. Best regards Peter De Berdt --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
@Fred of course is a Date type and not string ;) On 2 Ott, 14:39, Peter De Berdt <peter.de.be...-LPO8gxj9N8aZIoH1IeqzKA@public.gmane.org> wrote:> On 02 Oct 2009, at 13:12, adedip wrote: > > > I''ve this entry with a Date attribute, in the yyyy-mm-dd form. > > > I need to retrieve all the articles in a data Range, nothing of what I > > read really worked..what''s the matter? > > > Using find(:all, :conditions .... ) > > I''ve tryed: > > - date between ? and ? > > - date => from..to > > - year(date) > > > nothing happened..is ruby able to evaluate range for Dates? > > please show me the way :S > > If and only if your date field in the database is a date or a time: > > Model.all(:conditions => {:date => 7.days.ago..Date.today}) > Model.all(:conditions => ["date BETWEEN ? AND ?", > 7.days.ago ,Date.today])good to know..and thanks for this..but what if a have to choose among latter periods like from 1993 to 2001? (I''m dealing with newspaper articles) And I have in the search form a select with all the available Dates (in the yyyy-mm-dd) format. It could be good to me to only use year, so the question is how the get only the year from a Date type? I''ve tryed year(date) but it does not work (al least in the range actually)..or second aswer do I have to write a method that calculates how many days ago I want? even for 1993 ? thanks so far ;) Andrea> > both generate and return valid results: > > SELECT * FROM `models` WHERE (`models`.`date` BETWEEN ''2009-09-25 > 12:35:21'' AND ''2009-10-02'') > > I prefer the first notation, but tastes might differ. > > Best regards > > Peter De Berdt
2009/10/2 adedip <adedip-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>:> > @Fred of course is a Date type and not string ;) > > On 2 Ott, 14:39, Peter De Berdt <peter.de.be...-LPO8gxj9N8aZIoH1IeqzKA@public.gmane.org> wrote: >> On 02 Oct 2009, at 13:12, adedip wrote: >> >> > I''ve this entry with a Date attribute, in the yyyy-mm-dd form. >> >> > I need to retrieve all the articles in a data Range, nothing of what I >> > read really worked..what''s the matter? >> >> > Using find(:all, :conditions .... ) >> > I''ve tryed: >> > - date between ? and ? >> > - date => from..to >> > - year(date) >> >> > nothing happened..is ruby able to evaluate range for Dates? >> > please show me the way :S >> >> If and only if your date field in the database is a date or a time: >> >> Model.all(:conditions => {:date => 7.days.ago..Date.today}) >> Model.all(:conditions => ["date BETWEEN ? AND ?", >> 7.days.ago ,Date.today]) > > good to know..and thanks for this..but what if a have to choose among > latter periods like from 1993 to 2001? > (I''m dealing with newspaper articles) > And I have in the search form a select with all the available Dates > (in the yyyy-mm-dd) format. > > It could be good to me to only use year, so the question is how the > get only the year from a Date type? I''ve tryed year(date) but it does > not work (al least in the range actually)..or second aswer do I have > to write a method that calculates how many days ago I want? even for > 1993 ?Take the date from your form, which I think you are saying is a string in "yyyy-mm-dd" format and convert it into a Date object. Then you can use it in the find conditions as suggested above. Colin
Anyway..this is the only that returns valid results! thanks man! it works even with 7.years.ago ! cool :) so I''d need only to extract the year from the date params I get from the form..and then maybe calculete how many years ago it is. year(params[:date]) #?should this work? :) On 2 Ott, 14:39, Peter De Berdt <peter.de.be...-LPO8gxj9N8aZIoH1IeqzKA@public.gmane.org> wrote:> On 02 Oct 2009, at 13:12, adedip wrote: > > > I''ve this entry with a Date attribute, in the yyyy-mm-dd form. > > > I need to retrieve all the articles in a data Range, nothing of what I > > read really worked..what''s the matter? > > > Using find(:all, :conditions .... ) > > I''ve tryed: > > - date between ? and ? > > - date => from..to > > - year(date) > > > nothing happened..is ruby able to evaluate range for Dates? > > please show me the way :S > > If and only if your date field in the database is a date or a time: > > Model.all(:conditions => {:date => 7.days.ago..Date.today}) > Model.all(:conditions => ["date BETWEEN ? AND ?", > 7.days.ago ,Date.today]) > > both generate and return valid results: > > SELECT * FROM `models` WHERE (`models`.`date` BETWEEN ''2009-09-25 > 12:35:21'' AND ''2009-10-02'') > > I prefer the first notation, but tastes might differ. > > Best regards > > Peter De Berdt
On 2 Ott, 17:35, Colin Law <clan...-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org> wrote:> 2009/10/2 adedip <ade...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>: > > > > > > > @Fred of course is a Date type and not string ;) > > > On 2 Ott, 14:39, Peter De Berdt <peter.de.be...-LPO8gxj9N8aZIoH1IeqzKA@public.gmane.org> wrote: > >> On 02 Oct 2009, at 13:12, adedip wrote: > > >> > I''ve this entry with a Date attribute, in the yyyy-mm-dd form. > > >> > I need to retrieve all the articles in a data Range, nothing of what I > >> > read really worked..what''s the matter? > > >> > Using find(:all, :conditions .... ) > >> > I''ve tryed: > >> > - date between ? and ? > >> > - date => from..to > >> > - year(date) > > >> > nothing happened..is ruby able to evaluate range for Dates? > >> > please show me the way :S > > >> If and only if your date field in the database is a date or a time: > > >> Model.all(:conditions => {:date => 7.days.ago..Date.today}) > >> Model.all(:conditions => ["date BETWEEN ? AND ?", > >> 7.days.ago ,Date.today]) > > > good to know..and thanks for this..but what if a have to choose among > > latter periods like from 1993 to 2001? > > (I''m dealing with newspaper articles) > > And I have in the search form a select with all the available Dates > > (in the yyyy-mm-dd) format. > > > It could be good to me to only use year, so the question is how the > > get only the year from a Date type? I''ve tryed year(date) but it does > > not work (al least in the range actually)..or second aswer do I have > > to write a method that calculates how many days ago I want? even for > > 1993 ? > > Take the date from your form, which I think you are saying is a string > in "yyyy-mm-dd" format and convert it into a Date object. Then you > can use it in the find conditions as suggested above.it''s already a Date it''s not a string as I wrote above ;)> > Colin
or maybe I''m wrong about the type...
here''s the form code in the view:
<% @source = Article.find(:all) %>
From:
<%= collection_select("from", "date", @source,
"date","date",
{:include_blank => true}) %>
To:
<%= collection_select("to", "date", @source,
"date","date",
{:include_blank => true}) %>
On 2 Ott, 17:40, adedip <ade...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> On 2 Ott, 17:35, Colin Law
<clan...-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org> wrote:
>
> > 2009/10/2 adedip
<ade...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>:
>
> > > @Fred of course is a Date type and not string ;)
>
> > > On 2 Ott, 14:39, Peter De Berdt
<peter.de.be...-LPO8gxj9N8aZIoH1IeqzKA@public.gmane.org> wrote:
> > >> On 02 Oct 2009, at 13:12, adedip wrote:
>
> > >> > I''ve this entry with a Date attribute, in the
yyyy-mm-dd form.
>
> > >> > I need to retrieve all the articles in a data Range,
nothing of what I
> > >> > read really worked..what''s the matter?
>
> > >> > Using find(:all, :conditions .... )
> > >> > I''ve tryed:
> > >> > - date between ? and ?
> > >> > - date => from..to
> > >> > - year(date)
>
> > >> > nothing happened..is ruby able to evaluate range for
Dates?
> > >> > please show me the way :S
>
> > >> If and only if your date field in the database is a date or a
time:
>
> > >> Model.all(:conditions => {:date =>
7.days.ago..Date.today})
> > >> Model.all(:conditions => ["date BETWEEN ? AND
?",
> > >> 7.days.ago ,Date.today])
>
> > > good to know..and thanks for this..but what if a have to choose
among
> > > latter periods like from 1993 to 2001?
> > > (I''m dealing with newspaper articles)
> > > And I have in the search form a select with all the available
Dates
> > > (in the yyyy-mm-dd) format.
>
> > > It could be good to me to only use year, so the question is how
the
> > > get only the year from a Date type? I''ve tryed
year(date) but it does
> > > not work (al least in the range actually)..or second aswer do I
have
> > > to write a method that calculates how many days ago I want? even
for
> > > 1993 ?
>
> > Take the date from your form, which I think you are saying is a string
> > in "yyyy-mm-dd" format and convert it into a Date object.
Then you
> > can use it in the find conditions as suggested above.
>
> it''s already a Date it''s not a string as I wrote above ;)
>
>
>
> > Colin
On 02 Oct 2009, at 17:23, adedip wrote:> It could be good to me to only use year, so the question is how the > get only the year from a Date type? I''ve tryed year(date) but it does > not work (al least in the range actually)..or second aswer do I have > to write a method that calculates how many days ago I want? even for > 1993 ?Model.all(:conditions => ["YEAR(date) BETWEEN ? AND ?", 2000 ,2010]) works perfectly on mysql. If your database doesn''t support it, Model.all(:conditions => ["YEAR(date) >= ? AND YEAR(date) <= ?", 2000 ,2010]) might also work. Depends on whether you want an exact range from a certain date to a certain date or a range of years which option to choose. Best regards Peter De Berdt --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
2009/10/2 adedip <adedip-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>:> > or maybe I''m wrong about the type... > here''s the form code in the view: > > <% @source = Article.find(:all) %> > From: > <%= collection_select("from", "date", @source, "date","date", > {:include_blank => true}) %> > To: > <%= collection_select("to", "date", @source, "date","date", > {:include_blank => true}) %>If you are using params[:from] then it is a string. params[] are always strings. The fact that you keep saying it is in yyyy-mm-dd format does not make sense if it is a Date as a Date is not in a particular format till you convert it to string for display for example. Build a Date from the string and then you can compare it with your db value. Colin
Thanks again to Peter..that''s what I missed.. i need to call YEAR (date) even on the attribute side..not only for the variable.. :S thanks! I''ll try On 2 Ott, 18:04, Colin Law <clan...-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org> wrote:> 2009/10/2 adedip <ade...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>: > > > > > or maybe I''m wrong about the type... > > here''s the form code in the view: > > > <% @source = Article.find(:all) %> > > From: > > <%= collection_select("from", "date", @source, "date","date", > > {:include_blank => true}) %> > > To: > > <%= collection_select("to", "date", @source, "date","date", > > {:include_blank => true}) %> > > If you are using params[:from] then it is a string. params[] are > always strings. The fact that you keep saying it is in yyyy-mm-dd > format does not make sense if it is a Date as a Date is not in a > particular format till you convert it to string for display for > example. >that''s what I''ve realized few minutes ago :P so params[:from].to_date would do the job?!> Build a Date from the string and then you can compare it with your db value. > > Colin
On 2 Ott, 18:20, adedip <ade...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Thanks again to Peter..that''s what I missed.. i need to call YEAR > (date) even on the attribute side..not only for the variable.. :S > thanks! > I''ll try > > On 2 Ott, 18:04, Colin Law <clan...-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org> wrote: > > > 2009/10/2 adedip <ade...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>: > > > > or maybe I''m wrong about the type... > > > here''s the form code in the view: > > > > <% @source = Article.find(:all) %> > > > From: > > > <%= collection_select("from", "date", @source, "date","date", > > > {:include_blank => true}) %> > > > To: > > > <%= collection_select("to", "date", @source, "date","date", > > > {:include_blank => true}) %> > > > If you are using params[:from] then it is a string. params[] are > > always strings. The fact that you keep saying it is in yyyy-mm-dd > > format does not make sense if it is a Date as a Date is not in a > > particular format till you convert it to string for display for > > example. > > that''s what I''ve realized few minutes ago :P > > so > params[:from].to_date > would do the job?!or maybe from = Date.new(params[:from]) ?!> > > Build a Date from the string and then you can compare it with your db value. > > > Colin
2009/10/2 adedip <adedip-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>:> > > > On 2 Ott, 18:20, adedip <ade...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: >> Thanks again to Peter..that''s what I missed.. i need to call YEAR >> (date) even on the attribute side..not only for the variable.. :S >> thanks! >> I''ll try >> >> On 2 Ott, 18:04, Colin Law <clan...-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org> wrote: >> >> > 2009/10/2 adedip <ade...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>: >> >> > > or maybe I''m wrong about the type... >> > > here''s the form code in the view: >> >> > > <% @source = Article.find(:all) %> >> > > From: >> > > <%= collection_select("from", "date", @source, "date","date", >> > > {:include_blank => true}) %> >> > > To: >> > > <%= collection_select("to", "date", @source, "date","date", >> > > {:include_blank => true}) %> >> >> > If you are using params[:from] then it is a string. params[] are >> > always strings. The fact that you keep saying it is in yyyy-mm-dd >> > format does not make sense if it is a Date as a Date is not in a >> > particular format till you convert it to string for display for >> > example. >> >> that''s what I''ve realized few minutes ago :P >> >> so >> params[:from].to_date >> would do the job?! > or maybe > > from = Date.new(params[:from]) >Check the docs for Date. You might have to use strptime or one of the other constructor methods. Make sure you catch exceptions if converting from user typed input. Colin
ok..I swear this is the last (for today :P )
aut = params[:article][:author]
from = params[:from][:date]
to = params[:from][:date]
tt = Date.strptime(to, "%Y-%m-%d")
tf = Date.strptime(from, "%Y-%m-%d")
@articles = Article.find(:all, :order => :date, :conditions =>
{:author => aut, :date.year => tf.year..tt.year})
everything but :date.year works ..I''ve already tried year(date)
I can''t use between ? and ? cause I need to filter authors too.
so what?
(my eye is flickring :D)
thx guys
On 2 Ott, 18:35, Colin Law
<clan...-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org>
wrote:> 2009/10/2 adedip <ade...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>:
>
>
>
>
>
> > On 2 Ott, 18:20, adedip
<ade...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> >> Thanks again to Peter..that''s what I missed.. i need to
call YEAR
> >> (date) even on the attribute side..not only for the variable.. :S
> >> thanks!
> >> I''ll try
>
> >> On 2 Ott, 18:04, Colin Law
<clan...-gM/Ye1E23mwN+BqQ9rBEUg@public.gmane.org> wrote:
>
> >> > 2009/10/2 adedip
<ade...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>:
>
> >> > > or maybe I''m wrong about the type...
> >> > > here''s the form code in the view:
>
> >> > > <% @source = Article.find(:all) %>
> >> > > From:
> >> > > <%= collection_select("from",
"date", @source, "date","date",
> >> > > {:include_blank => true}) %>
> >> > > To:
> >> > > <%= collection_select("to",
"date", @source, "date","date",
> >> > > {:include_blank => true}) %>
>
> >> > If you are using params[:from] then it is a string. params[]
are
> >> > always strings. The fact that you keep saying it is in
yyyy-mm-dd
> >> > format does not make sense if it is a Date as a Date is not
in a
> >> > particular format till you convert it to string for display
for
> >> > example.
>
> >> that''s what I''ve realized few minutes ago :P
>
> >> so
> >> params[:from].to_date
> >> would do the job?!
> > or maybe
>
> > from = Date.new(params[:from])
>
> Check the docs for Date. You might have to use strptime or one of the
> other constructor methods. Make sure you catch exceptions if
> converting from user typed input.
>
> Colin
On 02 Oct 2009, at 19:03, adedip wrote:> @articles = Article.find(:all, :order => :date, :conditions => > {:author => aut, :date.year => tf.year..tt.year})@articles = Article.find(:all, :order => :date, :conditions => ["(YEAR(date) BETWEEN ? AND ?) AND author_id=?",tf.year, tt.year, aut.id]) Just out of the top of my head. Best regards Peter De Berdt --~--~---------~--~----~------------~-------~--~----~ 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 3 Ott, 10:50, Peter De Berdt <peter.de.be...-LPO8gxj9N8aZIoH1IeqzKA@public.gmane.org> wrote:> On 02 Oct 2009, at 19:03, adedip wrote: > > > @articles = Article.find(:all, :order => :date, :conditions => > > {:author => aut, :date.year => tf.year..tt.year}) > > @articles = Article.find(:all, :order => :date, :conditions => > ["(YEAR(date) BETWEEN ? AND ?) AND author_id=?",tf.year, tt.year, > aut.id]) > > Just out of the top of my head. > > Best regards > > Peter De BerdtOccam is always right... the easiest solution if often the best one :D (parenthesys work) morover.. when I use Article.scope(:all, .... it still behave like an array.. Instead using Article.scoped_by it seems to work better.. but I guess I can''t do something like this: proxy = Article.scoped_by_author_and_date(aut,"YEAR(date) BETWEEN ? AND ?",tf.year,tt,year) @articles = proxy.find_by_solr(params[:query]).results in the server log I get this: Article Load (1.2ms) SELECT * FROM `articles` WHERE (articles.id in (1)) AND (`articles`.`date` = ''date BETWEEN ? AND ?'' AND `articles`.`author` = ''No Author'') the question marks comes out :S I''m hopeless :D
On 03 Oct 2009, at 14:39, adedip wrote:> morover.. > when I use Article.scope(:all, .... > it still behave like an array.. > Instead using Article.scoped_by it seems to work better.. but I guess > I can''t do something like this: > > proxy = Article.scoped_by_author_and_date(aut,"YEAR(date) BETWEEN ? > AND ?",tf.year,tt,year) > @articles = proxy.find_by_solr(params[:query]).results > > in the server log I get this: > Article Load (1.2ms) SELECT * FROM `articles` WHERE (articles.id > in (1)) AND (`articles`.`date` = ''date BETWEEN ? AND ?'' AND > `articles`.`author` = ''No Author'') > > the question marks comes out :SYou didn''t put the conditions part into square brackets, that could be the reason. Best regards Peter De Berdt --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---