Brad Wood said:
Ok, but let try and move a large branch of a nested set somewhere
else in the hierarchy. :)
I'll begin with the adjacency list model:
Step 1: Update predecessor_id of the top of the branch
Done.
You are not done. How do you know its position relative to its siblings?
I used the nested set model for the v3.0 version of my CMS I wrote in the
last three weeks. I am delighted with the ease of doing a lot of the CMS
tasks - positioning parts of the site, moving chunks of site around,
building sitemaps, all those kinds of things
Mind you, I'm not building
ICGLink, Inc.
[EMAIL PROTECTED]
615.370.1530 x737
--//-
-Original Message-
From: Barney Boisvert [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 31, 2006 5:31 PM
To: CF-Talk
Subject: Re: Nested Set Model
Really, there's nothing to change. No triggers, procs, magic
That's an adjacency list, not nested sets. See my second example
(though I omitted the normalized keys for brevity).
cheers,
barneyb
On 2/1/06, Andy Matthews [EMAIL PROTECTED] wrote:
That's not the way I've got my db set up. Mine looks like this:
id|name|parent
1|Books|0
2|Sci/Fi|1
: Wednesday, February 01, 2006 10:55 AM
To: CF-Talk
Subject: Re: Nested Set Model
That's an adjacency list, not nested sets. See my second example
(though I omitted the normalized keys for brevity).
cheers,
barneyb
~|
Message: http
Unless u have a cascade delete trigger to automatically remove orphans.
Russ
-Original Message-
From: Barney Boisvert [mailto:[EMAIL PROTECTED]
Sent: 01 February 2006 16:55
To: CF-Talk
Subject: Re: Nested Set Model
That's an adjacency list, not nested sets. See my second example
Which is often worse than having the orphans to begin with. For
example, if you fire someone and delete them from an org chart, all
their minions aren't fired as well, they become minions of the fired
person's boss until they're reassigned. I'd hate to be the one that
has to go to HR and say you
And this whole time I've been thinking I was doing something right.
Well, no one said that an adjacency list model is wrong. Like our
discussions, the correct model for you is dependant on its usage and
environment.
Of course, you are back to recursion or iteration for a solution.
~Brad
PROTECTED]
Sent: Wednesday, February 01, 2006 1:22 PM
To: CF-Talk
Subject: RE: Nested Set Model
And this whole time I've been thinking I was doing something right.
Well, no one said that an adjacency list model is wrong. Like our
discussions, the correct model for you is dependant on its usage
I'm not sure what you wish to do with this level count, but be careful
if you plan on storing it in the original table. The level count is
already stored inherently in the table through its structure
brad, are you saying that i could do without the level column and rely instead
on the query to
Adjacency lists employ a parentID, so every node knows who it's parent
is, but that's it. Nested sets use a set of numbers (starting at
'left' and ending at 'right', typically) for each node, and the
relationships between the sets dictate hierarchy.
Adjacency list are simple and fast for tree
developers manually
creating code that might forget to update all artifacts - the intelligence
is encapsulated in the DAL.
Best Wishes,
Peter
-Original Message-
From: Barney Boisvert [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 01, 2006 3:18 PM
To: CF-Talk
Subject: Re: Nested Set Model
From what you describe, nested sets is probably better for your
scenario.
Actually I'm not so sure of this statement based on this quote of
Mike's:
my client wishes to be able to move categories to any other category
and also reorder a category within a particular level
That doesn't sound
That doesn't sound very static at all.
The question is how often it happens. I'd rather spend 20-fold the
time to move categories 50 times a day than I would spending 3-fold
the time to recall the stuff 5,000 times a day.
If these changes are infrequent and or the table will always remain
, 2006 2:18 PM
To: CF-Talk
Subject: Re: Nested Set Model
Adjacency lists employ a parentID, so every node knows who it's parent
is, but that's it. Nested sets use a set of numbers (starting at
'left' and ending at 'right', typically) for each node, and the
relationships between the sets dictate
4.1.x happily supports almost all forms of subqueries. That's the DB
I use almost exclusively. There are a few limitations (like you can't
update a table you've done a subquery on in the same query, and you
can't use LIMIT on a subquery), but they're basically full featured.
And even if you're
Hi Barney,
Agree wholeheartedly with performance comments re: my approach. Never said
it was good - I just didn't know about nested sets in '97!!! Am looking to
port to a nested set model based on your comments.
Do you know any easy way to get the nest level for each record using a
nested set
Hi Barney,
Agree wholeheartedly with performance comments re: my approach. Never said
it was good - I just didn't know about nested sets in '97!!! Am looking to
port to a nested set model based on your comments.
Do you know any easy way to get the nest level for each record using a
nested set
] NULL,
[cat_sort_order] [int] NULL
) ON [PRIMARY]
GO
there are definitely not going to be 100's of records in fact it should at all
times be reasonably small. so i am going for the nested set model.
to work work out the levels each record i am using...
cffunction name=rebuild_levels access
] wrote:
Hi Barney,
Agree wholeheartedly with performance comments re: my approach. Never said
it was good - I just didn't know about nested sets in '97!!! Am looking to
port to a nested set model based on your comments.
Do you know any easy way to get the nest level for each record using
Many thanks!!!
Best Wishes,
Peter
-Original Message-
From: Barney Boisvert [mailto:[EMAIL PROTECTED]
Sent: Wednesday, February 01, 2006 5:32 PM
To: CF-Talk
Subject: Re: SPAM-LOW: Re: Nested Set Model
I wasn't criticizing your approach at all. Nine years ago I wasn't even
aware
guys, on many sites i have looked at - when you click a category the link
includes an x=y= in the querystring. would this be referring to the left and
right columns of a nested model i wonder??
so if i know the left and right values for a particular category i can pull out
of the database the
however if the children also have children, how do i just pull out the first
generation of children?
You do a query that selects all decendants of the parent node in
question, except those that have have more than zero intervening
ancestors. To put that another way, you do a query for all
-LOW: Re: Nested Set Model
however if the children also have children, how do i just pull out the
first generation of children?
You do a query that selects all decendants of the parent node in question,
except those that have have more than zero intervening ancestors. To put
that another way
That's a good question. I have no idea. I've never run into
problems, though I haven't used it on huge recordsets. The farther up
the hierarchy you go, the less stuff changes, in general, so caching
could be used, probably with great effect.
Either way, it's fast enough until load testing
Mike Little wrote:
guys, on many sites i have looked at - when you click a category the link
includes an x=y= in the querystring. would this be referring to the left and
right columns of a nested model i wonder??
Probably not. It sounds a lot like the x and y of an image map.
And if it is a
barney, on my homepage i wish to display all top level categories eg. Summer,
Winter, Sale Items
i then wish to click on Summer and bring up only the child categories of summer
(just one level down).
i have tried the following query (taken from a mysql article on hierarchical
data)...
IF i use a cat_parent_id in my table i can use the following query to display
each level...
SELECT cat_ID, cat_title, cat_sort_order
FROM categories
WHERE cat_parent_id = cfqueryparam cfsqltype=cf_sql_integer
value=#URL.parent_id#
ORDER BY cat_sort_order
knowing that the top level categories
Except in Oracle, where it is a breeze.
On 2/2/06, Andy Matthews [EMAIL PROTECTED] wrote:
You're not lying there. It's simple to set the relationships between nodes,
but the retrieval is a bear.
--
CFAJAX docs and other useful articles:
http://jr-holmes.coldfusionjournal.com/
]
function=Railo Core Developer
Mike | NZSolutions Ltd schrieb:
Hi there,
I have the following category table which utilises a nested set model...
CREATE TABLE [dbo].[categories] (
[cat_id] [int] IDENTITY (1, 1) NOT NULL ,
[dModified] [datetime] NULL ,
[dCreated] [datetime] NULL
Be careful with recursion in MS SQL server 2000. There is a limit of 32
proc calls deep. Not sure about functions, though. This would have
obvious limitations of your structure was more than 32 levels deep.
I can cook up a coded sample of the iterative method if you want.
I'm not sure what
Check out this article:
Implementing the nested set model in ColdFusion
http://coldfusion.sys-con.com/read/42123.htm
hth,
Phil
Hi there,
I have the following category table which utilises a nested set model...
CREATE TABLE [dbo].[categories] (
[cat_id] [int] IDENTITY (1, 1) NOT NULL
The main advantage of the nested set model is that you don't have to
use recursion or loops to get data out. Consequently it's very fast
to recall stuff. Reorganizing the tree is more expensive than other
storage mechanisms, but pulling stuff out is hella fast. By and
large, you can get any
What you say is true. Initially I thought he was using an adjacent list
model when I saw his cat_parent_id column.
Where I work we all but phased out all of our implementations of the
nested set model (left right node) in favor of the adjacent list model
(predecessor_id). We were using them
either.
cheers,
barneyb
On 1/31/06, Brad Wood [EMAIL PROTECTED] wrote:
What you say is true. Initially I thought he was using an adjacent list
model when I saw his cat_parent_id column.
Where I work we all but phased out all of our implementations of the
nested set model (left right node
If data isn't changing too frequently, aggressive caching can pretty
much
negate the cost of the adjacency list, but again, that's not
applicable in all situations either.
Yeah, we used to have a lot of static data which accounted for a lot of
our database hits every page load (like security).
are). In general I'm a
fan of adjacency list over nested sets. I think the nested set model can
quickly get out wack as systems grow. The *wow* factor of pulling any branch
with a simple query quickly wears off. Either way, it's important to look at
the hierarchical problem you are trying to solve rather
snappy depending on the number of nodes
selected (actually more important is how deep the nodes are). In general I'm
a fan of adjacency list over nested sets. I think the nested set model can
quickly get out wack as systems grow. The *wow* factor of pulling any branch
with a simple query
Dave Ross wrote:
we hit similar issues with nested sets... the costs of inserts, moves and
deletes became too great.
Why would you need to change any numbers on deleting an element?
Jochem
~|
Message:
--//-
-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 31, 2006 2:15 PM
To: CF-Talk
Subject: Re: Nested Set Model
Dave Ross wrote:
we hit similar issues with nested sets... the costs of inserts, moves and
deletes became too great.
Why would
Andy Matthews wrote:
If you deleted an element, any children (and children of children) would
become orphans. So you'd at least have to change the association of the
child of the deleted record.
True for the adjacency list model. But the nested set model does
not have any problems with gaps
Not with a nested set, only with an adjacency list. With a nested
set, if you delete an ancestor, the decendants just move up a level
with no DB modification.
cheers,
barneyb
On 1/31/06, Andy Matthews [EMAIL PROTECTED] wrote:
If you deleted an element, any children (and children of children)
--//-
-Original Message-
From: Jochem van Dieten [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 31, 2006 2:35 PM
To: CF-Talk
Subject: Re: Nested Set Model
Andy Matthews wrote:
If you deleted an element, any children (and children of children) would
become orphans. So
Andy Matthews wrote:
It does when you retrieve data based on those dependancies. If you're trying
to connect 2 records and the middle record has been deleted then you're out
of luck unless you've made updates.
Could you elaborate with an example?
Jochem
: Tuesday, January 31, 2006 2:36 PM
To: CF-Talk
Subject: Re: Nested Set Model
Not with a nested set, only with an adjacency list. With a nested
set, if you delete an ancestor, the decendants just move up a level
with no DB modification.
cheers,
barneyb
On 1/31/06, Andy Matthews [EMAIL PROTECTED] wrote
procs.
!//--
andy matthews
web developer
ICGLink, Inc.
[EMAIL PROTECTED]
615.370.1530 x737
--//-
-Original Message-
From: Barney Boisvert [mailto:[EMAIL PROTECTED]
Sent: Tuesday, January 31, 2006 2:36 PM
To: CF-Talk
Subject: Re: Nested Set
Ok, but let try and move a large branch of a nested set somewhere else
in the hierarchy. :)
I'll begin with the adjacency list model:
Step 1: Update predecessor_id of the top of the branch
Done.
And now I'll start us off with the nested set model (Turn page over and
write on back if you need
On 1/31/06, Brad Wood [EMAIL PROTECTED] wrote:
Ok, but let try and move a large branch of a nested set somewhere else
in the hierarchy. :)
I'll begin with the adjacency list model:
Step 1: Update predecessor_id of the top of the branch
Done.
And now I'll start us off with the nested set
Hi there,
I have the following category table which utilises a nested set model...
CREATE TABLE [dbo].[categories] (
[cat_id] [int] IDENTITY (1, 1) NOT NULL ,
[dModified] [datetime] NULL ,
[dCreated] [datetime] NULL ,
[cat_parent_id] [int] NULL ,
[cat_lft
1. Recursion
or
2. A while loop over a table variable
~Brad
-Original Message-
From: Mike | NZSolutions Ltd [mailto:[EMAIL PROTECTED]
Sent: Monday, January 30, 2006 6:41 PM
To: CF-Talk
Subject: Nested Set Model
Hi there,
I have the following category table which utilises a nested set
1. Recursion
or
2. A while loop over a table variable
~Brad
Thanks for the pointer brad. i will see what i can find on recursion that might
help me out.
~|
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:230796
http://www.oreilly.com/catalog/sqlpr/chapter/ch01.pdf
On 1/31/06, Mike Little [EMAIL PROTECTED] wrote:
1. Recursion
or
2. A while loop over a table variable
~Brad
Thanks for the pointer brad. i will see what i can find on recursion that
might help me out.
--
CFAJAX docs and other
I have the following category table which utilises a nested
set model...
http://affy.blogspot.com/ntm/
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
Fig Leaf Software provides the highest caliber vendor-authorized
instruction at our training centers in Washington DC, Atlanta
53 matches
Mail list logo
