# Self-referencing has\_many

**URL:** <https://www.ruby-forum.com/t/self-referencing-has-many/61424>\
**Category:** Rails\
**Created:** [May 23, 2006, 11:40pm UTC](https://www.ruby-forum.com/t/self-referencing-has-many/61424 "2006-05-23T23:40:19Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![spdemac](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/6de8d8/32.png) [@spdemac](https://www.ruby-forum.com/u/spdemac)\
**Post date:** [May 23, 2006, 11:40pm UTC](https://www.ruby-forum.com/t/self-referencing-has-many/61424/1 "2006-05-23T23:40:19Z")

</div>

Having a devil of a time finding records in a self-referencing has\_many  
table relationship.

Everything is working find looking at the has\_many and the belongs\_to  
relationship.

But, when I try to find all “orphans”, records that are neither a parent  
nor a child, I can not find a query that work in ActiveRecord.

This query works in MySQL:

SELECT \* FROM templates  
LEFT JOIN templates t2 ON templates.id=t2.template\_id  
WHERE (t2.id IS NULL AND templates.template\_id IS NULL)

Of course, the table alises through ActiveRecord for a loop when  
manually entered in the :joins options.

Any suggestions?

---

<div class="post-metadata">

**Author:** ![spdemac](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/6de8d8/32.png) [@spdemac](https://www.ruby-forum.com/u/spdemac)\
**Post date:** [May 24, 2006, 12:07am UTC](https://www.ruby-forum.com/t/self-referencing-has-many/61424/2 "2006-05-24T00:07:38Z")

</div>

Lon B. wrote:

> Having a devil of a time finding records in a self-referencing has\_many  
> table relationship.
> 
> Everything is working find looking at the has\_many and the belongs\_to  
> relationship.
> 
> But, when I try to find all “orphans”, records that are neither a parent  
> nor a child, I can not find a query that work in ActiveRecord.
> 
> This query works in MySQL:
> 
> SELECT \* FROM templates  
> LEFT JOIN templates t2 ON templates.id=t2.template\_id  
> WHERE (t2.id IS NULL AND templates.template\_id IS NULL)
> 
> Of course, the table alises through ActiveRecord for a loop when  
> manually entered in the :joins options.
> 
> Any suggestions?

Can you show us your table schemas and model association definitions?

–  
Josh S.  
[http://blog.hasmanythrough.com](http://blog.hasmanythrough.com)

---

<div class="post-metadata">

**Author:** ![spdemac](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/6de8d8/32.png) [@spdemac](https://www.ruby-forum.com/u/spdemac)\
**Post date:** [May 24, 2006, 12:13am UTC](https://www.ruby-forum.com/t/self-referencing-has-many/61424/3 "2006-05-24T00:13:41Z")

</div>

Josh S. wrote:

> Can you show us your table schemas and model association definitions?

Here it it:

class Template \< ActiveRecord::Base  
has\_many :chains, :foreign\_key =\> ‘template\_id’, :class\_name =\>  
‘Template’  
belongs\_to :master, :foreign\_key =\> ‘template\_id’, :class\_name =\>  
‘Template’  
end

CREATE TABLE `templates` (  
`id` int(11) unsigned NOT NULL auto\_increment,  
`site_id` int(11) unsigned NOT NULL default ‘0’,  
`template_id` int(11) default NULL,  
`title` varchar(128) NOT NULL default ‘’,  
`subject` varchar(128) NOT NULL default ‘’,  
`content` text NOT NULL,  
PRIMARY KEY (`id`),  
KEY `site_id` (`site_id`)  
)

---

<div class="post-metadata">

**Author:** ![spdemac](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/6de8d8/32.png) [@spdemac](https://www.ruby-forum.com/u/spdemac)\
**Post date:** [May 24, 2006, 12:19am UTC](https://www.ruby-forum.com/t/self-referencing-has-many/61424/4 "2006-05-24T00:19:52Z")

</div>

have you tried acts\_as\_tree instead?

---

<div class="post-metadata">

**Author:** ![spdemac](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/6de8d8/32.png) [@spdemac](https://www.ruby-forum.com/u/spdemac)\
**Post date:** [May 24, 2006, 1:02am UTC](https://www.ruby-forum.com/t/self-referencing-has-many/61424/5 "2006-05-24T01:02:59Z")

</div>

Found a solution.

Template.find(:all, :conditions =\> [“t2.id IS NULL AND  
templates.template\_id IS NULL”], :joins =\> “LEFT JOIN templates t2 ON  
templates.id=t2.template\_id”, :select =\> “templates.\*”)

The key was changes the SELECT to include “templates.\*”.

Without that ActiveRecord would not populate the object with the  
returned values.

---

<div class="post-metadata">

**Author:** ![spdemac](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/6de8d8/32.png) [@spdemac](https://www.ruby-forum.com/u/spdemac)\
**Post date:** [May 24, 2006, 12:48am UTC](https://www.ruby-forum.com/t/self-referencing-has-many/61424/6 "2006-05-24T00:48:31Z")

</div>

unknown wrote:

> have you tried acts\_as\_tree instead?

The touble isn’t the current relationships. The issue is finding records  
with no children.

Unless I am missing some feature in acts\_as\_tree

---

<div class="post-metadata">

**Author:** ![spdemac](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/6de8d8/32.png) [@spdemac](https://www.ruby-forum.com/u/spdemac)\
**Post date:** [May 25, 2006, 3:10pm UTC](https://www.ruby-forum.com/t/self-referencing-has-many/61424/7 "2006-05-25T15:10:21Z")

</div>

Oh, sorry. I’ve actually looked at the docs now. I thought  
acts\_as\_tree had something like that. Turns out I was getting confused  
with acts\_as\_nested\_set. Speaking of which, I think acts\_as\_nested\_set  
would provide a much cleaner solution than you have now  
([Peak Obsession](http://wiki.rubyonrails.com/rails/pages/ActsAsNestedSet)). Use  
children.count == 0.  
-N

---

<div class="post-metadata">

**Author:** ![spdemac](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/6de8d8/32.png) [@spdemac](https://www.ruby-forum.com/u/spdemac)\
**Post date:** [May 25, 2006, 4:31pm UTC](https://www.ruby-forum.com/t/self-referencing-has-many/61424/8 "2006-05-25T16:31:42Z")

</div>

Thanks. I will take a look at acts\_as\_nested\_set.