Named scope for 'all without conditional associations'

Rails
1

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

2

On Mon, Oct 12, 2009 at 3:02 PM, Max W.
[email protected] 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 ~

3

Thanks, but why would i need an intermediate table? the users table is
already linked to sign_ins via sign_ins.user_id.

4

2009/10/12 Leonardo M. [email protected]:

On Mon, Oct 12, 2009 at 3:42 PM, Max W.
[email protected] 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

5

I can see that my hopes of someone just telling me the answer were a bit
optimistic. :slight_smile:

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”} }

6

On Mon, Oct 12, 2009 at 3:42 PM, Max W.
[email protected] 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 ~

7

Max W. wrote:

I can see that my hopes of someone just telling me the answer were a bit
optimistic. :slight_smile:

Yeah. We’re here to help, not to spoon-feed. :slight_smile:

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
[email protected]

8

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. [email protected]

9

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. [email protected]

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”} }