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
___________________________________
imijsoft.com
ianmjones.net (blog)