Forum: Ruby on Rails :conditions on has_one realationship

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
91a5a39129309428a6f634e7551c1314?d=identicon&s=25 Anthony Ramm (Guest)
on 2006-05-16 13:37
(Received via mailing list)
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
C01e9386e12c615ebf53cb6fad66dbec?d=identicon&s=25 Ian M. Jones (Guest)
on 2006-05-16 14:45
(Received via mailing list)
On 16 May 2006, at 12:35, Anthony Ramm 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)
This topic is locked and can not be replied to.