12월 사수자리

What I am going to build today is adjacent list. Data with hierarchic structure is very common. Today's output will looks like the image below. I, fisrtly, wanted to make a sortable and inplace editable list, but it was way too difficult. I am still learning how symfony is working. Today's work took whole my morning plus three hours. So I will do it later if I have some free time. (Anyway, symfony's in_place_edit_tag wrapper does not fully support what I want)


1. Project preparation
# symfony init-project sandbox
# symfony init-app item
# mysqladmin -u root create item
# modify config/database.yml, config/propel.ini

2. Writing Schema and CRUD generation

# config/schema.yml
propel:
  item:
    _attributes: { phpName: Item }
    id:
    parent_id:    integer
    thread:       integer
    depth:       { type: tinyint, default: 0 }
    title:        varchar(255)
    description:  longvarchar
    _foreign_keys:
      - self_joined_keys
        foreign_table: item
        on_delete: cascade
        references:
          - { local: parent_id, foreign: id }

# symfony propel-build-model
# symfony propel-build-sql
# symfony propel-insert-sql
# symfony propel-generate-crud item item Item (app name, module name, and model name, respectively)


3. Testdata

# data/fixtures/testdata.yml
Item:
  first:
    title: first item
    description: first list item
    depth: 0
  second:
    title: second item
    description: second list item
    depth: 0
  third:
    title: third item
    description: child of first item
    parent_id: first
  fourth:
    title: fourth item
    description: child of first item
    parent_id: first
  fifth:
    title: fifth item
    description: child of third item
    parent_id: third
  sixth:
    title: sixth item
    description: child of fifth item
    parent_id: fifth
  seventh:
    title: seventh item
    description: child of fourth item
    parent_id: fourth

# symfony propel-load-data item

4. Handle child nodes
To register a child node, we will make a link in `showSuccess.php`

# apps/item/modules/item/templates/showSuccess.php
<?php echo link_to('create child', 'item/create?parent='.$item->getId()) ?>

`item/create` action checks request parameter whether a `parent` parameter exists, and if it exists, the action will fetch the parent object. (We don't need parent information when a item is edited.)

# apps/item/modules/item/action/action.class.php
public function executeCreate ()
{
  $this->item = new Item();
   
  $this->parent = ItemPeer::retrieveByPk($this->getRequestParameter('parent'));

  $this->setTemplate('edit');
}

public function executeEdit ()
{
  $this->item = ItemPeer::retrieveByPk($this->getRequestParameter('id'));

  $this->parent = null;
   
  $this->forward404Unless($this->item);
}

Open and modify `editSuccess.php` template. We don't need to display thread number or depth number in this form, so remove them. And only if when a user is adding a child node, parent_id is needed.

# apps/item/modules/item/templates/showSuccess.php
<tbody>
<?php if ($parent): ?>
<tr>
  <th>Parent:</th>
  <td>
<?php echo object_input_tag($item, 'getParentId', array (
  'type' => 'hidden',
), $parent->getId()) ?>
    <?php echo $parent->getTitle() ?>
  </td>
</tr>
<?php endif; ?>
<tr>
  <th>Title:</th>
  <td><?php echo object_input_tag($item, 'getTitle', array (
  'size' => 80,
)) ?></td>
</tr>
<tr>
  <th>Description:</th>
  <td><?php echo object_textarea_tag($item, 'getDescription', array (
  'size' => '30x3',
)) ?></td>
</tr>
</tbody>

5. Override Item model
Now everything is prepared for child nodes registering. Now we need some codes actually save child nodes and set parent_id, thread, and depth for them. Let's override `Item` class.

# lib/model/Item.php
public function save($con = null) {
  // New records need to be initialized with same parents and thread number
  if (!$this->getId())
  {
    $con = Propel::getConnection(ItemPeer::DATABASE_NAME);
    try
    {
      $con->begin();
      
      parent::save();
      
      // Insert a record in a existing tree
      if ($this->parent_id)
      {
        $parent = ItemPeer::retrieveByPk($this->parent_id);
        $this->setParentId($parent->getId());
        $this->setThread($parent->getThread());
        $this->setDepth($parent->getDepth()+1);
      }
      else
      {
        $this->setParentId($this->getId());
        $this->setThread($this->getId());
      }
      parent::save();
      
      $con->commit();
    }
    catch (Exception $e)
    {
      $con->rollback();
      throw $e;
    }
  }
  else
  {
    parent::save();
  }
}

public function delete($con = null) {
  if ($this->parent_id != $this->getId())
  {
    $con = Propel::getConnection(ItemPeer::DATABASE_NAME);
    try
    {
      $con->begin();
      
      // Remove cascading records
      $delete = new Criteria();
      $delete->add(ItemPeer::PARENT_ID, $this->getId());
      BasePeer::doDelete($delete, $con);
     
      parent::delete();

      $con->commit();
    }
    catch (Exception $e)
    {
      $con->rollback();
      throw $e;
    }
  }
  else
  {
    parent::delete();
  }
}

Based on Propel Guide^[각주:1], it supports cascading delete. But I am not sure what will happen, so I just wrote some code for cascading delete.

Now Let's test it using testdata.

# symfony propel-load-data item

Now our list has a basic representation of hierarchic structure. However, how can we display them as a nested list?

6. Implement Adjacent List
Now it's time to implement adjacent list. I will use an algorithm from the article "Storing Hierarchical Data in a Database^[각주:2]. Two images from the same article will be helpful to understand what is it and how to implement it.


First, we need a left and right field in our data model. Since 'left' and 'right' are one of mysql keywords, I will use 'lft' and 'rgt' instead.

# config/schema.yml
propel:
  item:
    _attributes: { phpName: Item }
    id:
    parent_id:    integer
    thread:       integer
    depth:       { type: tinyint, default: 0 }
    title:        varchar(255)
    description:  longvarchar
    lft:         { type: integer, default: 1 }
    rgt:         { type: integer, default: 2 }
    _foreign_keys:
      - self_joined_keys
        foreign_table: item
        on_delete: cascade
        references:
          - { local: parent_id, foreign: id }

# symfony propel-build-all (It will do 'propel-build-model', 'propel-build-sql', and 'propel-insert-sql')

Then, let's modify `Item` model, so that the model can insert and remove dangled nodes.

# lib/model/Item.php#->save() Method
        if ($this->parent_id)
        {
          $parent = ItemPeer::retrieveByPk($this->parent_id);
          $this->setParentId($parent->getId());
          $this->setThread($parent->getThread());
          $this->setDepth($parent->getDepth()+1);
          // Last in, First out
          $this->setLft($parent->getLft()+1);
          $this->setRgt($this->getLft()+1);
         
          // Last in, Last out
          // $this->setLft($parent->getRgt());
          // $this->setRgt($this->getLft()+1);
         
          $sql = "UPDATE ".ItemPeer::TABLE_NAME." SET ".ItemPeer::RGT." = ".ItemPeer::RGT." + 2 WHERE ".ItemPeer::THREAD." = ".$this->getThread()." AND ".ItemPeer::RGT." >= ". $this->getLft();
          $stmt = $con->prepareStatement($sql);
          $stmt->executeUpdate();
          $stmt->close();
         
          $sql = "UPDATE ".ItemPeer::TABLE_NAME." SET ".ItemPeer::LFT." = ".ItemPeer::LFT." + 2 WHERE ".ItemPeer::THREAD." = ".$this->getThread()." AND ".ItemPeer::LFT." >= ". $this->getLft();
          $stmt = $con->prepareStatement($sql);
          $stmt->executeUpdate();
          $stmt->close();
        }

# lib/model/Item.php#->save() Method    
      try
      {
        $con->begin();
       
        // Remove cascading records
        $delete = new Criteria();
        $delete->add(ItemPeer::LFT, $this->getLft(), Criteria::GREATER_THAN);
        $delete->addAnd(ItemPeer::LFT, $this->getRgt(), Criteria::LESS_THAN);
        BasePeer::doDelete($delete, $con);

        $cascading_records = $this->getRgt() - $this->getLft() + 1;
       
        $sql = "UPDATE ".ItemPeer::TABLE_NAME." SET ".ItemPeer::RGT." = ".ItemPeer::RGT." - ".$cascading_records." WHERE ".ItemPeer::THREAD." = ".$this->getThread()." AND ".ItemPeer::RGT." >= ". $this->getLft();
        $stmt = $con->prepareStatement($sql);
        $stmt->executeUpdate();
        $stmt->close();
       
        $sql = "UPDATE ".ItemPeer::TABLE_NAME." SET ".ItemPeer::LFT." = ".ItemPeer::LFT." - ".$cascading_records." WHERE ".ItemPeer::THREAD." = ".$this->getThread()." AND ".ItemPeer::LFT." >= ". $this->getLft();
        $stmt = $con->prepareStatement($sql);
        $stmt->executeUpdate();
        $stmt->close();
       
        parent::delete();

        $con->commit();
      }

Yes. We don't have any elegant method to wrap an update query like "UPDATE item SET lft = lft + 2 WHERE ...". I thought I could you Criteria::CUSTOM^[각주:3], but it turned out I can't use it for an update query. Maybe Doctrine supports it. I will check them out later.

Then, we need a method to fetching those items in certain order.

# lib/model/ItemPeer.php
  static function getAllByThread()
  {
    $c = new Criteria;
    $c->addDescendingOrderByColumn(self::THREAD);
    $c->addAscendingOrderByColumn(self::LFT);
    return self::doSelect($c);
  }

# apps/item/modules/item/action/action.class.php
  public function executeList ()
  {
    $this->items = ItemPeer::getAllByThread();
  }

Now let's test our modification.

# symfony propel-load-data item

# item/list action

7. Validating form


8. Other References

1. How to make sortable lists^[각주:4]
2. Symfony model^[각주:5]