select parent?

#1

Hello,

I have the following table:

id=int
pid=int
name=varchar(100)

It will hold tree items where 'pid' is the parent id. If zero then the
item belongs to the root and has no parents.

Suppose I have this structure:

[dir] -> pid=0, id=1
dir\f1 -> id=2, pid=1
dir\[dir2] -> id = 3, pid=1
dir\dir2\f2 -> id=4, pid=3

Is there is a nice way to directly figure out the root's id given the
record with id=4 ?

So I want to tell what is the root parent of itemid=4 (the answer is
id=1).

Thanks

#2

On 29 June, 12:24, lallous <lall... (AT) lgwm (DOT) org> wrote:

Quote:

Hello,

I have the following table:

id=int
pid=int
name=varchar(100)

It will hold tree items where 'pid' is the parent id. If zero then the
item belongs to the root and has no parents.

Suppose I have this structure:

[dir] -> pid=0, id=1
dir\f1 -> id=2, pid=1
dir\[dir2] -> id = 3, pid=1
dir\dir2\f2 -> id=4, pid=3

Is there is a nice way to directly figure out the root's id given the
record with id=4 ?

So I want to tell what is the root parent of itemid=4 (the answer is
id=1).

Thanks

Take a look at:
http://delicious.com/Captain_Paralytic/hierarchical

#3

Quote:

Take a look at:
http://delicious.com/Captain_Paralytic/hierarchical

A warning: I did the left-field / right-field trick and hit myself hard.
Especially in combination with autonumbers, the left-right technique,
although elegant, causes all kinds of nasty locking problems. These can
be worked around, but it was so much trouble that I switched to using
path strings instead.

Also, a path string can be faster and more easily faster. When you use
the left-right approach, you really learn to value the STRAIGT_JOIN
keyword in your queries.

Best regards,
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

#4

On 2010-06-29 13:24, lallous wrote:

Quote:

Hello,

I have the following table:

id=int
pid=int
name=varchar(100)

It will hold tree items where 'pid' is the parent id. If zero then the
item belongs to the root and has no parents.

Suppose I have this structure:

[dir] -> pid=0, id=1
dir\f1 -> id=2, pid=1
dir\[dir2] -> id = 3, pid=1
dir\dir2\f2 -> id=4, pid=3

Is there is a nice way to directly figure out the root's id given the
record with id=4 ?

So I want to tell what is the root parent of itemid=4 (the answer is
id=1).

Unless you extend your datamodel one way or another, you will need a
dbms that supports some kind of closure operation (for example recursive
common table expressions), but that is not supported in mysql.

Troels Arvin has a nice set of links to various articles on the subject:

http://troels.arvin.dk/db/rdbms/links/#hierarchical

/Lennart

#5

"lallous" <lallous (AT) lgwm (DOT) org> wrote

Quote:

Hello,

I have the following table:

id=int
pid=int
name=varchar(100)

It will hold tree items where 'pid' is the parent id. If zero then the
item belongs to the root and has no parents.

Suppose I have this structure:

[dir] -> pid=0, id=1
dir\f1 -> id=2, pid=1
dir\[dir2] -> id = 3, pid=1
dir\dir2\f2 -> id=4, pid=3

Is there is a nice way to directly figure out the root's id given the
record with id=4 ?

So I want to tell what is the root parent of itemid=4 (the answer is
id=1).

Unless you know the depth in advance I don't think you can generate a single
SQL statement which will do it for you. I think you will need to fall back
to a programming language (PHP, Delphi, Java, .NET - whatever you feel most
at home with) and do it using that - basically a simple loop reading a
record and looping until the record doesn't have a parent.
--
Brian Cryer
http://www.cryer.co.uk/brian

#6

Brian Cryer wrote:

Quote:

"lallous" <lallous (AT) lgwm (DOT) org> wrote in message
news:09dad895-95dc-4bfd-8810-2c0c5ec16075 (AT) w12g2000yqj (DOT) googlegroups.com...
Hello,

I have the following table:

id=int
pid=int
name=varchar(100)

It will hold tree items where 'pid' is the parent id. If zero then the
item belongs to the root and has no parents.

Suppose I have this structure:

[dir] -> pid=0, id=1
dir\f1 -> id=2, pid=1
dir\[dir2] -> id = 3, pid=1
dir\dir2\f2 -> id=4, pid=3

Is there is a nice way to directly figure out the root's id given the
record with id=4 ?

So I want to tell what is the root parent of itemid=4 (the answer is
id=1).

Unless you know the depth in advance I don't think you can generate a
single SQL statement which will do it for you.

I did not find a way to do that. For arbitrary depths.

You can however do as many left joins as the maximum depth you have, and
concatenate fields to get one answer.

I think you will need to

Quote:

fall back to a programming language (PHP, Delphi, Java, .NET - whatever
you feel most at home with) and do it using that - basically a simple
loop reading a record and looping until the record doesn't have a parent.

Which is probably no less efficient than using multiple joins, to be honest.

#7

lallous wrote:

Quote:

Hello,

I have the following table:

id=int
pid=int
name=varchar(100)

It will hold tree items where 'pid' is the parent id. If zero then the
item belongs to the root and has no parents.

Suppose I have this structure:

[dir] -> pid=0, id=1
dir\f1 -> id=2, pid=1
dir\[dir2] -> id = 3, pid=1
dir\dir2\f2 -> id=4, pid=3

Is there is a nice way to directly figure out the root's id given the
record with id=4 ?

So I want to tell what is the root parent of itemid=4 (the answer is
id=1).

Thanks

As Lennart indicated, recursive SQL works great on such operations, but
it's too bad MySQL can't do them.

You should, however, be able to do it in a stored procedure.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#8

On Jun 29, 2:56*pm, Willem Bogaerts <w.bogae... (AT) kratz (DOT) nl> wrote:

Quote:

Take a look at:
http://delicious.com/Captain_Paralytic/hierarchical

A warning: I did the left-field / right-field trick and hit myself hard.
Especially in combination with autonumbers, the left-right technique,
although elegant, causes all kinds of nasty locking problems. These can
be worked around, but it was so much trouble that I switched to using
path strings instead.

Also, a path string can be faster and more easily faster. When you use
the left-right approach, you really learn to value the STRAIGT_JOIN
keyword in your queries.

Best regards,
--
Willem Bogaerts

Application smith
Kratz B.V.http://www.kratz.nl/

Thank you all for the answers, since I never worked w/ this before I
found the provided links very informative.
I will be using a programming language anyway, but was concerned about
the amount of queries to determine the path for every node.
But at the same time, this will not be a problem because the user
interface will not prefetch everything at once, instead if user
expands a node, we do an extra query to determine parent or siblings.

@Willem: so what exactly you mean by path strings?you store the
complete path in each node?

#9

On Tue, 29 Jun 2010 14:35:17 +0100, Brian Cryer wrote:

Quote:

"lallous" <lallous (AT) lgwm (DOT) org> wrote in message
news:09dad895-95dc-4bfd-8810-2c0c5ec16075 (AT) w12g2000yqj (DOT) googlegroups.com...
Hello,

I have the following table:

id=int
pid=int
name=varchar(100)

It will hold tree items where 'pid' is the parent id. If zero then the
item belongs to the root and has no parents.

Suppose I have this structure:

[dir] -> pid=0, id=1
dir\f1 -> id=2, pid=1
dir\[dir2] -> id = 3, pid=1
dir\dir2\f2 -> id=4, pid=3

Is there is a nice way to directly figure out the root's id given the
record with id=4 ?

So I want to tell what is the root parent of itemid=4 (the answer is
id=1).

Unless you know the depth in advance I don't think you can generate a single
SQL statement which will do it for you. I think you will need to fall back
to a programming language (PHP, Delphi, Java, .NET - whatever you feel most
at home with) and do it using that - basically a simple loop reading a
record and looping until the record doesn't have a parent.

Stored procedures can recurse. At least as of version 5 point
*mumble*...

--
Life does not cease to be funny when people die any more than it ceases
to be serious when people laugh.
-- George Bernard Shaw

#10

Quote:

@Willem: so what exactly you mean by path strings?you store the
complete path in each node?

I have defined a "parent" field as VARCHAR DEFAULT '/'. Any top-level
object (that means object with no parent) will have '/' as its path. For
any parent, I prepend a slash and the parent's ID value. So if the
parent field holds "/2/34/", it has a top-level object with ID#2 and a
direct parent with ID#34. You can search on any subtree with a
LIKE-query. For instance, if the example object wants to find other
objects in the same subtree, it can be done with:

SELECT * FROM TableName WHERE Parent LIKE '%/34/%';

Best regards,
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/