[doctrine 1.x] Many to many relation on the same table

Question

Hi!

I'm trying to setup a m2m relation between 'Gabarits'.

• One gabarit can have many Gabarits ( called Options )
• One gabarit can come from many Gabarits ( called OptionsFrom )

Here is my schema.yml:

Gabarit:
  actAs: [Attachable]
  columns:
    libelle: { type: string, size: 255 }
    description: { type: clob }
  relations:
    Options:
      class: Gabarit
      refClass: Gabarit2Gabarit
      local: gabarit_id
      foreign: fils_id
      foreignAlias: OptionsFrom

Gabarit2Gabarit:
  columns:
    fils_id: { type: integer, primary: true }
    gabarit_id: { type: integer, primary: true }
  relations:
    Gabarit:
      class: Gabarit
      local: gabarit_id
      alias: Gabarit
      foreignAlias: Gabarit2Gabarits
    Fils:
      class: Gabarit
      local: fils_id
      alias: Fils
      foreignAlias: Gabarit2Gabarits

Generated code is:

   $this->hasMany('Gabarit as Options', array(
         'refClass' => 'Gabarit2Gabarit',
         'local' => 'gabarit_id',
         'foreign' => 'fils_id'));

    $this->hasMany('Gabarit as OptionsFrom', array(
         'refClass' => 'Gabarit2Gabarit',
         'local' => 'fils_id',
         'foreign' => 'gabarit_id'));

It seems good, according to http://www.doctrine-project.org/projects/orm/1.2/docs/manual/defining-models/en#relationships:join-table-associations:self-referencing-nest-relations:non-equal-nest-relations

Logically, it would give me this:

foreach($gabarit_>getOptions() as $option)
{
    in_array($gabarit->getId(), $options->getOptionsFrom()->getPrimaryKeys()); // should be true but returns false !!
}

Generated SQL for getOptions() :

SELECT gabarit.id AS gabarit__id, gabarit.libelle AS gabarit__libelle, gabarit.description AS gabarit__description, gabarit2_gabarit.fils_id AS gabarit2_gabarit__fils_id, gabarit2_gabarit.gabarit_id AS gabarit2_gabarit__gabarit_id FROM gabarit INNER JOIN gabarit2_gabarit ON gabarit.id = gabarit2_gabarit.fils_id WHERE gabarit.id IN (SELECT fils_id FROM gabarit2_gabarit WHERE gabarit_id = '507') ORDER BY gabarit.id ASC

Generated SQL for getOptionsFrom() :

SELECT gabarit.id AS gabarit__id, gabarit.libelle AS gabarit__libelle, gabarit.description AS gabarit__description, gabarit2_gabarit.fils_id AS gabarit2_gabarit__fils_id, gabarit2_gabarit.gabarit_id AS gabarit2_gabarit__gabarit_id FROM gabarit INNER JOIN gabarit2_gabarit ON gabarit.id = gabarit2_gabarit.gabarit_id WHERE gabarit.id IN (SELECT gabarit_id FROM gabarit2_gabarit WHERE fils_id = '529') ORDER BY gabarit.id ASC

Do you have any idea why Gabarit::getOptionsFrom returns an empty collection ?

thanks in advance, Florian.

Answer 1

It will most probably be the fact that you are using the same alias

foreignAlias: Gabarit2Gabarits

Your Gabarit2Gabarit should be like this:

Gabarit2Gabarit:
  columns:
    fils_id: { type: integer, primary: true }
    gabarit_id: { type: integer, primary: true }
  relations:
    Gabarit:
      class: Gabarit
      local: gabarit_id
      alias: Gabarit
      foreignAlias: Gabarit2GabaritsGabarit
    Fils:
      class: Gabarit
      local: fils_id
      alias: Fils
      foreignAlias: Gabarit2GabaritsFils

That might help?