Many-to-many relation with attributes in refClass

Question

Hello, I'm currently designing a website, using symfony (1.2) with Doctrine as an ORM.

I have a Dinner class, a Criteria class, and a Mark class.

• A Mark is linked with a Dinner and a Criteria, and has private attributes, like DateOfMark, MarkValue, etc.
• Dinner and Criteria can have many Marks (or none).

As I use Doctrine, I define this model in my schema.yml, using the following code :

Dinner:
  columns:
    date: { type: timestamp, notnull: true }
    nb_presents: { type: integer, notnull: true }
  relations:
    Marks:
      class:    Criteria
      local:    dinner_id
      foreign:  criteria_id
      refClass: Mark

Criteria:
  columns:
    name: { type: string(50), notnull: true }
  relation:
    Marks:
      class:    Dinner
      local:    criteria_id
      foreign:  dinner_id
      refClass: Mark

Mark:
  columns:
    criteria_id: { type: integer, primary: true }
    dinner_id: { type: integer, primary: true }
    value: { type: integer, notnull: true }
  relations:
    Dinner:
      local:    dinner_id
      foreign:  id
    Criteria:
      local:    criteria_id
      foreign:  id

Problem is that the SQL generated by Doctrine, it adds a FOREIGN KEY CONSTRAINT on Mark.dinner_id to Dinner.id (which is correct) AND it adds a FOREIGN KEY CONSTRAINT on Dinner.id to Mark.dinner_id (which is really incorrect, as a Dinner might have many Marks).

Question

Did I miss something ? Am I doing this kind of relation between classes wrong ?

Thanks you.

Answer 1

You need to define the relation as being a one-to-many relation. Try this (note the "type: many" added to the Dinner and Criteria definitions):

Dinner:
  columns:
    date: { type: timestamp, notnull: true }
    nb_presents: { type: integer, notnull: true }
  relations:
    Marks:
      class:    Criteria
      local:    dinner_id
      foreign:  criteria_id
      refClass: Mark
      type: many

Criteria:
  columns:
    name: { type: string(50), notnull: true }
 relation:
   Marks:
     class:    Dinner
     local:    criteria_id
     foreign:  dinner_id
     refClass: Mark
     type: many

Mark:
  columns:
    criteria_id: { type: integer, primary: true }
    dinner_id: { type: integer, primary: true }
    value: { type: integer, notnull: true }
  relations:
    Dinner:
      local:    dinner_id
      foreign:  id
    Criteria:
      local:    criteria_id
      foreign:  id