PHP/MySQL: Retrieve a single path in the Adjacency List model

Question

Is there any effective way to, without limiting the depth, retrieve a single path in a Adjacency List model based on the node's ID? Like if I've got an ID for a node named "Banana" I could get the following Path: Food > Fruits > Banana

It's not a big problem if it's impossible, but I thought about if it could be possible to run joins through a while-loop or something? Until the parent turns 0.

Paramorphism

Answer 1

No, not in MySQL at least. That is one of the biggest limitations of the Adjacency List Model.

You could continue to self join a finite number of times, but that's ugly, awkward and doesn't cover unlimited dept. You could also download all the data in your application, build a tree, and find the path in the application.

Some DBMSes, such as SQL Server 2005, Postgres 8.4 and Oracle 11g, support recursive queries using common table expressions with the WITH keyword. This feature allows queries such as this to be written with ease, but unfortunately MySQL does not support recursive queries yet.

You may may be interested in checking out the following article which describes an alternative model (the nested set model), which makes recursive operations easier (possible) in MySQL:

• Mike Hillyer: Managing Hierarchical Data in MySQL

In addition, I also suggest checking out the following presentation by @Bill Karwin, a regular contributor on Stack Overflow:

• Bill Karwin: Models for hierarchical data with SQL and PHP

The closure table model described in the presentation is a very valid alternative to the nested set. He describes this model in further detail in his SQL Antipatterns book (excerpt from the chapter on this topic).

Goring

SQL Antipatterns is a must have for developers. – Cleland

Okay, then I know the answer to this question. I've worked pretty much with the Nested Set model before, but that model isn't optimal for the site I'm working on today. By the way, as you mentioned tree I remembered that I've built a tree with a single query before and some PHP loops. But I can't find my work - you don't happen to know the solution for that, do you? – Paramorphism

@Ivarska: I can't help with that unfortunately :) ... Did you consider the "closure table model"?... It's easier to implement than nested sets (but uses more data, and requires a separate table). – Goring

Never mind, I think I'll figure it out. ;) I'm afraid I can't find much information about this Closure Table model. Not a big deal, but I'm certainly interested in different database models. – Paramorphism

Answer 2

No, MySQL doesn't have recursive queries like PostgreSQL, Oracle or SQL Server. The adjacency list model isn't a great model when using MySQL, a nested set is a better (but more complex) one.

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

Cleland

Answer 3

Try this query:

SET @id:=12345;

SELECT content_name, content_id, (@id:=content_parent) as content_parent 
FROM 
    ( SELECT content_id, content_name, content_id, content_parent 
      FROM content_table 
      ORDER BY content_parent DESC
    ) AS aux_table 
    WHERE content_id = @id

Federative