Thanks joeles, but that won’t work methinks because in nested sets we’re not tracking the parentid to establish a relationship. The relationship is maintained by the lft and rgt column values. I suppose a column could be added to the table that tracks the parentid when a child is inserted, but that would also require modification of the deletion and (sub) tree moving and deletion methods as entries may become orphaned otherwise.

The goal is to compute the parent from the lft and rgt comparison. In esras model, there is a getAncestor() function - which should actually be called getAncestry() - which returns the path to a given childnode in a tree (not the parent as stated in the docs). I’m trying to get a modification of that to only return the immediate parent.

Building on top of that method, you can get the ancestry (path to a node) by comparing parent.lft < node.lft AND parent.right > node.right.

Now there is a way to compute the depth of nodes in a tree. It’s not in the class, but I’m using the code as shown on the mysql.com article. The solution to my problem will likely be in a combination of the ancestry AND where parent.depth = node.depth -1. That should do the trick.

I’ve just not been able to successfully wrap it into a SQL query.

really
wooww
i think it’ can’t be
because it’s still in one table

i have try but i can find good one
something like this

but i don’t have any idea,if someone have please tell us

::Removing foot from mouth::

Mirage, I can’t believe I totally glazed over something so fundamental. Duh! Thanks for the quick schooling. I’ll study up more before offering up additional suggestions.

joeles - you’re being too hard on yourself.

mirage - 05 March 2007 12:52 AM

Using the example - is there a single SELECT that can be written to return the name of a node in one and the name of it’s parent in a second column? So that the output would look like this:

+-----------------+--------------+
| name            | parent_name  |
+-----------------+--------------+
| top             |              |
| movies          | top          |
| romantic comedy | movies       |
| westerns        | movies       |
| books           | top          |
| ...             | ...          |
+-----------------+--------------+

I seem to think it’s possible, but it appears I’m experiencing sql-jam..

Thank you!
Juergen

Took me a while to figure it out, but this ought to do it:

It means selecting on the same table with three aliases (cat, parent and rel) at the same time but hey… 

Awesome thunder!

I just arrived at the same conclusion using a subquery, but I won’t post it because yours is leaner and cleaner. Nice job!

Many thanks,
Juergen

thunder - 05 March 2007 03:05 PM

mirage - 05 March 2007 12:52 AM

Using the example - is there a single SELECT that can be written to return the name of a node in one and the name of it’s parent in a second column? So that the output would look like this:

+-----------------+--------------+
| name            | parent_name  |
+-----------------+--------------+
| top             |              |
| movies          | top          |
| romantic comedy | movies       |
| westerns        | movies       |
| books           | top          |
| ...             | ...          |
+-----------------+--------------+

I seem to think it’s possible, but it appears I’m experiencing sql-jam..

Thank you!
Juergen

 

Took me a while to figure it out, but this ought to do it:

 

SELECT      cat.name      AS name,
            parent.name   AS parent
FROM        categories    AS cat

                 LEFT JOIN categories AS parent
                 ON parent.leftval = (

                           SELECT           MAX(rel.leftval)
                           FROM             categories AS rel
                           WHERE            rel.leftval < cat.leftval AND rel.rightval > cat.rightval
         
                  )

ORDER BY cat.leftval ASC

It means selecting on the same table with three aliases (cat, parent and rel) at the same time but hey… 

wowwwwwww
thanks dud you the best

mirage - 05 March 2007 09:59 AM

The goal is to compute the parent from the lft and rgt comparison. In esras model, there is a getAncestor() function - which should actually be called getAncestry() - which returns the path to a given childnode in a tree (not the parent as stated in the docs). I’m trying to get a modification of that to only return the immediate parent.

Are you sure? the getAncestor method should only return the immediate parent. Admittedly, a getAncestry method would be a handy addition too (and reasonably straight-forward to add, I think)

Now there is a way to compute the depth of nodes in a tree. It’s not in the class, but I’m using the code as shown on the mysql.com article. The solution to my problem will likely be in a combination of the ancestry AND where parent.depth = node.depth -1. That should do the trick.

I’ve just not been able to successfully wrap it into a SQL query.

That mysql.com article has some useful stuff in it - I’ll see if I can add some of those queries/sections into an updated class for the wiki. A getTree with parent_id, parent_name and depth/level would be very handy!

mirage - 05 March 2007 03:12 PM

Awesome esra!

I just arrived at the same conclusion using a subquery, but I won’t post it because yours is leaner anc cleaner. Nice job!

Many thanks,
Juergen

But my name’s not esra!

Sorry about that. Edited.

Jameson - 16 February 2007 05:48 AM

Unfortunately it won’t work under PHP4.

Call to a member function on a non-object in [...]/nested_sets_model.php on line 294

Specifically, this function causes error:

$query = $this->db->query($sql);

Ahh, that may be because your database library isn’t loaded at the point you’re making the query (me, I tend to have the database ‘autoloaded’ as I tend to use database driven sessions and site configs).

If you add $this->load->library(‘database’ ); to the constructor of the Category_demo controller, it should be ok.

That said, PHP4 users do still need to go through the Nested_sets_model class and remove all the “public” and “private” modifiers to the function names

so…

Needs to become

And likewise with the private ones too. :S

I’ll change that for the next version I upload.

thunder - 05 March 2007 03:49 PM

mirage - 05 March 2007 09:59 AM

... there is a getAncestor() function - which should actually be called getAncestry() - which returns the path to a given childnode in a tree (not the parent as stated in the docs). I’m trying to get a modification of that to only return the immediate parent.

Are you sure? the getAncestor method should only return the immediate parent. Admittedly, a getAncestry method would be a handy addition too (and reasonably straight-forward to add, I think)

Yes thunder, positive. The getAncestor function is missing the MAX clause you used in that nifty subquery.

That mysql.com article has some useful stuff in it - I’ll see if I can add some of those queries/sections into an updated class for the wiki. A getTree with parent_id, parent_name and depth/level would be very handy!

Actually I think you got most of it covered - and then some. The biggest problem with all of the articles written about ‘nested sets’ is that they all focus on querying the tree and have only poor examples of adding data. But you have solved that rather nicely in your model.

Is the wiki broken since the website upgrade? I can view the wiki entry, but the model file download isn’t working.

Wiki downloads appear to be working again ok

I’m curious about this class and I put on my thinking cap to try to understand what all the fuss is about.  Everyone keeps talking about trees and heirarchical data but I couldn’t really get at why the data in trees couldn’t be represented in a properly normalized database.

I think I’ve come up with a short answer for myself to what kind of data you would want to store in this type of database:

1) you don’t know the structure of the data before you start to get it (e.g., ad hoc categories, tags)
2) you do know the structure of the dataset but the data is not nice and tidy - it is heterogenous. For instance, you might want to store data with attributes that exist for data in one part of the dataset that don’t exist in another part and, for that problem, database normalization techniques won’t help to bring order. (e.g, a personnel database where some departments have more levels of heirarchy than others)

I suppose this is why thunder included the categories demo data because it nicely demonstrates a dataset that is not a good candidate for your typical database normalization.

If I am wrong or this definition needs refinement, please chime in!

EDIT: I found this writeup that has some clear discussion of why to use nested sets.