Hi there,

I have a table like the one below:

CREATE TABLE IF NOT EXISTS menuitens (
id int(11) NOT NULL auto_increment,
idMenu int(11) NOT NULL default 0,
idParent int(11) NOT NULL default 0,
PRIMARY KEY (id)
) TYPE=INNODB;

Because I will allow parent itens, I have that idParent thats
corresponds to an existing id of menuitens table, my dought is, how
can I have a query that lists all the data in the correct
order, in other words something similar to the example below:

Example:

Row1
Row2
Row2.1
Row2.2
Row2.3
Row3
Row3.1
Row4
Row5
Row6
Row6.1

Thanks in advance.

On Wed, 26 Sep 2007 13:38:48 +0200, João Morais <jcsmorais@gmail.com>
wrote:

> Hi there,
>
> I have a table like the one below:
>
> CREATE TABLE IF NOT EXISTS menuitens (
> id int(11) NOT NULL auto_increment,
> idMenu int(11) NOT NULL default 0,
> idParent int(11) NOT NULL default 0,

I'd say idParent should be NULL instead of 0 for root elements (has all
sorts of advantages, one of which is deleting a whole subtree in one
statement by cascading deletes).

> PRIMARY KEY (id)
> ) TYPE=INNODB;
>
> Because I will allow parent itens, I have that idParent thats
> corresponds to an existing id of menuitens table, my dought is, how
> can I have a query that lists all the data in the correct
> order, in other words something similar to the example below:
>
> Example:
>
> Row1
> Row2
> Row2.1
> Row2.2
> Row2.3
> Row3
> Row3.1
> Row4
> Row5
> Row6
> Row6.1

It could very well be the most referenced page concerning hierarchical
data, anyway, this will explain a lot:
http://dev.mysql.com/tech-resources/...ical-data.html

See the example "Retrieving a Full Tree" for the Adjacency Model. Jost add
order by clause (I assume on idMenu?) like:
ORDER BY t1.idMenu,t2.idMenu,..etc...
--
Rik Wasmus

I think that will solve my problem, thanks a lot mate very good
examples

Im having a couple of troubles trying to solve the following problem,

CREATE TABLE IF NOT EXISTS menuitens (
id int(11) NOT NULL auto_increment,
idMenu int(11) NOT NULL default 0,
idParent int(11) NOT NULL default 0,
position int(11) NOT NULL default 0,
PRIMARY KEY (id)
) TYPE=INNODB;

With this I will have a tree structure with rows ordered by position
field example:

position - field name

1 - a
1 - a1
1 - a11
1 - a11
2 - a2
1 - a21
3 - a3
4 - a4
1 - a41
2 - a42

....

Although I can select every rows I cannot order them by position field
and keep the "parent-child" grouping, can any one help on this?

Thaks in advance.

On Wed, 26 Sep 2007 20:04:40 +0200, João Morais <jcsmorais@gmail.com>
wrote:

> Im having a couple of troubles trying to solve the following problem,
>
> CREATE TABLE IF NOT EXISTS menuitens (
> id int(11) NOT NULL auto_increment,
> idMenu int(11) NOT NULL default 0,
> idParent int(11) NOT NULL default 0,
> position int(11) NOT NULL default 0,
> PRIMARY KEY (id)
> ) TYPE=INNODB;
>
> With this I will have a tree structure with rows ordered by position
> field example:
>
> position - field name
>
> 1 - a
> 1 - a1
> 1 - a11
> 1 - a11
1 - a111 I presume
> 2 - a2
> 1 - a21
> 3 - a3
> 4 - a4
> 1 - a41
> 2 - a42
>
> ...
>
> Although I can select every rows I cannot order them by position field
> and keep the "parent-child" grouping, can any one help on this?

What is the curent query you're using? As long as you have an order by
clause detailing "ORDER BY t1.position, t2.position, t3.position" etc...
in the previous example for every level of your tree everything should be
OK.
--
Rik Wasmus

> 1 - a111 I presume
You're right, my mistake.

> What is the curent query you're using?

SELECT DISTINCT mi1.id, mi1.idParent, mi1.name, mi1.position
FROM menuitens AS mi1
LEFT JOIN menuitens mi2 ON mi1.id=mi2.idParent AND mi2.idMenu=1
WHERE mi1.idMenu=1


That will provide the following:

id | idParent | name | position

5 0 a 1
9 13 a111 1
10 5 a2 2
11 5 a3 3
12 5 a1 1
13 12 a11 1
14 0 b 2

When what i want is something like:

id | idParent | name | position

5 0 a 1
12 5 a1 1
13 12 a11 1
9 13 a111 1
10 5 a2 2
11 5 a3 3
14 0 b 2

On 27 Sep, 09:42, "João Morais" <jcsmor...@gmail.com> wrote:
> > 1 - a111 I presume
>
> You're right, my mistake.
>
> > What is the curent query you're using?
>
> SELECT DISTINCT mi1.id, mi1.idParent, mi1.name, mi1.position
> FROM menuitens AS mi1
> LEFT JOIN menuitens mi2 ON mi1.id=mi2.idParent AND mi2.idMenu=1
> WHERE mi1.idMenu=1
>
> That will provide the following:
>
> id | idParent | name | position
>
> 5 0 a 1
> 9 13 a111 1
> 10 5 a2 2
> 11 5 a3 3
> 12 5 a1 1
> 13 12 a11 1
> 14 0 b 2
>
> When what i want is something like:
>
> id | idParent | name | position
>
> 5 0 a 1
> 12 5 a1 1
> 13 12 a11 1
> 9 13 a111 1
> 10 5 a2 2
> 11 5 a3 3
> 14 0 b 2

Why are you using that query rather than the one that Rik pointed you
to?

SELECT m1.id, m1.idParent, m1.name, m1.position
FROM menuitens m1
LEFT JOIN menuitens m2 ON m1.id=m2.id AND m1.idParent<m2.idParent
WHERE m2.idParent IS NULL
ORDER BY m1.position, m1.idParent

This one almost does what I need, result provided:

id | idParent | name | position
5 0 a 1
12 5 a1 1
13 12 a11 1
9 13 a111 1
14 0 b 2
10 5 a2 2
11 5 a3 3

(id 14 should be at the bottom)

When it should be:

id | idParent | name | position
5 0 a 1
12 5 a1 1
13 12 a11 1
9 13 a111 1
10 5 a2 2
11 5 a3 3
14 0 b 2

Hope someone can help me on this, cause I dont want to use recursive
solutions since this script will be used often and thats not good for
the server.

> Why are you using that query rather than the one that Rik pointed you
> to?

Because the query that Rik pointed me to, has a limited number of
levels.

On Thu, 27 Sep 2007 11:32:17 +0200, João Morais <jcsmorais@gmail.com>
wrote:

>> Why are you using that query rather than the one that Rik pointed you
>> to?
>
> Because the query that Rik pointed me to, has a limited number of
> levels.


.... which is an unavoidable result from using the adjacency model. If your
levels are limited, it's prefectly allright to use such a construct and a
limited query. If you want both unlimited levels and a properly formated
tree without recursive queries the nested set model is the only way to go
that I know of.

sorting by id as in your attempt doesn't make any sense at all BTW. It
just 'happens' to be close to what you want.
--
Rik Wasmus