sometimes not all the constraints are created for a many to many relation

I seem to have a very similar issue. It is really annoying because no error feedback is given at any point...
In my case, one out of three many-to-many relations actually has its constraints created, the other two not.
However, if I take the file date/sql/schema.sql (I am working with Symfony 1.4.4) and run as an SQL query directly with my database, the constraints are created properly!

So, with symfony, if I go:
$ symfony14 doctrine:build-model
$ symfony14 doctrine:build-sql
$ symfony14 doctrine:insert-sql
The model gets generated, the database gets generated, but several many-to-many constrains are missing.

Then if I go:
$ symfony14 doctrine:build-model
$ symfony14 doctrine:build-sql
$ mysql -u user -p database < date/sql/schema.sql
The model gets generated, the database gets generated, and the constraints are in place.

Hope this helps to have the problem fixed.

Cheers,
Piotrek

I do confirm this "alphabetical-order exporting" problem! (in 1.2 svn revision 7676)
(see also #DC-655)

*Edit:* I have gone closer through the functions calls and used a debugger, and the problem is actually logical;
it happens whenever a refClass of a many-to-many relation is parsed (and exported) before one or both of the linked classes.

Let's take this schema (YAML is shorter, anyways the generated PHP models are not bugged):

---
LinkTable:
  columns:
    table1_id:
      primary: true
      type:    integer
    table2_id:
      primary: true
      type:    integer
Table1:
  relations:
    Table2:
      refClass: LinkTable
      local:    table1_id
      foreign:  table2_id
Table2:
  relations:
    Table1:
      refClass: LinkTable
      local:    table2_id
      foreign:  table1_id

Now if you call Doctrine_Core::createTablesFromModels() or Doctrine_Core::generateSqlFromModels(), consequent call to Doctrine_Export::exportSortedClassesSql() does a loop and calls exportClassesSql() for each model in alphabetical order; once a table has been exported, subsequent bindings of pending relations won't be "retro-parsed" for the export.

I tried to compact the calls chain:

/* IN CLASS Doctrine_Export (or child (connection export)) */

$this->exportSortedClassesSql($classes)
{
    FOREACH ($classes as $class) {
        $connection = Doctrine_Manager::getInstance()->getConnectionForComponent($class);
        //...
        $sql =
        $connection->export->exportClassesSql($models = array($class))
        {
            FOREACH ($models as $name) {
                $record = new $name();
                $table = $record->getTable();
                //...
                $data = $table->getExportableFormat()
                {
                    /* IN CLASS Doctrine_Table (or child) */
                    
                    //...
                    $this->_parser->getRelations()
                    {
                        /* IN CLASS Doctrine_Relation_Parser (or child) */
                        
                        FOREACH ($this->_pending as $alias => $v) {
                            $this->getRelation($alias)
                            {
                                //...
                                /*
                                 * In the case of an "association relation" through a refTable:
                                 *   binds a *pending* (simple foreign key) relation to the refTable parser,
                                 *   binds a *pending* (simple foreign key) relation to itself,
                                 *   and "finalizes" (adds to $this->_relations) its association relation
                                 */
                            }
                        }
                    }
                    //...
                }
            }
        }
    }
}

So, with our example schema,

1. LinkTable is exported with no constraints (because has no relations at this time);
2. Table1 gets parsed, which finalizes the pending relation with Table2 (binded when loaded), binds to itself a pending relation with LinkTable, and binds to LinkTable parser the first pending relation (with Table1); Table1 is then exported whilst it still has a pending relation;
3. Table2 gets parsed and exported the same way.

So now we have 2 problems:

• each table has been exported immediately after its parsing, i.e. in an incomplete state (this is the present issue)
• even after the whole relations parsing loop, LinkTable has still 2 pending relations, and each of both other tables has one "final" and one pending (but maybe it is supposed to be so...)

Note that a second call to Doctrine_Core::generateSqlFromModels() just after the first one, will parse all models one more time and thus finalize those pending relations, generating all constraints (also will subsequent calls).

For now I don't have a fix but I think we could consider:

• loop twice in Doctrine_Export::exportSortedClassesSql() and exportClassesSql(), and construct SQL queries only the 2nd time (but... hum...)
• improve Doctrine_Relation_Parser? maybe explicitly call getRelations() after each bind() (in the getRelation() function)...

Anyway it seems clear that export shouldn't be done before all tables have been parsed.

G.X

I still have no fix but in the waiting we can still discuss some "user-side" work-arounds (that don't need modifying Doctrine source code)...
Here is what crosses my mind first:

• (in the PHP working code) do a first "dummy" call to

  $conn->export->exportClassesSql(Doctrine_Core::loadModels('models'))

  just to ensure all relations are bound, then a "real" call to Doctrine_Core::createTablesFromModels('models') which will export all constraints well

• (in the schema) rename LinkTable to something like ZzLinkTable so that it is exported last

• "explicitize" the two (one-to-many) foreign key relations in LinkTable by completing the schema as follows:
  schema.yml

  ---
  LinkTable:
    columns:
      table1_id:
        primary: true
        type:    integer
      table2_id:
        primary: true
        type:    integer
  #(added)
    relations:
      Table1:
        local:   table1_id
        foreign: id
      Table2:
        local:   table2_id
        foreign: id
  #(/added)
  Table1:
    relations:
      Table2:
        refClass: LinkTable
        local:    table1_id
        foreign:  table2_id
  Table2:
    relations:
      Table1:
        refClass: LinkTable
        local:    table2_id
        foreign:  table1_id
  

  (I think this one would also "solve" the behavior problem of Papp Richard)

But I'm not that happy with the idea of choosing one of these "hacks" if I had to, I would use this last one but (unless you use some "onDelete: CASCADE" or so) it looks like unnecessary duplication...
Someone, any other idea? ^^

G.X

(edit: typo fix)

We can try another approach too: let's say that our generated PHP models classes are incomplete.

Indeed, for a simple foreign key relation (i.e. o2o and o2m / m2o), we can define it only once on the owning side in the YAML file, and the schema importer will generate complete PHP classes, with the "hasOne" on the owning side and the auto-completed "hasMany" on the opposite side. E.g:

Phonenumber:
  columns:
    user_id: integer
  relations:
    User: {local: user_id, foreign: id}
User:
# we don't precise the opposite relation here

will generate the following classes (simplified a bit):

// models/Phonenumber.php
class Phonenumber extends Doctrine_Record
{
    public function setTableDefinition() {
        $this->hasColumn('user_id', 'integer');
    }

    public function setUp() {
        $this->hasOne('User', array('local'   => 'user_id',
                                    'foreign' => 'id'));
    }
}

// models/User.php
class User extends Doctrine_Record
{
    public function setTableDefinition() {
    }

    public function setUp() {
        // this was auto-added during the schema import:
        $this->hasMany('Phonenumber', array('local'   => 'id',
                                            'foreign' => 'user_id'));
    }
}

and this will work well.
BUT, now if we remove the auto-added hasMany() in User.php, then run the following (which worked well before our manual modification):

require_once 'bootstrap.php';

$user = new User();
Doctrine_Core::dump($user->Phonenumber);

we get some Fatal error: Uncaught exception 'Doctrine_Record_UnknownPropertyException' with message 'Unknown record property / related component "Phonenumber" on "User"', which is supposed to occur. Indeed, how could Doctrine know that a User has a "link" with Phonenumber? It would be only possible by parsing the relations of all the (loaded) models first...

So we can say that YAML schema files can be incomplete whereas PHP models classes MUST be COMPLETE.

Now, considering it is not to change, why not apply this "principle" to (m2m) association relations?
i.e., Doctrine documentation could warn that, if you want to have all constraints properly exported (like in the present issue) and/or use relations from an association class (having $link instance of LinkTable, you want to use $link->Table1, not just $link->table1_id), then you MUST complete your PHP models with the FK relations, like this:

// models/LinkTable.php
class LinkTable extends Doctrine_Record
{
    public function setTableDefinition() {
        $this->hasColumn('table1_id', 'integer', null, array(
                'primary' => true));
        $this->hasColumn('table2_id', 'integer', null, array(
                'primary' => true));
    }

    public function setUp() {
        // ADD:
        $this->hasOne('Table1', array('local'   => 'table1_id',
                                      'foreign' => 'id'));
        $this->hasOne('Table2', array('local'   => 'table2_id',
                                      'foreign' => 'id'));
    }
}

// models/Table1.php
class Table1 extends Doctrine_Record
{
    public function setTableDefinition() {
    }

    public function setUp() {
        $this->hasMany('Table2', array('refClass' => 'LinkTable',
                                       'local'    => 'table1_id',
                                       'foreign'  => 'table2_id'));
        // add too?:
        $this->hasMany('LinkTable', array('local'    => 'id',
                                          'foreign'  => 'table1_id'));
    }
}

// models/Table2.php
class Table2 extends Doctrine_Record
{
    public function setTableDefinition() {
    }

    public function setUp() {
        $this->hasMany('Table1', array('refClass' => 'LinkTable',
                                       'local'    => 'table2_id',
                                       'foreign'  => 'table1_id'));
        // add too?:
        $this->hasMany('LinkTable', array('local'    => 'id',
                                          'foreign'  => 'table2_id'));
    }
}

After all, these additional relations are bound internally (if not already defined) when exporting tables to DB...

We also said that the YAML can be incomplete, but for now the short schema.yml I took as an example in my first comment results in PHP classes without those (2+1+1) adds.

So to allow YAML to be incomplete even for m2m relations, Doctrine_Import_Schema#_buildRelationships() would need some "improvement" so that it creates the many-to-one relation for each many-to-many relation (and the opposite one-to-many relation, on the association class side i.e. the owning side, would be auto-completed afterwards). However, a m2m relation is allowed to be defined on one single end, thus may be created one the other end only when _autoCompleteOppositeRelations() is called, i.e. after _buildRelationships() has looped over all the existing relations. So Doctrine_Import_Schema#_autoCompleteOppositeRelations() would need the same kind of improvement...

In fact I'm not sure of what is a "well defined" model as soon as it uses refClass, the documentation sometimes uses contradictory examples...

Sorry I was so verbose!
Thank you for developing Doctrine