Quick Doctrine NestedSet reference

I discovered Doctrine’s NestedSet behavior this week. I wasn’t able to find any resource on the Internet which would summarize how to create schema and fixture all together. So let me share with you this small reference article, maybe it will spare you some minutes.

First on your schema.yml

# let's say you got an Item table, with some subitems
Item:
  actAs:
    NestedSet:
      hasManyRoots: true # it means there can be several "main" items. If set to false, there is one main item, and all others subitems are bounded to this first one
      rootColumnName: item_id # Speaks for itself, that's where the relation between items will be stored
  columns:
    name: { type: string } # let's give our item a name
# You can leave it like this, but I added some relations, so i can fetch one item's subitems easily
    item_id: { type: integer, default: null }
  relations:
    Item:
      local: item_id
      foreign: id
      foreignAlias: Subitems

This schema will build your model with 3 extra fields : rgt, lft and level. Level is 0 if the record is root. Rgt and Lft are filled according to this behavior : Nested Set Model.

Now we got this, let’s create some fixtures :

Item:
  item_1:
    name: First item
    children: [] # It means for now, it has no subitems. You have to specify it, otherwise you're doctrine:build-all commands will fail
  item_2:
    name: Second item
    children:
      item_2a:
        name: Second item, First subitem
      item_2b:
        name: Second item, Second subitem
        children:
          ...

So now, instead of calling for example a foreach($item->getSubitems() as $subitem) which will occur in an extra query to the database if no Subitems exist (even if you have leftJoined your relations), you can test : if($item->getRgt() – $item->getLft() > 1). If the difference is greater than one, the current $item got subitems children.

Hope it will help.

« Pre-fill a form in the backend with symfony

Symfony, MAMP, PhpMyAdmin, password changed… »