Rails - Oct 2009 - 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 http://www.ruby-forum.com/.

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 ~

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 http://www.ruby-forum.com/.

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 ~

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

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 http://www.ruby-forum.com/.

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
http://www.marnen.org
marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org
-- 
Posted via http://www.ruby-forum.com/.

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/.

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 http://www.ruby-forum.com/.