# Has\_many with :finder\_sql doesn't actually give out data?

**URL:** <https://www.ruby-forum.com/t/has-many-with-finder-sql-doesnt-actually-give-out-data/112703>\
**Category:** Rails\
**Created:** [August 22, 2007, 11:32am UTC](https://www.ruby-forum.com/t/has-many-with-finder-sql-doesnt-actually-give-out-data/112703 "2007-08-22T11:32:28Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Morgan\_G](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/57b2e6/32.png) [@Morgan\_G](https://www.ruby-forum.com/u/Morgan_G)\
**Post date:** [August 22, 2007, 11:32am UTC](https://www.ruby-forum.com/t/has-many-with-finder-sql-doesnt-actually-give-out-data/112703/1 "2007-08-22T11:32:28Z")

</div>

Hi,

I have a model unrelated to a table, and I want to assign three has\_many  
child relationships.

ie.,

```
has_many :classifieds, :conditions => %q/ email = '#{email}' /
#=> SELECT * FROM classifieds WHERE (classifieds.white_label_id = 

```

NULL  
AND ( email = ‘removed\_email\_address@domain.invalid’ ))

But that assumes that I´ll find the name of the model as a foreign key  
field  
in the jobs table (which I won’t). I want to use the email as the  
foriegn  
key so I tried (setting model.email=email before calling for the  
children)

```
attr_accessor :email

has_many :classifieds, :foreign_key => :email

```

And requesting with

```
model.classifieds.find :all

```

But the resulting SQL is

SELECT \* FROM classifieds WHERE (classifieds.email = NULL)

So I try setting the email address as a condition

```
has_many :classifieds, :foreign_key => :email, :conditions => %q/ 

```

email  
= ‘#{email}’ /  
#=\> SELECT \* FROM classifieds WHERE (classifieds.email = NULL AND (  
email = ‘removed\_email\_address@domain.invalid’ ))

Not much use there either. I tried setting foriegn\_key to false and nil  
and  
nothing seemed to get rid of it from the query, and I can´t find any  
documentation on getting rid of it, so I decided to try a different  
approach: :finder\_sql

```
has_many :classifieds, :finder_sql => %q/
    SELECT DISTINCT i.*
    FROM classifieds i
    WHERE i.email = '#{email}'
/

```

Which is interesting. Because now

```
model.classifieds #=> []
model.classifieds.count #=> 8
model.classifieds.find :all #=> nil
model.classifieds.find 38 #=> nil (where 38 is the id of an item 

```

that  
exists)

Very long story short, the only way I can get all of my data out of  
:finder\_sql is to use .find\_all email but since :find\_all is deprecated,  
I  
don’t think that’s a good idea.

Please can someone point out to me where I’m going so far wrong with  
this  
approach?

Cheers,  
Morgan G…

---

<div class="post-metadata">

**Author:** ![Morgan\_G](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/57b2e6/32.png) [@Morgan\_G](https://www.ruby-forum.com/u/Morgan_G)\
**Post date:** [August 22, 2007, 4:44pm UTC](https://www.ruby-forum.com/t/has-many-with-finder-sql-doesnt-actually-give-out-data/112703/2 "2007-08-22T16:44:34Z")

</div>

Morgan G. wrote:

> Very long story short, the only way I can get all of my data out of  
> :finder\_sql is to use .find\_all email but since :find\_all is deprecated, I  
> don’t think that’s a good idea.

find\_all is deprecated because it was replaced with find(:all). But  
that’s probably still not the best approach.

To be honest I’m having a bit of trouble figuring out exactly what  
you’re trying to do, and how to go about it. If you could paste the  
code for the models involved (not all of it, just enough to show the  
relationships) and their migrations, maybe I or someone else can help  
you out a bit more.

I’m also curious what the SQL calls look like that produces each of  
the strange results (nil array, count of 8, etc.) you mention.