# Help with a query - no comments in 90 days

**URL:** <https://www.ruby-forum.com/t/help-with-a-query-no-comments-in-90-days/134320>\
**Category:** Rails\
**Created:** [April 9, 2008, 8:38pm UTC](https://www.ruby-forum.com/t/help-with-a-query-no-comments-in-90-days/134320 "2008-04-09T20:38:31Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![Marcello\_P](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/fbc32d/32.png) [@Marcello\_P](https://www.ruby-forum.com/u/Marcello_P)\
**Post date:** [April 9, 2008, 8:38pm UTC](https://www.ruby-forum.com/t/help-with-a-query-no-comments-in-90-days/134320/1 "2008-04-09T20:38:31Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Marcello\_P](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/fbc32d/32.png) [@Marcello\_P](https://www.ruby-forum.com/u/Marcello_P)\
**Post date:** [April 9, 2008, 8:40pm UTC](https://www.ruby-forum.com/t/help-with-a-query-no-comments-in-90-days/134320/2 "2008-04-09T20:40:52Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Marcello\_P](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/fbc32d/32.png) [@Marcello\_P](https://www.ruby-forum.com/u/Marcello_P)\
**Post date:** [April 9, 2008, 8:53pm UTC](https://www.ruby-forum.com/t/help-with-a-query-no-comments-in-90-days/134320/3 "2008-04-09T20:53:47Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Marcello\_P](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/fbc32d/32.png) [@Marcello\_P](https://www.ruby-forum.com/u/Marcello_P)\
**Post date:** [April 9, 2008, 10:03pm UTC](https://www.ruby-forum.com/t/help-with-a-query-no-comments-in-90-days/134320/4 "2008-04-09T22:03:00Z")

</div>

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](http://agileconsultingllc.com)  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![Marcello\_P](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/fbc32d/32.png) [@Marcello\_P](https://www.ruby-forum.com/u/Marcello_P)\
**Post date:** [April 9, 2008, 10:10pm UTC](https://www.ruby-forum.com/t/help-with-a-query-no-comments-in-90-days/134320/5 "2008-04-09T22:10:39Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Marcello\_P](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/fbc32d/32.png) [@Marcello\_P](https://www.ruby-forum.com/u/Marcello_P)\
**Post date:** [April 10, 2008, 7:47am UTC](https://www.ruby-forum.com/t/help-with-a-query-no-comments-in-90-days/134320/6 "2008-04-10T07:47:59Z")

</div>

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

[http://tinyurl.com/342buv](http://tinyurl.com/342buv)

---

<div class="post-metadata">

**Author:** ![Marcello\_P](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/fbc32d/32.png) [@Marcello\_P](https://www.ruby-forum.com/u/Marcello_P)\
**Post date:** [April 10, 2008, 7:32am UTC](https://www.ruby-forum.com/t/help-with-a-query-no-comments-in-90-days/134320/7 "2008-04-10T07:32:47Z")

</div>

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/](http://sensei.zenunit.com/)

---

<div class="post-metadata">

**Author:** ![Marcello\_P](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/fbc32d/32.png) [@Marcello\_P](https://www.ruby-forum.com/u/Marcello_P)\
**Post date:** [April 11, 2008, 5:54pm UTC](https://www.ruby-forum.com/t/help-with-a-query-no-comments-in-90-days/134320/8 "2008-04-11T17:54:10Z")

</div>

Hello, Rob and Jonzo…  
Thanks for your help…

Worked fine…

---

<div class="post-metadata">

**Author:** ![Marcello\_P](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/fbc32d/32.png) [@Marcello\_P](https://www.ruby-forum.com/u/Marcello_P)\
**Post date:** [April 10, 2008, 8:27am UTC](https://www.ruby-forum.com/t/help-with-a-query-no-comments-in-90-days/134320/9 "2008-04-10T08:27:51Z")

</div>

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/](http://sensei.zenunit.com/)