title: Adjacency List Model with two tables
body: <p>So I <em>think</em> my issue boils down to two questions:</p>
  
  <ol>
  <li><p>How do I build a traversable tree structure in PHP when the tree is stored in MySQL (between two tables) using the Adjacency List Model approach while keeping performance in mind?</p></li>
  <li><p>What's a maintainable approach to displaying the tree in the needed formats without duplicating traversal code and littering the logic with if/else and switch statements?</p></li>
  </ol>
  
  <p>Below are more details:</p>
  
  <p>I'm using the Zend Framework.</p>
  
  <p>I'm working with a questionnaire. It's stored in a MySQL database between two separate tables: questions and question_groups. Each table extends the appropriate Zend_Db_Table_* classes. The hierarchy is represented using the Adjacency List Model approach.</p>
  
  <p>I realize the problems I'm running into are likely due to the fact that I'm stuffing a tree structure into an RDBMS so I'm open to alternatives. However, I'm also storing questionnaire respondents and their responses so alternative approaches would need to support that.</p>
  
  <p>The questionnaire needs to be displayed in various HTML formats:</p>
  
  <ol>
  <li>As a form for entering responses (using Zend_Form)</li>
  <li>As an ordered list (nested) with questions (and <em>some</em> groups) as links to view responses by question or by group.</li>
  <li>As an ordered list (nested) with responses appended to each question.</li>
  </ol>
  
  <p>Questions are leaf nodes and question_groups can contain other question_groups and/or questions. Combined, there are a little over 100 rows to process and display.</p>
  
  <p>Currently, I have a view helper that does all the processing using recursion to retrieve a question_group's children (a query which performs a UNION between the two tables: QuestionGroup::getChildren($id)). Plus when displaying questionnaire with the question response an additional two queries are needed to retrieve the respondent and their response to each question.</p>
  
  <p>While the page load time isn't very long this approach feels wrong. Recursion plus multiple database queries for almost every node does not make me feel very warm and fuzzy inside.</p>
  
  <p>I've tried <a href="http://blog.jdbartlett.com/2008/01/php-data-hierarchies-now-with-100-more.html" rel="nofollow noreferrer">recursion-less</a> and recursive methods on the full tree array returned from the UNION to build a hierarchical array to traverse and display. However, that seems to break down since there are duplicated node ids due to the fact that groups and questions are stored in separate tables. Maybe I'm missing something there...</p>
  
  <p>Currently, the logic to display the tree in the formats listed above is quite a mess. I'd prefer not to duplicate the traversal logic all over the place. However, conditionals all over the place don't produce the most easily maintainable code either. I've read up on Visitors, Decorators and some of the PHP SPL iterators but I'm still feeling unclear as to how that would all work together with the classes that are extending Zend_Db_Table, Zend_Db_Table_Rowset and Zend_Db_Table_Row. Especially since I haven't solved the previous problem of building the hierarchy from the database. It would be nice to add new display formats (or modify existing ones) somewhat easily.</p>