Help with a query - no comments in 90 days

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

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.

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

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

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.

Hehe :slight_smile:

And then watch as all your work colleagues back slowly out of the room :wink:

j/k

Julian.

Learn Ruby on Rails! Check out the FREE VIDS (for a limited time) VIDEO #3 out NOW!
http://sensei.zenunit.com/

http://tinyurl.com/342buv

LOL no, because we're all sitting at our desks, pronouncing our code
out loud, silly :slight_smile:

Julian

Learn Ruby on Rails! Check out the FREE VIDS (for a limited time)
VIDEO #3 out NOW!
http://sensei.zenunit.com/

Hello, Rob and Jonzo…

Thanks for your help…

Worked fine…