Rails - Dec 2005 - Trouble combining :has_many, :finder_sql and :conditions to create a sub-search

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.

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

On 12/26/05, Reginald Braithwaite
<raganwald-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> 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.
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

On 12/27/05, Ed C. <defeated2k4-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> 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.)
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

On 12/27/05, Rick Olson
<technoweenie-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> On 12/27/05, Ed C.
<defeated2k4-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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.