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

**URL:** <https://discuss.rubyonrails.org/t/has-many-with-finder-sql-doesnt-actually-give-out-data/17286>\
**Category:** rubyonrails-talk\
**Created:** [August 22, 2007, 9:31am UTC](https://discuss.rubyonrails.org/t/has-many-with-finder-sql-doesnt-actually-give-out-data/17286 "2007-08-22T09:31:48Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Morgan\_Grubb](https://avatars.discourse-cdn.com/v4/letter/m/51bf81/32.png) [@Morgan\_Grubb](https://discuss.rubyonrails.org/u/Morgan_Grubb)\
**Post date:** [August 22, 2007, 9:31am UTC](https://discuss.rubyonrails.org/t/has-many-with-finder-sql-doesnt-actually-give-out-data/17286/1 "2007-08-22T09:31:48Z")

</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 = ‘morgan.grubb@justlanded.com’ ))

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 = ‘morgan.grubb@justlanded.com’ ))

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 Grubb.

---

<div class="post-metadata">

**Author:** ![Bill\_Kocik](https://avatars.discourse-cdn.com/v4/letter/b/54ee81/32.png) [@Bill\_Kocik](https://discuss.rubyonrails.org/u/Bill_Kocik)\
**Post date:** [August 22, 2007, 2:43pm UTC](https://discuss.rubyonrails.org/t/has-many-with-finder-sql-doesnt-actually-give-out-data/17286/2 "2007-08-22T14:43:19Z")

</div>

Morgan Grubb 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.