PHP / MySQL: getting one way in the Adjacency list model

Question

Is there any efficient way, without limiting the depth, to extract one path in the Adjacency List model based on the node identifier? For example, if I have an identifier for a node named "Banana", I could get the following Path: Food> Fruits> Banana

This is not a big problem if it is not possible, but I was thinking about whether it is possible to run joins through a while loop or something else? Until the parent becomes 0.

Answer 1

No, not in MySQL, at least. This is one of the biggest limitations of the Model List of adjacent regions .

You could continue to independently join a finite number of times, but this is ugly, inconvenient, and not covering an unlimited department. You can also download all the data in your application, build a tree and find the path in the application.

Some DBMSs, such as SQL Server 2005, Postgres 8.4, and Oracle 11g, support recursive queries using common table expressions with the WITH keyword. This feature makes it easy to write questions like this, but unfortunately MySQL does not yet support recursive queries.

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

• Mike Hillier: Managing Hierarchical Data in MySQL

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

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

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

Answer 2

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 

Answer 3

No, MySQL does not have recursive queries such as PostgreSQL, Oracle, or SQL Server. The adjacency list model is not a great model when using MySQL, the nested set is better (but more complex).

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