The tree in question contains categories, subcategories and finally image
galleries. It is common for the user to want to sort all the subordinates
of one level a different way, at times alphanumeric, other times simply to
their liking. I have been reading through Joe Celko's Trees and
Hierarchies In Sql for Smarties book to refresh the old brain. He never
talks about how to do any sorting, which tells me it is none trivial, but I
am sure it has been done.
My thought process is to do this:
1. create a temp table to hold all the descendants of the parent
2. copy the subordinates (and descendants) into the temp table one at a
time in the new order to get the lft/rgt values correct
3. Once all the children and descendants are copied into the temp table,
update the lft/rgt values of the source table to get the new order
Is this a valid approach? Is there a better one?
Main Question: What is the best way to implement the insert in #2?
Ideally, I would like to do that in one SQL statement, do one insert select
that reorders things based on a where condition. What is throwing me off
is the issue of the descents of what is being sorted, their order should
NOT be changing. This would seem like a great place for a stored proc or
in the case of SQLite the WITH clause. Can the WITH clause do what I am
trying to achieve? An outer select controls the ordering of the
subordinates being sorted and the inner select does the actual gathering of
the data to insert into the temp table? If this is possible, what might it
look like?
P.S. In the book Joe talks about creating a view that shows subordinates,
for starts that can be used to sort alphanumeric:
select ChildOID, ChildName, lft, rgt
from EventNodeSubordinates
where ParentOID = '98f13b01-3936-44b0-84a4-56681320fb7d' and
ChildOID <> '98f13b01-3936-44b0-84a4-56681320fb7d'
order by ChildName
Pax vobiscum,
Sam Carleton
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
