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