Max Williams
2009-Oct-12 13:02 UTC
named scope for ''all without conditional associations''
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 -- Posted via ruby-forum.com.
Leonardo Mateo
2009-Oct-12 13:31 UTC
Re: named scope for ''all without conditional associations''
On Mon, Oct 12, 2009 at 3:02 PM, Max Williams <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> > 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. >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 Mateo. There''s no place like ~
Max Williams
2009-Oct-12 13:42 UTC
Re: named scope for ''all without conditional associations''
Thanks, but why would i need an intermediate table? the users table is already linked to sign_ins via sign_ins.user_id. -- Posted via ruby-forum.com.
Leonardo Mateo
2009-Oct-12 13:45 UTC
Re: named scope for ''all without conditional associations''
On Mon, Oct 12, 2009 at 3:42 PM, Max Williams <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> 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 Mateo. There''s no place like ~
Colin Law
2009-Oct-12 14:52 UTC
Re: named scope for ''all without conditional associations''
2009/10/12 Leonardo Mateo <leonardomateo-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>:> > On Mon, Oct 12, 2009 at 3:42 PM, Max Williams > <rails-mailing-list-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> 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
Max Williams
2009-Oct-12 15:18 UTC
Re: named scope for ''all without conditional associations''
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"} } -- Posted via ruby-forum.com.
Marnen Laibow-Koser
2009-Oct-12 15:36 UTC
Re: named scope for ''all without conditional associations''
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, -- Marnen Laibow-Koser marnen.org marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via ruby-forum.com.
Matt Jones
2009-Oct-13 18:57 UTC
Re: named scope for ''all without conditional associations''
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...-ARtvInVfO7ksV2N9l4h3zg@public.gmane.org> wrote:> 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"} } > -- > Posted viahttp://www.ruby-forum.com.
Max Williams
2009-Oct-13 20:54 UTC
Re: named scope for ''all without conditional associations''
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"} } -- Posted via ruby-forum.com.