Forum: Ruby on Rails Trouble combining :has_many, :finder_sql and :conditions to

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.
Reginald Braithwaite (Guest)
on 2005-12-27 03:06
(Received via mailing list)
I'm sure there's something right under my nose that I'm missing. I
have two tables with two parallel one-to-many relationships. I wish to
use the :finder_sql parameter to essentially 'or' the two foreign
keys.

What isn't working for me is performing a 'sub-search'.

Let's say the tables are "stores" and "people," and the relationships
are called "works_for" and "shops_at."

I want to have a single "has_many" relationship that encompasses the
people who visit a store. Something like:

class Store  < ActiveRecord::Base
    has_many    :people,
                :dependent  =>  true,
                :finder_sql => 'SELECT DISTINCT p.*
                                FROM    people p
                                WHERE       (p.works_for_id = #{id} OR
 p.shops_at_id = #{id})',
                :order      => "updated_on DESC"
end

Meanwhile people is an STI:

class Person  < ActiveRecord::Base
end

class Employee < Person
    belongs_to  :store,
                :foreign_key => 'works_for_id'
end

class Shopper < Person
    belongs_to  :store,
                :foreign_key => 'shops_at_id'
end

This all seems to work for the common cases. For example, given an
instance of Store called macys, I can get its visitors with
macys.people.

However, this all goes badly when I try to perform a sub-search. What
I want are all of macy's people that have been recently updated.
Something like:

macys.people.find_all :conditions => ['updated_on >= ?',
1.fortnights.ago]

What I get is all of the people at macys. It seems to ignore the
conditions.

I've tried :

macys.people.find :all, :conditions => ['updated_on >= ?',
1.fortnights.ago]

And I get an exception inside of association_proxy.rb

Anyways, that's very long-winded. If we snip it all off, what I want
is to be able to add conditions to a relationship that has its own
:finder_sql.

Is there something else I should be doing?

Thanks in advance!

--
Reginald Braithwaite
"Our show may not be fancy, but it's noisy and it's free."
http://www.braithwaite-lee.com/weblog/

Like all text messages, email exchanged with a gmail account may be
stored indefinitely and/or read by third parties without the sender or
receiver's knowledge or permission. Please do not send any privileged
or confidential transmission to this account: enquire about secure
alternatives.
Ed C. (Guest)
on 2005-12-27 16:27
(Received via mailing list)
Reg,

I think the problem lies in your finder_sql string -- its using
single-quotes instead of double-quotes, so the #{} isn't getting
escaped.

:finder_sql => "SELECT DISTINCT p.*
                               FROM    people p
                               WHERE       (p.works_for_id = #{id} OR
 p.shops_at_id = #{id})"

(Ruby treats a string in single-quotes literally.)

Hope that helps,
Best Regards,
EJC
Rick O. (Guest)
on 2005-12-27 17:09
(Received via mailing list)
On 12/27/05, Ed C. <removed_email_address@domain.invalid> wrote:
>
> (Ruby treats a string in single-quotes literally.)

Actually, he's correct.  When you define the finder_sql in the model
class, you have no clue what id will be.  It's defined in single
quotes so that #{id} gets parsed at query time with the current id
value.

As for the original question, I believe having a defined finder_sql
forces it to ignore the other attributes such as order and conditions.
 It looks like you're using two shop_id foreign keys in the people
table for duplicate purpose with STI.  Why not simplify the schema a
bit?

class Store  < ActiveRecord::Base
   has_many    :people, :order      => "updated_on DESC"
end

class Person  < ActiveRecord::Base
  belongs_to  :store
end

class Employee < Person
end

class Shopper < Person
end

--
rick
http://techno-weenie.net
Reginald Braithwaite (Guest)
on 2005-12-27 17:53
(Received via mailing list)
On 12/27/05, Rick O. <removed_email_address@domain.invalid> wrote:
> On 12/27/05, Ed C. <removed_email_address@domain.invalid> wrote:

> As for the original question, I believe having a defined finder_sql
> forces it to ignore the other attributes such as order and conditions.

After examining the source for a while, I'm worried this may be the
case.

>  It looks like you're using two shop_id foreign keys in the people
> table for duplicate purpose with STI.  Why not simplify the schema a
> bit?

Because... drum roll... some people shop in one store and work in
another. Well, my tables aren't really shops and people. But that's
the posish.

Thansk for the hint!

--
Reginald Braithwaite
"Our show may not be fancy, but it's noisy and it's free."
http://www.braithwaite-lee.com/weblog/

Like all text messages, email exchanged with a gmail account may be
stored indefinitely and/or read by third parties without the sender or
receiver's knowledge or permission. Please do not send any privileged
or confidential transmission to this account: enquire about secure
alternatives.
This topic is locked and can not be replied to.