<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html><head><meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"><title>Hierarchical data</title><meta name="generator" content="DocBook XSL Stylesheets V1.60.1"><link rel="home" href="index.html" title="OpenACS Core Documentation"><link rel="up" href="tutorial-advanced.html" title="Chapter�9.�Advanced Topics"><link rel="previous" href="tutorial-notifications.html" title="Notifications"><link rel="next" href="tutorial-vuh.html" title="Using .vuh files for pretty urls"><link rel="stylesheet" href="openacs.css" type="text/css"></head><body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="navheader"><a href="http://openacs.org"><img src="/doc/images/alex.jpg" border="0" alt="Alex logo"></a><table width="100%" summary="Navigation header" border="0"><tr><td width="20%" align="left"><a accesskey="p" href="tutorial-notifications.html">Prev</a> </td><th width="60%" align="center">Chapter�9.�Advanced Topics</th><td width="20%" align="right"> <a accesskey="n" href="tutorial-vuh.html">Next</a></td></tr></table><hr></div><div class="sect1" lang="en"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a name="tutorial-hierarchical"></a>Hierarchical data</h2></div></div><div></div></div><div class="authorblurb"><p>by <a href="http://rubick.com:8002" target="_top">Jade Rubick</a>
      with help from many people in the OpenACS community</p>
          OpenACS docs are written by the named authors, and may be edited
          by OpenACS documentation staff.
        </div><p>One of the nice things about using the OpenACS object system
    is that it has a built-in facility for tracking hierarchical data
    in an efficient way. The algorithm behind this is called
    <tt class="computeroutput">tree_sortkey.</tt></p><p>Any time your tables are subclasses of the acs_objects
    table, then you automatically get the ability to structure them
    hierarchically. The way you do this is currently via the
    <tt class="computeroutput">context_id</tt> column of
    acs_objects (Note that there is talk of adding in a
    <tt class="computeroutput">parent_id</tt> column instead, because
    the use of <tt class="computeroutput">context_id</tt> has been
    ambiguous in the past). So when you want to build your hierarchy,
    simply set the context_id values. Then, when you want to make
    hierarchical queries, you can do them as follows:</p><pre class="programlisting">
      db_multirow categories blog_categories &quot;
      SELECT
      c.*,
      o.context_id,
      tree_level(o.tree_sortkey)
      FROM
      blog_categories c,
      acs_objects o
      WHERE
      c.category_id = o.object_id
      ORDER BY
      o.tree_sortkey&quot;
    </pre><p>Note the use of the
    <tt class="computeroutput">tree_level()</tt> function, which
    gives you the level, starting from 1, 2, 3... </p><p>Here's an example, pulling all of the children for a given
  parent:</p><pre class="programlisting">
      SELECT 
      children.*,
      tree_level(children.tree_sortkey) -
        tree_level(parent.tree_sortkey) as level
      FROM 
      some_table parent, 
      some_table children
      WHERE 
      children.tree_sortkey between parent.tree_sortkey and tree_right(parent.tree_sortkey)
      and parent.tree_sortkey &lt;&gt; children.tree_sortkey
      and parent.key = :the_parent_key;
      </pre><p>The reason we substract the parent's tree_level from the
    child's tree_level is that the tree_levels are global, so if you
    want the parent's tree_level to start with 0, you'll want the
    subtraction in there. This is a reason you'll commonly see magic
    numbers in tree_sortkey SQL queries, like
    <tt class="computeroutput">tree_level(children.tree_sortkey) -
    4</tt>. That is basically an incorrect way to do it,
    and subtracting the parent's tree_level is the preferred method.</p><p>This example does not include the parent. To return the entire subtree including the parent, leave out the non-equals clause:</p><pre class="programlisting">
      SELECT
      subtree.*,
      tree_level(subtree.tree_sortkey) -
        tree_level(parent.tree_sortkey) as level
      FROM some_table parent, some_table subtree
      WHERE 
      subtree.tree_sortkey between parent.tree_sortkey and tree_right(parent.tree_sortkey)
      and parent.key = :the_parent_key;
    </pre><p>If you are using the Content Repository, you get a similar
      facility, but the <tt class="computeroutput">parent_id</tt>
      column is already there. Note you can do joins with
      <tt class="computeroutput">tree_sortkey</tt>:</p><pre class="programlisting">
      SELECT
      p.item_id,
      repeat(:indent_pattern, (tree_level(p.tree_sortkey) - 5)* :indent_factor) as indent,
      p.parent_id as folder_id,
      p.project_name
      FROM pm_projectsx p, cr_items i
      WHERE p.project_id = i.live_revision
      ORDER BY i.tree_sortkey
    </pre><p>This rather long thread explains <a href="http://openacs.org/forums/message-view?message_id=16799" target="_top">How
    tree_sortkeys work</a> and this paper <a href="http://www.yafla.com/papers/sqlhierarchies/sqlhierarchies2.htm" target="_top">describes
    the technique for tree_sortkeys</a>, although the <a href="http://openacs.org/forums/message-view?message_id=112943" target="_top">OpenACS
    implementation has a few differences in the
    implementation</a>, to make it work for many languages and the
    LIKE construct in Postgres.
    </p></div><div class="navfooter"><hr><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="tutorial-notifications.html">Prev</a> </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right"> <a accesskey="n" href="tutorial-vuh.html">Next</a></td></tr><tr><td width="40%" align="left">Notifications </td><td width="20%" align="center"><a accesskey="u" href="tutorial-advanced.html">Up</a></td><td width="40%" align="right"> Using .vuh files for pretty urls</td></tr></table><hr><address><a href="mailto:docs@openacs.org">docs@openacs.org</a></address></div><a name="comments"></a><center><a href="http://openacs.org/doc/current/tutorial-hierarchical.html#comments">View comments on this page at openacs.org</a></center></body></html>