Archive for March 21st, 2009
This is a series of articles on hierarchical queries in MySQL:
- Hierarchical queries in MySQL
- Hierarchical queries in MySQL: adding level
- Hierarchical queries in MySQL: adding ancestry chains.
- Hierarchical queries in MySQL: finding leaves
- Hierarchical queries in MySQL: finding loops
Today, we will check our structure for loops.
Loops in hierarchical queries occur when a row contains itself in its ancestry chain.
In general, it’s a sign of a database logic flaw and should be avioded, but sometimes it’s deliberately used.
The simplest and most easily detectable case is having
id equal to
parent (meaning that the row is both its father and child). This may be tested by using a simple comparison condition.
But a loop of course can be more complex: a row can be its own grandfather, grand-grandfather etc.
To detect such loops, we will improve our functions a little.
Read the rest of this entry »