On Mon, 03 Mar 2008 15:10:20 +0100, Rik Wasmus
<luiheidsgoeroe@hotmail.com> wrote:

> 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;

On a side note: see the difference in counting category_id & GROUPing by
that? If you don't enforce a UNIQUE name in the table, don't group by it.
However, technically it's possible to have 2 nodes, on the same level,
with the same parent, with the same name. If that's possible in the real
table used, some more elaborate syntax is required:

SELECT MAX(CHAR_LENGTH(name)) FROM nested_category INTO @char_length;
SELECT FLOOR(LOG10(MAX(category_id))) + 1 FROM nested_category INTO
@id_length;
SELECT
CONCAT(REPEAT(' ',COUNT(parent.category_id)-1),node.name) AS name,
GROUP_CONCAT(CONCAT(RPAD(parent.name,@char_length, '
'),LPAD(parent.category_id,@id_length,'0')) 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;

Notice we anxiously keep 1 parent portion of the path a fixed length. Not
doing so might yield unexpected results if a name of a record starts with
a number. It would be a rare condition indeed (exact same parent, and id
+ number in a name actually matching up with an id of a sibling), but it
wouldn't be the first time 'rare' conditions occured and destroyed a lot
of logic further on.
--
Rik Wasmus