Trouble combining :has_many, :finder_sql and :conditions to


#1

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.


#2

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


#3

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


#4

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.