Help with a query - no comments in 90 days

Rails
1

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

2

Marcello P. 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

3

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

4

On Apr 9, 2008, at 2:52 PM, Marcello P. wrote:

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 B. http://agileconsultingllc.com
[email protected]

5

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.

6

From my query? or from Rob’s? Or because I look funny?

http://tinyurl.com/342buv

7

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/

8

Hello, Rob and Jonzo…
Thanks for your help…

Worked fine…

9

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/