Thread: another question about connectby from contrib

ELECT f.*, link, text, target, icon, node FROM connectby('menu', 'id',
'parent_id', 8, 0) AS f(id integer, parent_id integer, level integer),
menu where menu.id = f.id;

I have got this:

 id | parent_id | level | link |      text       | target | icon | node
----+-----------+-------+------+-----------------+--------+------+------
  8 |           |     0 |      | #3         |        |      | t
 11 |         8 |     1 |      | #3.1       |        |      | t
 12 |         8 |     1 |      | #3.2       |        |      | f
 13 |        11 |     2 |      | #3.1.1     |        |      | f
 14 |        11 |     2 |      | #3.1.2     |        |      | t
 15 |        11 |     2 |      | #3.1.3     |        |      | f
 16 |        11 |     2 |      | #3.1.4     |        |      | t
 17 |        11 |     2 |      | #3.1.5     |        |      | f
 18 |        14 |     3 |      | #3.1.2.1   |        |      | f
 19 |        14 |     3 |      | #3.1.2.2   |        |      | f
 20 |        14 |     3 |      | #3.1.2.3   |        |      | f
 21 |        16 |     3 |      | #3.1.4.1   |        |      | f
 22 |        16 |     3 |      | #3.1.4.2   |        |      | f
 23 |        16 |     3 |      | #3.1.4.3   |        |      | t
 24 |        16 |     3 |      | #3.1.4.4   |        |      | f
 25 |        23 |     4 |      | #3.1.4.3.1 |        |      | f
 26 |        23 |     4 |      | #3.1.4.3.2 |        |      | f
 27 |        23 |     4 |      | #3.1.4.3.3 |        |      | f
 28 |        16 |     3 |      | #3.1.4.5   |        |      | f

How am I able to get result where text field is like this:

#3
#3.1
#3.1.1
#3.1.2
#3.1.2.1
#3.1.2.2
#3.1.2.3
#3.1.3
#3.1.4
#3.1.4.1
#3.1.4.2
#3.1.4.3
#3.1.4.3.1
#3.1.4.3.2
#3.1.4.3.3
#3.1.4.4
#3.1.4.5
#3.1.5
#3.2


--
WBR, sector119

sector119@mail.ru wrote:
> ELECT f.*, link, text, target, icon, node FROM connectby('menu', 'id',
> 'parent_id', 8, 0) AS f(id integer, parent_id integer, level integer),
> menu where menu.id = f.id;
>
> I have got this:
>
>  id | parent_id | level | link |      text       | target | icon | node
> ----+-----------+-------+------+-----------------+--------+------+------
>   8 |           |     0 |      | #3         |        |      | t
>  11 |         8 |     1 |      | #3.1       |        |      | t
[...snip...]
>
> How am I able to get result where text field is like this:
>
> #3
> #3.1
[...snip...]

I don't see any difference. You'll have to be more clear with your question.

Joe

> id | parent_id | level | link |  text      | target | icon | node
>----+-----------+-------+------+------------+--------+------+------
>  8 |           |     0 |      | #3         |        |      | t
> 11 |         8 |     1 |      | #3.1       |        |      | t
> 12 |         8 |     1 |      | #3.2       |        |      | f
> 13 |        11 |     2 |      | #3.1.1     |        |      | f

If I get this right, you have a tree represented in a table in a bfs
order (by id) and want to output a dfs order (by text)?

>#3
>#3.1
>#3.1.1
>#3.1.2

so I guess you could simply add an "ORDER BY text" clause to your query
and be done as long as you don't have any really weird locale sorting this
text column in an unintuitive manner.

HTH
  Jan