Two-Table Address List Model

Question

So, I think my question comes down to two questions:

• How to create an end-to-end tree structure in PHP when the tree is stored in MySQL (between two tables) using the Adjacency address list model approach while maintaining performance?

• What is the supported approach to displaying the tree in the required formats without duplicating the code for traversing and attenuating logic using if / else and switch statements?

See below for details:

I am using Zend Framework.

I work with a profile. It is stored in a MySQL database between two separate tables: questions and question_groups. Each table extends the corresponding Zend_Db_Table_ * classes. The hierarchy is represented using the Adjacency address list model approach.

I understand that the problems that I encounter are probably related to the fact that I am building a tree structure in the DBMS, so I am open to alternatives. However, I also keep respondent questionnaires and their answers, so alternative approaches would have to support this.

The questionnaire should be displayed in various HTML formats:

• As a form for entering responses (using Zend_Form)
• As an ordered list (nested) with questions (and some groups) as links for viewing answers to questions or groups.
• As an ordered list (nested) with answers added to each question.

Questions are leaf nodes, and question groups can contain other questions_groups and / or questions. In combination, there are more than 100 lines for processing and display.

, question_group (, UNION : QuestionGroup:: getChildren ($ id)). .

, . node .

recursion-less , UNION, . , , , node - , . , - ...

. . . "", "" PHP SPL, , , Zend_Db_Table, Zend_Db_Table_Rowset Zend_Db_Table_Row. , . ( ) .

Answer 1

• Adjacency List parent_id , . parent_id NULL, . SQL-, .

• root_id, , . , SQL-. Table, Rowset .

  class QuestionGroups extends Zend_Db_Table_Abstract
  {
      protected $_rowClass = 'QuestionGroup';
      protected $_rowsetClass = 'QuestionGroupSet';
      protected function fetchTreeByRootId($root_id)
      {
           $rowset = $this->fetchAll($this
              ->select()
              ->where('root_id = ?', $root_id)
              ->order('id');
          );
          $rowset->initTree();
          return $rowset;
      }
  }
  

• , Zend_Db_Table_Row, , Rowset . Row . Row getLevel() getAncestorsRowset() .

  class QuestionGroup extends Zend_Db_Table_Row_Abstract
  {
      protected $_children = array();
      protected $_parent   = null;
      protected $_level    = null;
      public function setParent(Zend_Db_Table_Row_Abstract $parent)
      {
          $this->_parent = $parent;
      }
      public function getParent()
      {
          return $this->_parent;
      }
      public function addChild(Zend_Db_Table_Row_Abstract $child)
      {
          $this->_children[] = $child;
      }
      public function getChildren()
      {
          return $this->_children;
      }
      public function getLevel() {}
      public function getAncestors() {}
  }
  

• , Zend_Db_Table_Rowset, , , . , Rowset getRootRow().

  class QuestionGroupSet extends Zend_Db_Table_Rowset_Abstract
  {
      protected $_root = null;
      protected function getRootRow()
      {
          return $this->_root;
      }
      public function initTree()
      {
          $rows = array();
          $children = array();
          foreach ($this as $row) {
            $rows[$row->id] = $row;
            if ($row->parent_id) {
              $row->setParent($rows[$row->parent_id]);
              $rows[$row->parent_id]->addChild($row);
            } else {
              $this->_root = $row;
            }
          }
      }
  }
  

getRootRow() , node. node, getChildren() . getChildren() , .