Rails - May 2006 - :conditions on has_one realationship

Hi there,

I''m currently building a membership application and I want to be able  
to select, not just all the memberships, but the most current  
membership from the database on a per user basis.  I have linked the  
tables as shown:-

class Membership < ActiveRecord::Base
   belongs_to :payment
   belongs_to :person
end

class Person < ActiveRecord::Base
   has_many :memberships
   has_one :active_membership, :class_name => ''Membership'',
:order =>
''enddate DESC'', :conditions => ''memberships.id is
not null and
memberships.enddate > now()''
end

As I understand it I should be able to do a
     @people = Person.find :all, :include => :active_membership
in my controller and it should find any records from the table people  
that have an active membership in table memberships.  As far as I can  
tell this should generate this SQL:-
     SELECT people.`id` AS t0_r0, people.`firstname` AS t0_r1,  
people.`surname` AS t0_r2, memberships.`id` AS t1_r0,  
memberships.`person_id` AS t1_r1, memberships.`startdate` AS t1_r3,  
memberships.`enddate` AS t1_r4 FROM people LEFT OUTER JOIN  
memberships ON memberships.person_id = people.id WHERE memberships.id  
is not null and memberships.enddate > now() ORDER BY enddate DESC

However the SQL that is generated does not appear to be correct.  The  
SQL that is generated is:-
     SELECT people.`id` AS t0_r0, people.`firstname` AS t0_r1,  
people.`surname` AS t0_r2, memberships.`id` AS t1_r0,  
memberships.`person_id` AS t1_r1, memberships.`startdate` AS t1_r3,  
memberships.`enddate` AS t1_r4 FROM people LEFT OUTER JOIN  
memberships ON memberships.person_id = people.id AND memberships.id  
is not null and memberships.enddate > now()  ORDER BY enddate DESC
It would appear that the WHERE clause is being left off the end, with  
just an AND instead.

Also, it seems that the has_one association should have a :joins  
parameter so I could use an INNER JOIN instead of the LEFT OUTER JOIN  
in the above and remove the need for the ''memberships.id is not
null''
clause in the SQL.  or perhaps my understanding is not correct.

Any help would be gratefully received.

Thanks

Ant

On 16 May 2006, at 12:35, Anthony Ramm wrote:
> Hi there,
>
> I''m currently building a membership application and I want to be  
> able to select, not just all the memberships, but the most current  
> membership from the database on a per user basis.  I have linked  
> the tables as shown:-
>
> class Membership < ActiveRecord::Base
>   belongs_to :payment
>   belongs_to :person
> end
>
> class Person < ActiveRecord::Base
>   has_many :memberships
>   has_one :active_membership, :class_name =>
''Membership'', :order
> => ''enddate DESC'', :conditions =>
''memberships.id is not null and
> memberships.enddate > now()''
> end
>
> As I understand it I should be able to do a
>     @people = Person.find :all, :include => :active_membership
> in my controller and it should find any records from the table  
> people that have an active membership in table memberships.  As far  
> as I can tell this should generate this SQL:-
>     SELECT people.`id` AS t0_r0, people.`firstname` AS t0_r1,  
> people.`surname` AS t0_r2, memberships.`id` AS t1_r0,  
> memberships.`person_id` AS t1_r1, memberships.`startdate` AS t1_r3,  
> memberships.`enddate` AS t1_r4 FROM people LEFT OUTER JOIN  
> memberships ON memberships.person_id = people.id WHERE  
> memberships.id is not null and memberships.enddate > now() ORDER BY  
> enddate DESC
>
> However the SQL that is generated does not appear to be correct.   
> The SQL that is generated is:-
>     SELECT people.`id` AS t0_r0, people.`firstname` AS t0_r1,  
> people.`surname` AS t0_r2, memberships.`id` AS t1_r0,  
> memberships.`person_id` AS t1_r1, memberships.`startdate` AS t1_r3,  
> memberships.`enddate` AS t1_r4 FROM people LEFT OUTER JOIN  
> memberships ON memberships.person_id = people.id AND memberships.id  
> is not null and memberships.enddate > now()  ORDER BY enddate DESC
> It would appear that the WHERE clause is being left off the end,  
> with just an AND instead.
>
> Also, it seems that the has_one association should have a :joins  
> parameter so I could use an INNER JOIN instead of the LEFT OUTER  
> JOIN in the above and remove the need for the ''memberships.id is  
> not null'' clause in the SQL.  or perhaps my understanding is not  
> correct.
>
> Any help would be gratefully received.
>
> Thanks
>
> Ant
>
Hi Ant,

Using an AND clause as part of the join syntax in SQL is perfectly  
valid, so it should work fine as the clauses each reference the  
joined table (memberships). I wouldn''t worry about it, I think
it''s OK.

I''m a complete newbie when it comes to Rails though, so the rest I  
can''t comment on.

Regards,
--
Ian M. Jones
___________________________________
http://www.imijsoft.com
http://www.ianmjones.net (blog)