Recursive SQL Queries

For instance, lets say we have the following URL:

http://www.mysite.com/articles/how-to/home/plumbing

The following items in the URL are objects in the database:

• plumbing (parent is home)
• home (parent is how-to)
• how-to (no parent)

So your object table might look like:

Not all DBMSs offer recursive queries, so this method only applies to those that don't. The following SQL query will return a list of those objects in hierarchical form:

Now, the question is, how can we dynamically generate such a query? In PHP, you can use the following code as a guide.

$object_path = array_slice(explode('/', $_SERVER['PATH_INFO']),1);

$depth = sizeof($object_path);

$select = 'SELECT ';
for ($i = 1; $i < $depth; $i++)
  $select .= "t{$i}.local_id as local_id{$i}, t{$i}.title as title{$i}, ";

$select .= 't' . $depth . '.local_id, t' . $depth . '.id, t' . $depth . '.object_type, t' . $depth . '.user_id, t' . $depth . '.title, t' . $depth . '.ts_modified ';

$from = 'FROM object AS t1 ';
$join = '';
for ($i = 2; $i <= $depth; $i++) {
  $prev = $i - 1;
  $join .= ' LEFT JOIN object AS t' . $i . ' ON t' . $i . '.parent_id = t' . $prev . '.id ';
}

$where = "WHERE t1.local_id = '{$object_path[0]}'";
for ($i = 2; $i <= $depth; $i++)
  $where .= " AND t{$i}.local_id = '{$object_path[$i - 1]}'";

$sql = $select . $from . $join . $where;

Posted 1 year, 2 months ago on July 21, 2007
The trackback url for this post is http://blog.universalwebservices.net/bblog/trackback.php/7/

---