# Trouble combining :has\_many, :finder\_sql and :conditions to

**URL:** <https://www.ruby-forum.com/t/trouble-combining-has-many-finder-sql-and-conditions-to/51135>\
**Category:** Rails\
**Created:** [December 27, 2005, 2:06am UTC](https://www.ruby-forum.com/t/trouble-combining-has-many-finder-sql-and-conditions-to/51135 "2005-12-27T02:06:39Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Reginald\_Braithwait](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/d2c977/32.png) [@Reginald\_Braithwait](https://www.ruby-forum.com/u/Reginald_Braithwait)\
**Post date:** [December 27, 2005, 2:06am UTC](https://www.ruby-forum.com/t/trouble-combining-has-many-finder-sql-and-conditions-to/51135/1 "2005-12-27T02:06:39Z")

</div>

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/](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.

---

<div class="post-metadata">

**Author:** ![Reginald\_Braithwait](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/d2c977/32.png) [@Reginald\_Braithwait](https://www.ruby-forum.com/u/Reginald_Braithwait)\
**Post date:** [December 27, 2005, 3:27pm UTC](https://www.ruby-forum.com/t/trouble-combining-has-many-finder-sql-and-conditions-to/51135/2 "2005-12-27T15:27:08Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Reginald\_Braithwait](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/d2c977/32.png) [@Reginald\_Braithwait](https://www.ruby-forum.com/u/Reginald_Braithwait)\
**Post date:** [December 27, 2005, 4:09pm UTC](https://www.ruby-forum.com/t/trouble-combining-has-many-finder-sql-and-conditions-to/51135/3 "2005-12-27T16:09:28Z")

</div>

On 12/27/05, Ed C. [removed\_email\_address@domain.invalid](mailto: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](http://techno-weenie.net)

---

<div class="post-metadata">

**Author:** ![Reginald\_Braithwait](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/d2c977/32.png) [@Reginald\_Braithwait](https://www.ruby-forum.com/u/Reginald_Braithwait)\
**Post date:** [December 27, 2005, 4:53pm UTC](https://www.ruby-forum.com/t/trouble-combining-has-many-finder-sql-and-conditions-to/51135/4 "2005-12-27T16:53:28Z")

</div>

On 12/27/05, Rick O. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> On 12/27/05, Ed C. [removed\_email\_address@domain.invalid](mailto: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/](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.