:conditions on has_one realationship


#1

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


#2

On 16 May 2006, at 12:35, Anthony R. wrote:

end
in my controller and it should find any records from the table
However the SQL that is generated does not appear to be correct.
Also, it seems that the has_one association should have a :joins

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)