# Help with tricky query / relationship?

**URL:** <https://www.ruby-forum.com/t/help-with-tricky-query-relationship/124668>\
**Category:** Rails\
**Created:** [December 31, 2007, 9:52pm UTC](https://www.ruby-forum.com/t/help-with-tricky-query-relationship/124668 "2007-12-31T21:52:52Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![goodieboy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/ecc23a/32.png) [@goodieboy](https://www.ruby-forum.com/u/goodieboy)\
**Post date:** [December 31, 2007, 9:52pm UTC](https://www.ruby-forum.com/t/help-with-tricky-query-relationship/124668/1 "2007-12-31T21:52:52Z")

</div>

Hi,

I’ve got a tree that is described by 2 different tables. One table is  
for the nodes, and another is for the relationships. The relationship  
table simply has a child\_node\_id and a parent\_node\_id. I’d like to  
find a way to find the root relations, something like: Relation.roots  
where:

the roots are relationships that have child\_node relationships  
pointing to them (by the child\_node relationship parent\_node\_id’s) but  
don’t have any other relationships pointing to them as if they were  
child nodes. In other words, I want to find all relations that have  
child nodes, but no parent nodes. This seems difficult because every  
relation has a child\_node\_id and a parent\_node\_id.

It seems to me that I’d have to do some kind of nested select or a  
tricky join on the same table. Anyone want to take a shot at this?

Thanks,  
Matt

I’ve copied in my fixtures below. The root relations in this case  
would be the mom and dad relations.

Here is an example of my node fixture:

dad:  
name: dad  
description: father

mom:  
name: mom  
description: mother

jr:  
name: jr  
description: son

son\_of\_jr:  
name: son\_of\_jr  
description: grandchild of mom and dad

########  
And here is an example of my relation fixture:  
########

one:  
child\_node: jr  
parent\_node: dad  
notes: jr is child of dad

two:  
child\_node: jr  
parent\_node: mom  
notes: jr is child of mom

three:  
child\_node: son\_of\_jr  
parent\_node: jr  
notes: son\_of\_jr is child of jr

---

<div class="post-metadata">

**Author:** ![goodieboy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/ecc23a/32.png) [@goodieboy](https://www.ruby-forum.com/u/goodieboy)\
**Post date:** [December 31, 2007, 10:08pm UTC](https://www.ruby-forum.com/t/help-with-tricky-query-relationship/124668/2 "2007-12-31T22:08:39Z")

</div>

OK I have the SQL that does what I want, now how can I implement this  
using the find method? I’d like to be able to use this but pass in  
additional conditions, like using with\_scope or something:

in the console:

> > CategoryRelation.find\_by\_sql(‘select a.\* from category\_relations as a left join category\_relations b on a.parent\_node\_id = b.child\_node\_id where b.child\_node\_id IS NULL’)

=\> [#\<CategoryRelation id: 953125641, child\_node\_id: 7170908,  
parent\_node\_id: 858529756, notes: “jr is child of dad”\>,  
#\<CategoryRelation id: 996332877, child\_node\_id: 7170908,  
parent\_node\_id: 936153944, notes: “jr is child of mom”\>]

Thanks,  
Matt

---

<div class="post-metadata">

**Author:** ![goodieboy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/ecc23a/32.png) [@goodieboy](https://www.ruby-forum.com/u/goodieboy)\
**Post date:** [December 31, 2007, 10:16pm UTC](https://www.ruby-forum.com/t/help-with-tricky-query-relationship/124668/3 "2007-12-31T22:16:49Z")

</div>

On 12/31/07, goodieboy [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> I’ve got a tree that is described by 2 different tables. One table is  
> for the nodes, and another is for the relationships. The relationship  
> table simply has a child\_node\_id and a parent\_node\_id. I’d like to  
> find a way to find the root relations, something like: Relation.roots  
> where:

I could be wrong but it sounds like you want all that  
wheel-already-invented goodness that comes with acts\_as\_tree.

[http://wiki.rubyonrails.org/rails/pages/ActsAsTree](http://wiki.rubyonrails.org/rails/pages/ActsAsTree)

–  
Greg D.  
[http://destiney.com/](http://destiney.com/)

---

<div class="post-metadata">

**Author:** ![goodieboy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/ecc23a/32.png) [@goodieboy](https://www.ruby-forum.com/u/goodieboy)\
**Post date:** [December 31, 2007, 10:18pm UTC](https://www.ruby-forum.com/t/help-with-tricky-query-relationship/124668/4 "2007-12-31T22:18:00Z")

</div>

Whew, got it. I seem to always answer my own questions. Maybe  
describing the problem to others helps? I think so…

class CategoryRelation \< ActiveRecord::Base

def self.roots(_args)  
 #return find\_by\_sql("select a._ from #{table\_name} as a left join  
#{table\_name} b on a.parent\_node\_id = b.child\_node\_id where  
b.child\_node\_id IS NULL")  
conditions=[‘b.child\_node\_id IS NULL’]  
joins=‘LEFT JOIN ’ + “#{table\_name}” + ’ b ON a.parent\_node\_id =  
b.child\_node\_id’

with\_scope(:find=\>{:conditions=\>conditions, :joins=\>joins,  
:select=\>‘a.\*’, :from=\>"#{table\_name}  
a"}) do  
find(:all, \*args)  
end  
end

end

---

<div class="post-metadata">

**Author:** ![goodieboy](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/g/ecc23a/32.png) [@goodieboy](https://www.ruby-forum.com/u/goodieboy)\
**Post date:** [December 31, 2007, 10:20pm UTC](https://www.ruby-forum.com/t/help-with-tricky-query-relationship/124668/5 "2007-12-31T22:20:20Z")

</div>

Ha, not quite (I wish it were that simple for me). The problem is this  
model has multiple parents! So it’s really a family tree. That’s OK  
though… because I’m just about ready to release (drum-roll  
please)… “acts\_as\_family\_tree” - I’ll post an announcement here  
soon.

Matt