CODEWITHSUNDEEP
phpmysqlpathadjacency-list
Ivar
Ivar

Reputation: 4392

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

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.

Upvotes: 2

Views: 2795

Answers (3)

CSI
CSI

Reputation: 11

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

Upvotes: 1

Frank Heikens
Frank Heikens

Reputation: 127136

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/

Upvotes: 1

Daniel Vassallo
Daniel Vassallo

Reputation: 344351

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:

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

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).

Upvotes: 2

Related Questions