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
At 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@AgileConsultingLLC.com
