# Tree related problem

**URL:** <https://www.ruby-forum.com/t/tree-related-problem/142251>\
**Category:** Rails\
**Created:** [July 5, 2008, 8:42am UTC](https://www.ruby-forum.com/t/tree-related-problem/142251 "2008-07-05T08:42:54Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![mrbless](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/ba9def/32.png) [@mrbless](https://www.ruby-forum.com/u/mrbless)\
**Post date:** [July 5, 2008, 8:42am UTC](https://www.ruby-forum.com/t/tree-related-problem/142251/1 "2008-07-05T08:42:54Z")

</div>

How to find the child and subchild of particular node in a tree in sql.

---

<div class="post-metadata">

**Author:** ![mrbless](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/ba9def/32.png) [@mrbless](https://www.ruby-forum.com/u/mrbless)\
**Post date:** [July 5, 2008, 8:52am UTC](https://www.ruby-forum.com/t/tree-related-problem/142251/2 "2008-07-05T08:52:51Z")

</div>

On Sat, Jul 5, 2008 at 4:42 PM, mrbless [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> How to find the child and subchild of particular node in a tree in sql.

more data needed for intelligent answer…

–

> **[Mikel Lindsaar](https://lindsaar.net/)**
>
> Internet Home for Mikel Lindsaar

Rails, RSpec and Life blog…

---

<div class="post-metadata">

**Author:** ![mrbless](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/ba9def/32.png) [@mrbless](https://www.ruby-forum.com/u/mrbless)\
**Post date:** [July 5, 2008, 10:35am UTC](https://www.ruby-forum.com/t/tree-related-problem/142251/3 "2008-07-05T10:35:47Z")

</div>

Thanks I have resolve it.

---

<div class="post-metadata">

**Author:** ![mrbless](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/ba9def/32.png) [@mrbless](https://www.ruby-forum.com/u/mrbless)\
**Post date:** [July 5, 2008, 1:07pm UTC](https://www.ruby-forum.com/t/tree-related-problem/142251/4 "2008-07-05T13:07:20Z")

</div>

my database is like this:  
{  
id  
name  
subject  
body  
root\_id  
parent\_id  
lft  
rgt  
depth  
created\_at  
updated\_at  
}

now what I want to do for the given id is I want to find all its  
children and sub children

---

<div class="post-metadata">

**Author:** ![mrbless](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/ba9def/32.png) [@mrbless](https://www.ruby-forum.com/u/mrbless)\
**Post date:** [July 7, 2008, 12:40pm UTC](https://www.ruby-forum.com/t/tree-related-problem/142251/5 "2008-07-07T12:40:40Z")

</div>

# I think this work

@posts=ForumPost.find\_by\_sql("SELECT parent.\* "+  
"FROM forum\_posts AS node, "+  
"forum\_posts AS parent "+  
"WHERE node.lft\<=parent.lft AND node.rgt\>=parent.rgt "+  
“AND parent.root\_id=node.root\_id AND node.parent\_id!=0 AND  
node.parent\_id =”+params[:id])

===========

---

<div class="post-metadata">

**Author:** ![mrbless](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/ba9def/32.png) [@mrbless](https://www.ruby-forum.com/u/mrbless)\
**Post date:** [July 7, 2008, 12:41pm UTC](https://www.ruby-forum.com/t/tree-related-problem/142251/6 "2008-07-07T12:41:51Z")

</div>

# Tell me guys whether this is absolutely fine or not as I haven’t checked it for all cases:

@post=ForumPost.find\_by\_sql("SELECT parent.\* " +  
"FROM forum\_posts AS node, “+  
“forum\_posts AS parent “+  
“WHERE (node.lft\<=parent.lft AND node.rgt\>=parent.rgt " +  
“AND node.parent\_id =”+params[:id]+  
" AND parent.root\_id=node.root\_id AND node.parent\_id!=0) or  
(parent.id=”+params[:id]+ " AND node.id=”+params[:id] +”)”)

========================