Doctrine Many To Many With Extra Fields

Recently I have started using Doctrine with Zend Framework. Most of the time it is great, but sometimes I get stuck on this or that issue. Most of my problems so far have been connected with the Many to Many relationship. Here are a few tips I learned the hard way.

Automatic relationship detection

When setting up a YAML file with your database schema you can start it with the handy declaration:

---
detect_relations: true```

It does the One to Many relationship for you nicely, however the Many to Many ones did not work out of the box and required manual BaseModel tweaking. Take for example my **Tag**, **Website**, and **WebsiteTag** classes.

### BaseTag:

abstract class BaseTag extends Doctrine_Record {

public function setTableDefinition() {
$this->setTableName('ts_tags');
$this->hasColumn('name', 'string', 128, array('type' => 'string', 'length' => '128'));
$this->hasColumn('is_category as isCategory', 'integer', 1, array('type' => 'integer', 'length' => '1'));
$this->hasColumn('page_id', 'integer', 8, array('type' => 'integer', 'length' => 8));
$this->hasColumn('ordering', 'integer', 5, array('type' => 'integer', 'length' => '5'));
}

public function setUp() {
$this->hasOne('Page', array('local' => 'page_id',
'foreign' => 'id'));

$this->hasMany('Website as Websites', array('local' => 'tag_id',
'foreign' => 'website_id',
'refClass' => 'WebsiteTag'));

$i18n0 = new Doctrine_Template_I18n(array('fields' => array(0 => 'name')));
$this->actAs($i18n0);

}
}```

BaseWebsite:

abstract class BaseWebsite extends Doctrine_Record{
  public function setTableDefinition() {
    $this->setTableName('ts_websites');
    $this->hasColumn('page_id', 'integer', 8, array('type' => 'integer', 'length' => 8));
    $this->hasColumn('title', 'string', 128, array('type' => 'string', 'length' => '128'));
    $this->hasColumn('client', 'string', 128, array('type' => 'string', 'length' => '128'));
    $this->hasColumn('link', 'string', 255, array('type' => 'string', 'length' => '255'));
    $this->hasColumn('intro', 'string', null, array('type' => 'string'));
    $this->hasColumn('content', 'string', null, array('type' => 'string'));
    $this->hasColumn('published', 'timestamp', null, array('type' => 'timestamp'));
    $this->hasColumn('is_home as isHome', 'integer', 1, array('type' => 'integer', 'length' => '1'));
  }

  public function setUp() {
    $this->hasOne('Page', array('local' => 'page_id',
                                              'foreign' => 'id'));

    <strong>$this->hasMany('Tag as Tags', array('local' => 'website_id',
                                                          'foreign' => 'tag_id',
                                                          'refClass' => 'WebsiteTag'));</strong>

    $this->hasMany('Media', array('local' => 'website_id',
                                                 'foreign' => 'media_id',
                                                 'refClass' => 'WebsiteMedia'));

    $i18n0 = new Doctrine_Template_I18n(array('fields' => array(0 => 'intro', 1 => 'content', 2 => 'title')));
    $this->actAs($i18n0);
  }
}

BaseWebsiteTag:

abstract class BaseWebsiteTag extends Doctrine_Record {

  public function setTableDefinition() {
    $this->setTableName('ts_website_has_tags');
    $this->hasColumn('tag_id', 'integer', 8, array('type' => 'integer', 'length' => 8, <strong>'primary' => true )</strong>);
    $this->hasColumn('website_id', 'integer', 8, array('type' => 'integer', 'length' => 8,<strong> 'primary' => true</strong>));
  }

  public function setUp() {
  }
}

The highlighted portions of code needed to be added. You can actually define these manually in the YAML file, the catch is that I was expecting it to happen magically with the auto relationship setting.

Many to Many with extra fields

Another little issue that Doctrine has is when you try to retrieve records. It in a way skips the joining table and retrieves a collection of the final items. In most cases this is exactly what you might need. However for my Media I decided to keep the ordering in the joining table – WebsiteMedia.

$q = Doctrine_Query::create()
  ->from('Website w')
  ->joinLeft('w.Media');```

If you simply proceed with the above you only get Media objects without the ordering.

### BaseWebsiteMedia:

abstract class BaseWebsiteMedia extends Doctrine_Record {

public function setTableDefinition() {
    $this->setTableName('ts_website_has_media');
    $this->hasColumn('website_id', 'integer', 8, array('type' => 'integer', 'length' => 8, 'primary' => true));
    $this->hasColumn('media_id', 'integer', 8, array('type' => 'integer', 'length' => 8, 'primary' => true));
    $this->hasColumn('caption', 'string', 255, array('type' => 'string', 'length' => '255'));
    $this->hasColumn('is_main as isMain', 'integer', 1, array('type' => 'integer', 'length' => '1'));
    $this->hasColumn('ordering', 'integer', 4, array('type' => 'integer', 'length' => '4'));
}

public function setUp() {
    <strong>$this->hasOne('Website', array('local' => 'website_id',
                                   'foreign' => 'id'));

    $this->hasOne('Media', array('local' => 'media_id',
                                 'foreign' => 'id'));</strong>

    $i18n0 = new Doctrine_Template_I18n(array('fields' => array(0 => 'caption')));
    $this->actAs($i18n0);
}

}```

If you do not have the additional One to Many relations declared already you should add them now. With the modified model now you can get the records like so:

$q = Doctrine_Query::create()
  ->from('Website w')
  ->leftJoin('w.WebsiteMedia wm')
    ->leftJoin('wm.Media m');

This gives you a collection of WebsiteMedia records each containing the actual Media item.