Symfony and Doctrine Templates - Setting up a one-to-many relationship

In my DB schema I have several tables that will be related to a parent table. The "ugly" way to solve the relation stuff would be to include the dependencies manually in the schema:

sfArea:
  columns:
    id: integer
    name: string

sfCity:
  columns:
    name: string        
    area_id: integer
  relations:
    Area:
      class: sfArea
      local: area_id
      foreignType: many
      foreignAlias: Cities

sfItem:
  columns:
    name: string
    area_id: integer
  relations:
    Area:
      class: sfArea
      local: area_id
      foreignType: many
      foreignAlias: Items

However, each time that I will add a class to be attached to开发者_C百科 the area, I will need to add the relation and all the lines that go with it (copy/paste => future hell). This is where I decided to use the Doctrine_Template, which allows me to achieve the same thing like that:

sfArea:
  columns:
    id: integer
    name: string

sfCity:
  actAs:
    AreaRelated: { foreignAlias: Cities }
  columns:
    name: string        

sfItem:
  actAs:
    AreaRelated: { foreignAlias: Items }
  columns:
    name: string

And the template class:

class AreaRelated extends Doctrine_Template
{
    protected $_options = array(
        'foreignAlias'  =>  ''
    );

    public function setTableDefinition()
    {
        $this->hasColumn('area_id', 'integer');
    }

    public function setUp()
    {
        $this->hasOne('sfArea as Area', array(
                'local' => 'area_id',
                'foreign' => 'id',
                'foreignType' => 'many',
                'foreignAlias' => $this->_options['foreignAlias']
            )
        );
    }
}

The tables are generated properly and the relation works in the direction $sfCity->Area. However, the relations that should be set up in the sfArea class are not created ($sf_area->Cities give the error "Unknown record property / related component "Cities" on "sfArea"").

How can the other relation be created? I even tried that (without success):

//...
public function setUp()
{
    $thisTable = $this->_table;
    $areaTable = Doctrine::getTable("smArea");

    $thisTable->hasOne('smArea as Area', array(
            'local' => 'area_id',
            'foreign' => 'id',
            'foreignType' => Doctrine_Relation::MANY
        )
    );        

    $areaTable->hasMany($thisTable->getOption('name') . ' as ' . $this->_options['foreignAlias'], array(
            'local' => 'id',
            'foreign' => 'area_id',
            'foreignType' => Doctrine_Relation::ONE
        )
    );             
}

Unfortunately, I don't think there's any way to do this without at least some extra definitions*. One way to do this that would require minimal additions would be to enumerate the relations on the Area via an option:

Area:
  options:
    models_with_areas: [Cities, Items]

Then attach the relations in Area::setUp

public function setUp()
{
  parent::setUp();
  $models = $this->getTable()->getOption('models_with_areas');
  foreach($models as $model)
  {
    $this->hasMany($model, array(
         'local' => 'id',
         'foreign' => Doctrine_Inflector::tablize($model) . '_id'
    ));
  }
}

Of course, such an approach is inflexible and if you want significantly more complex logic or options, this doesn't work so well. You could always define the relations only on the Area but continue to use behaviors for the many side of the relations.

Contrary to some other answers, I do think that this is a sound approach and potentially a good idea. A central tenet of the Symfony/Doctrine philosophy is Don't Repeat Yourself. This solution adheres to that idea. It also provides significant benefits if there is logic shared between classes that are AreaRelated.

*Except one ugly one: you could iterate through every table and find any table that has the template AreaRelated. This would require instantiating every table every time an Area record is loaded which sounds like an absolutely terrible idea.

So far, for the record, my best guess is to do it the manual way in the schema file. As the schema changes rarely as mentionned by Nathan, I think this is the best way to do it if the Behaviour cannot be implemented (which I'll keep trying to do as a side-project). The benefit of the Behaviour approach being that it can generate the relations to a particular very central object automatically and consistently across all the objects that depend on this central object.

Another useful example of where this could be of use, is a multi-site blog engine for instance, where all posts, users, categories, media uploads, ... are to be related to a particular blog.

sfArea:
  columns:
    id: integer
    name: string

sfCity:
  columns:
    name: string        
    area_id: integer
  relations:
    Area:
      class: sfArea
      local: area_id
      foreignType: many
      foreignAlias: Cities

sfItem:
  columns:
    name: string
    area_id: integer
  relations:
    Area:
      class: sfArea
      local: area_id
      foreignType: many
      foreignAlias: Items

I think you have a big model issue, representing this kind of hirearchy on a database only will get you to lots of headaches. I'm doing some mantainence for a major site that has something like that and is hell.

My advise is, think things over and re-analize if the hirearchy is really needed to be in the database or maybe you are thinking in objects while modeling a Relational database.

With the information you gave in the example, City and seems like "Categories" so if you put them in a reference table an area could be categorized, minimizing code replication. A category may have other relations or just be a big table with lots of fields.

So here is the kind of model i suggest (propel):

Area:
  tableName: area_table
  description: Area
  columns:
    id:
    name:
    category_id:
      type: integer
      foreignClass: Category
      foreignReference: id
      required: true

Category
  tableName: categor_table
  description: An area specialization
  columns:
    id:
    model_class_name:
      type: varchar(255)
      description: the model Class that will represent this piece of information
    field1:
    field2:
    relation:1

Using mode_class_name as a hint to know what to do with raw data, you could do something like:

CategoryClass >>
 public function getObjectRepresentation()
 {
   return new $this->getModelClassName()($this);
 }
[...]

RealObjectRepresentation1 >>
 public function __construct(Category $category)
 {
   //Initialize proper object using category information
 }

So this way you have moved DatabaseHirearchy to PhpObjectHirearchy

Hope this helps in some way, if you need more details i'd be happy to help!