Hello all, I tried to get this work for two days... by did not make it right.... so I decided to call for help.... I have users and comments, like this: # User has_many :comments # Comment belongs_to :user Each comment has a datetime field. I need to get all users that has no comment in last 90 days (or no comment at all)... I tried: User.find(:all, :conditions => ["comments.day > ? and comments.id IS NULL", 30.days.ago]) But have got an empty set. In the other hand, User.find(:all, :conditions => "comments.id IS NULL") gives correctly all users without comments... The problem is when I try to use the date.... Thanks for any help... Marcello --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Marcello Parra wrote:> I need to get all users that has no comment in last 90 days (or no > comment at all)...> User.find(:all, :conditions => ["comments.day > ? and comments.id > IS NULL", 30.days.ago])Compare your verbal sentence to your ActiveRecord command. One uses "or" and the other uses "and"! Tip: Always pronounce your code out-loud, and always name your code after what you would say, out-loud, when describing a situation. This helps catch logical errors. -- Phlip --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Hello Phlip, Problem is that none of this possibilities solved my problem: User.find(:all, :conditions => ["comments.day > ? or comments.id IS NULL", 30.days.ago]) User.find(:all, :conditions => ["comments.day > ? and comments.id IS NULL", 30.days.ago]) etc... No idea what I''m doing wrong... Thanks On Wed, Apr 9, 2008 at 3:35 PM, Phlip <phlip2005-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > Marcello Parra wrote: > > > I need to get all users that has no comment in last 90 days (or no > > comment at all)... > > > User.find(:all, :conditions => ["comments.day > ? and comments.id > > IS NULL", 30.days.ago]) > > Compare your verbal sentence to your ActiveRecord command. One uses "or" > and the > other uses "and"! > > Tip: Always pronounce your code out-loud, and always name your code after > what > you would say, out-loud, when describing a situation. This helps catch > logical > errors. > > -- > Phlip >--~--~---------~--~----~------------~-------~--~----~ 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 Apr 9, 2008, at 2:52 PM, Marcello Parra wrote:> > Hello Phlip, > > Problem is that none of this possibilities solved my problem: > > User.find(:all, :conditions => ["comments.day > ? or comments.id IS > NULL", 30.days.ago]) > User.find(:all, :conditions => ["comments.day > ? and comments.id > IS NULL", 30.days.ago]) > etc... > > No idea what I''m doing wrong... > > ThanksAt the very least, you need to know what''s going on at the SQL level. Here''s how I''d construct a suitable query in pure SQL: select users.id from users left outer join comments on comments.user_id = users.id where comments.day > DATE_SUB(CURDATE(),INTERVAL 30 DAY) group by users.id having max(comments.day) IS NULL Your ActiveRecord conditions refer to comments, but I suspect that table isn''t even called out in the query. You want to ask a "negative" question: Users that DON''T have a comments within 30 days. Perhaps turn that around. All users except those that DO have a comment within 30 days. Now, why is that easier? All users: all_user_ids = User.find(:all, :select => :id).map{|u|u.id} Users with recent comment: users_with_comments = User.find(:all, :include => :comments, :conditions => [''comments.day > ?'', 30.days.ago]).map{|u| u.id} no_recent_comment_users = User.find(all_user_ids - users_with_comments) Alternatively, no_recent_comment_users = User.find(:all, :conditions => [''id NOT IN (?)'', users_with_comments]) Note that the second form didn''t get all the ids first. -Rob Rob Biedenharn http://agileconsultingllc.com Rob-xa9cJyRlE0mWcWVYNo9pwxS2lgjeYSpx@public.gmane.org> On Wed, Apr 9, 2008 at 3:35 PM, Phlip <phlip2005-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > Marcello Parra wrote: > > > I need to get all users that has no comment in last 90 days (or no > > comment at all)... > > > User.find(:all, :conditions => ["comments.day > ? and comments.id > > IS NULL", 30.days.ago]) > > Compare your verbal sentence to your ActiveRecord command. One uses > "or" and the > other uses "and"! > > Tip: Always pronounce your code out-loud, and always name your code > after what > you would say, out-loud, when describing a situation. This helps > catch logical > errors. > > -- > Phlip--~--~---------~--~----~------------~-------~--~----~ 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 would revert back to sql for this one: * you''re gunna need to do some testing of this query! as I''ve done none! User.find_by_sql( ["SELECT * FROM users WHERE users.id NOT IN (SELECT u.id FROM users AS u, comments WHERE u.id = comments.user_id AND comments.day < ?)", 30.days.ago]) there is 2 parts to this query - the first part is selecting all the users with comments more recent than 30 days - then the second part is taking all the users, and subtracting the first group that you don''t want from it. Having said that, there might be a better way of doing it that''s pure rails because writing sql isn''t that flexible! Thanks, Jonzo. On Apr 10, 6:52 am, "Marcello Parra" <parro...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Hello Phlip, > Problem is that none of this possibilities solved my problem: > > User.find(:all, :conditions => ["comments.day > ? or comments.id IS NULL", > 30.days.ago]) > User.find(:all, :conditions => ["comments.day > ? and comments.id IS NULL", > 30.days.ago]) > etc... > > No idea what I''m doing wrong... > > Thanks > > On Wed, Apr 9, 2008 at 3:35 PM, Phlip <phlip2...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote: > > > Marcello Parra wrote: > > > > I need to get all users that has no comment in last 90 days (or no > > > comment at all)... > > > > User.find(:all, :conditions => ["comments.day > ? and comments.id > > > IS NULL", 30.days.ago]) > > > Compare your verbal sentence to your ActiveRecord command. One uses "or" > > and the > > other uses "and"! > > > Tip: Always pronounce your code out-loud, and always name your code after > > what > > you would say, out-loud, when describing a situation. This helps catch > > logical > > errors. > > > -- > > Phlip--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Hehe :-) And then watch as all your work colleagues back slowly out of the room ;-) j/k Julian. Learn Ruby on Rails! Check out the FREE VIDS (for a limited time) VIDEO #3 out NOW! http://sensei.zenunit.com/ On 10/04/2008, at 4:35 AM, Phlip wrote:> > Tip: Always pronounce your code out-loud, and always name your code > after what > you would say, out-loud, when describing a situation. This helps > catch logical > errors. > > -- > Phlip--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
From my query? or from Rob''s? Or because I look funny? http://tinyurl.com/342buv On Apr 10, 5:32 pm, Julian Leviston <jul...-AfxEtdRqmE/tt0EhB6fy4g@public.gmane.org> wrote:> Hehe :-) > > And then watch as all your work colleagues back slowly out of the > room ;-) > > j/k > > Julian. > > Learn Ruby on Rails! Check out the FREE VIDS (for a limited time) > VIDEO #3 out NOW!http://sensei.zenunit.com/ > > On 10/04/2008, at 4:35 AM, Phlip wrote: > > > > > Tip: Always pronounce your code out-loud, and always name your code > > after what > > you would say, out-loud, when describing a situation. This helps > > catch logical > > errors. > > > -- > > Phlip--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
LOL no, because we''re all sitting at our desks, pronouncing our code out loud, silly :) Julian Learn Ruby on Rails! Check out the FREE VIDS (for a limited time) VIDEO #3 out NOW! http://sensei.zenunit.com/ On 10/04/2008, at 3:47 PM, Jonzo wrote:> >> From my query? or from Rob''s? Or because I look funny? > > http://tinyurl.com/342buv > > On Apr 10, 5:32 pm, Julian Leviston <jul...-AfxEtdRqmE/tt0EhB6fy4g@public.gmane.org> wrote: >> Hehe :-) >> >> And then watch as all your work colleagues back slowly out of the >> room ;-) >> >> j/k >> >> Julian. >> >> Learn Ruby on Rails! Check out the FREE VIDS (for a limited time) >> VIDEO #3 out NOW!http://sensei.zenunit.com/ >> >> On 10/04/2008, at 4:35 AM, Phlip wrote: >> >> >> >>> Tip: Always pronounce your code out-loud, and always name your code >>> after what >>> you would say, out-loud, when describing a situation. This helps >>> catch logical >>> errors. >> >>> -- >>> Phlip > >--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
Hello, Rob and Jonzo... Thanks for your help... Worked fine..... On Wed, Apr 9, 2008 at 5:10 PM, Jonzo <j.fantham-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > I would revert back to sql for this one: > > * you''re gunna need to do some testing of this query! as I''ve done > none! > > User.find_by_sql( ["SELECT * FROM users WHERE users.id NOT IN (SELECT > u.id FROM users AS u, comments WHERE u.id = comments.user_id AND > comments.day < ?)", 30.days.ago]) > > there is 2 parts to this query > - the first part is selecting all the users with comments more > recent than 30 days > - then the second part is taking all the users, and subtracting the > first group that you don''t want from it. > > Having said that, there might be a better way of doing it that''s pure > rails because writing sql isn''t that flexible! > > Thanks, > Jonzo. > >--~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---