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
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.
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
Max Williams 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,
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 Jones
Matt Jones 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 Jones
On Oct 12, 11:18�am, Max Williams <rails-mailing-l...@andreas-s.net>
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"} }