On Mon, 03 Mar 2008 13:58:33 +0100, Captain Paralytic
<paul_lautman@yahoo.com> wrote:

> On 3 Mar, 12:42, Maxxx <non...@nohost.xxx> wrote:
>> Hi again,
>>
>> Some time ago I asked for a suggestion about nested set query and I had
>> a
>> really valuable help, thank you again to all.
>> I'm developing my application using the queries managing nested set
>> method and I have another "curiosity" about an additional feature I
>> would
>> like to have in the data result from the query. I think my "request"
>> will
>> not be possible to realize, but I would try to ask. :-)
>>
>> Based to this article:
>>
>> http://dev.mysql.com/tech-resources/...ical-data.html
>>
>> the following query will extract tree data as reported:
>>
>> SELECT node.name, (COUNT(parent.name) - 1) AS depth
>> FROM nested_category AS node,
>> nested_category AS parent
>> WHERE node.lft BETWEEN parent.lft AND parent.rgt
>> GROUP BY node.name
>> ORDER BY node.lft;
>>
>> +----------------------+-------+
>> | name | depth |
>> +----------------------+-------+
>> | ELECTRONICS | 0 |
>> | TELEVISIONS | 1 |
>> | TUBE | 2 |
>> | LCD | 2 |
>> | PLASMA | 2 |
>> | PORTABLE ELECTRONICS | 1 |
>> | MP3 PLAYERS | 2 |
>> | FLASH | 3 |
>> | CD PLAYERS | 2 |
>> | 2 WAY RADIOS | 2 |
>> +----------------------+-------+
>>
>> This query order data by the "lft" field for have all the nodes ordered
>> in "descending" way and this is very good for "make" a tree inside a
>> control. I would to know if it will be possible to have another "sub-
>> order" insithe this data. I would like to have this data in this same
>> main "descending" order but, inside every "sub-level depth group" have
>> also ordered the name alphabetically. For use the same example I would
>> to
>> have the data axtreacted ordered in the following mode:
>>
>> +----------------------+-------+
>> | name | depth |
>> +----------------------+-------+
>> | ELECTRONICS | 0 |
>> | PORTABLE ELECTRONICS | 1 |
>> | 2 WAY RADIOS | 2 |
>> | CD PLAYERS | 2 |
>> | MP3 PLAYERS | 2 |
>> | FLASH | 3 |
>> | TELEVISIONS | 1 |
>> | LCD | 2 |
>> | PLASMA | 2 |
>> | TUBE | 2 |
>> +----------------------+-------+
>>
>> Is this possible to have a query making this result? I think no, but is
>> better to ask to experts.
>> Thank you again for the help
>
> Did you try adding node.name to the ORDER BY clause? If so what
> happened?

That would destroy the whole ordering. lft is unique, and used for
maintaining the hierarchy. Sorting by lft first & name second would not
change a thing, sorting by name first & lft later will destory all
hierarchical information. A nested set has an explicit order, and to alter
that order still maintaining the hierarchy is quite difficult in 1 query.

The last time the exact same question was asked, this was the best answer
I could come up with:

On Fri, 01 Feb 2008 17:55:42 +0100, Rik Wasmus
<luiheidsgoeroe@hotmail.com> wrote:
> SELECT CONCAT( REPEAT(' ', COUNT(parent.name)-1), node.name) AS name,
> GROUP_CONCAT(parent.name ORDER BY parent.lft SEPARATOR '~') as 'path',
> node.lft
> FROM nested_category AS node
> LEFT JOIN nested_category AS parent
> ON node.lft BETWEEN parent.lft AND parent.rgt
> GROUP BY node.name
> ORDER BY path,node.name;
>
> '~' is chosen because of it's high ascii value. In case of unicode /
> higher characters, this could potentially break. If the
> possibilities/character set for node.name is limited, this will work
> though.

I'm open to better suggestions, one that comes to mind are right padding
the strings with the lowest ascii character available (space probably?) to
their maximum length:

SELECT MAX(CHAR_LENGTH(name)) FROM nested_category INTO @char_length;
SELECT
CONCAT(REPEAT(' ',COUNT(parent.category_id)-1),node.name) AS name,
GROUP_CONCAT(RPAD(parent.name,@char_length,' ') ORDER BY parent.lft
SEPARATOR '') AS 'path',
COUNT(parent.category_id) -1 as 'depth',
node.lft
FROM nested_category AS node
LEFT JOIN nested_category AS parent
ON node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.category_id
ORDER BY path;
--
Rik Wasmus