# Named scope for 'all without conditional associations' **URL:** \ **Category:** Rails\ **Created:** [October 12, 2009, 3:02pm UTC](https://www.ruby-forum.com/t/named-scope-for-all-without-conditional-associations/177151 "2009-10-12T15:02:41Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![cmaxvv](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/85f322/32.png) [@cmaxvv](https://www.ruby-forum.com/u/cmaxvv)\ **Post date:** [October 12, 2009, 3:02pm UTC](https://www.ruby-forum.com/t/named-scope-for-all-without-conditional-associations/177151/1 "2009-10-12T15:02:41Z")
That title doesn’t explain it very well… Basically a User has many sign\_ins, and sign\_ins have a created\_at field. I want to do a named scope on User which returns all users who have no sign\_ins in the last two months (ie have no associated sign\_in records where created\_at \>= 2.months.ago). This feels like it should be simple but i can’t get it. thanks max ---
**Author:** ![cmaxvv](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/85f322/32.png) [@cmaxvv](https://www.ruby-forum.com/u/cmaxvv)\ **Post date:** [October 12, 2009, 3:32pm UTC](https://www.ruby-forum.com/t/named-scope-for-all-without-conditional-associations/177151/2 "2009-10-12T15:32:45Z")
On Mon, Oct 12, 2009 at 3:02 PM, Max W. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > If you want to make a named\_scope, I guess you will need joins with the sign\_ins table. something like “INNER JOIN intermediate\_table ON intermediate\_table.user\_id = users.id INNER JOIN sign\_ins ON sign\_ins.id = intermediate\_table.sign\_in\_id WHERE DATETIME(sign\_is.created\_at) \> DATETIME(#{named\_scope\_parameter})” (this is just a quick first approach, you probaly have to re-think it and adapt it to your database). Other approach can be create a named scope in the sign\_ins class, to retrieve all the user ids that have no signs in, and then create a named scope or a method on User to filter the results based on these ids. Hope it helps. – Leonardo M… There’s no place like ~ ---
**Author:** ![cmaxvv](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/85f322/32.png) [@cmaxvv](https://www.ruby-forum.com/u/cmaxvv)\ **Post date:** [October 12, 2009, 3:42pm UTC](https://www.ruby-forum.com/t/named-scope-for-all-without-conditional-associations/177151/3 "2009-10-12T15:42:16Z")
Thanks, but why would i need an intermediate table? the users table is already linked to sign\_ins via sign\_ins.user\_id. ---
**Author:** ![cmaxvv](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/85f322/32.png) [@cmaxvv](https://www.ruby-forum.com/u/cmaxvv)\ **Post date:** [October 12, 2009, 4:54pm UTC](https://www.ruby-forum.com/t/named-scope-for-all-without-conditional-associations/177151/4 "2009-10-12T16:54:43Z")
2009/10/12 Leonardo M. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid): > On Mon, Oct 12, 2009 at 3:42 PM, Max W. > [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > > > Thanks, but why would i need an intermediate table? Â the users table is > > already linked to sign\_ins via sign\_ins.user\_id. > > Then it should be simpler, you would only need one INNER JOIN. You should not need any joins, Rails should do all that for you from the associations. First I would forget about the fact that you want a named scope and work out the find that gives the required records. Have you done that? Then convert it to a named scope. Colin ---
**Author:** ![cmaxvv](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/85f322/32.png) [@cmaxvv](https://www.ruby-forum.com/u/cmaxvv)\ **Post date:** [October 12, 2009, 5:18pm UTC](https://www.ruby-forum.com/t/named-scope-for-all-without-conditional-associations/177151/5 "2009-10-12T17:18:48Z")
I can see that my hopes of someone just telling me the answer were a bit optimistic. 🙂 Ok, after a bit of help from a friend i have the sql - it works on the basis of doing a left join with sign\_ins (with the conditions on the join) and then getting back the rows where we have nothing on the right hand side (ie no matching sign ins). select \* from users left join sign\_ins on (users.id = sign\_ins.user\_id and sign\_ins.created\_at \> ‘2009-08-12 00:00:00’) where sign\_ins.id is null; next step, turn this into a named scope. I can manage this, and it seems to work, but is a bit ugly. Is there a nicer way? eg avoiding the string evaluation stuff for the date. named\_scope :no\_login\_for\_last\_two\_months, lambda { { :joins =\> “left join sign\_ins on (users.id = sign\_ins.user\_id and sign\_ins.created\_at \>= ‘#{2.months.ago.to\_s(:db)}’)”, :conditions =\> “sign\_ins.id is null”} } ---
**Author:** ![cmaxvv](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/85f322/32.png) [@cmaxvv](https://www.ruby-forum.com/u/cmaxvv)\ **Post date:** [October 12, 2009, 4:31pm UTC](https://www.ruby-forum.com/t/named-scope-for-all-without-conditional-associations/177151/6 "2009-10-12T16:31:18Z")
On Mon, Oct 12, 2009 at 3:42 PM, Max W. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote: > Thanks, but why would i need an intermediate table? the users table is > already linked to sign\_ins via sign\_ins.user\_id. Then it should be simpler, you would only need one INNER JOIN. – Leonardo M… There’s no place like ~ ---
**Author:** ![cmaxvv](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/85f322/32.png) [@cmaxvv](https://www.ruby-forum.com/u/cmaxvv)\ **Post date:** [October 12, 2009, 5:36pm UTC](https://www.ruby-forum.com/t/named-scope-for-all-without-conditional-associations/177151/7 "2009-10-12T17:36:44Z")
Max W. wrote: > I can see that my hopes of someone just telling me the answer were a bit > optimistic. 🙂 Yeah. We’re here to help, not to spoon-feed. 🙂 > Ok, after a bit of help from a friend i have the sql - it works on the > basis of doing a left join with sign\_ins (with the conditions on the > join) and then getting back the rows where we have nothing on the right > hand side (ie no matching sign ins). Why go to all that trouble? You could sort User.sign\_ins by date and check the date on the most recent one. ## Best, Marnen Laibow-Koser [http://www.marnen.org](http://www.marnen.org) removed\_email\_address@domain.invalid ---
**Author:** ![cmaxvv](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/85f322/32.png) [@cmaxvv](https://www.ruby-forum.com/u/cmaxvv)\ **Post date:** [October 13, 2009, 8:58pm UTC](https://www.ruby-forum.com/t/named-scope-for-all-without-conditional-associations/177151/8 "2009-10-13T20:58:59Z")
At the risk of sounding dense, is there a reason you can’t cache the most recent signon date in the User object itself? Several of the auth plugins will even handle this automatically… Consider it an equivalent to the counter\_cache option for an association; the complicated left join stuff is still needed to answer hard questions (for instance, find all users logged in more than twice in the last week, etc) but this case seems to be common enough that caching the results is a good idea… –Matt J. On Oct 12, 11:18 am, Max W. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) ---
**Author:** ![cmaxvv](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/85f322/32.png) [@cmaxvv](https://www.ruby-forum.com/u/cmaxvv)\ **Post date:** [October 13, 2009, 10:54pm UTC](https://www.ruby-forum.com/t/named-scope-for-all-without-conditional-associations/177151/9 "2009-10-13T22:54:15Z")
Matt J. wrote: > At the risk of sounding dense, is there a reason you can’t cache the > most recent signon date in the User object itself? Several of the auth > plugins will even handle this automatically… > > Consider it an equivalent to the counter\_cache option for an > association; the complicated left join stuff is still needed to answer > hard questions (for instance, find all users logged in more than twice > in the last week, etc) but this case seems to be common enough that > caching the results is a good idea… > > –Matt J. > > On Oct 12, 11:18�am, Max W. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) Hi Matt I already do this as it happens, and it’s been adequate till now. Now though i have a requirement to get (eg) all users who have logged in twice or more in the last month. I ended up doing the ‘haven’t logged in within the last two months’ query like this: named\_scope :no\_login\_in\_last\_two\_months, lambda { { :joins =\> “LEFT JOIN sign\_ins ON (users.id = sign\_ins.user\_id AND sign\_ins.created\_at \>= ‘#{2.months.ago.to\_s(:db)}’)”, :conditions =\> “sign\_ins.id IS NULL”} }