One to two relationship in Doctrine with YAML

Question

I'm working on my first Symfony project with Doctrine, and I've run into a hitch. I'm trying to express a game with two players. The relationship I want to have is PlayerOne and PlayerTwo each being keyed to an ID in the Users table. This is part of what I've got so far:

Game:
  actAs: { Timestampable:- }
  columns:
    id: { type: integer,  notnull: true, unique: true }
    startDate: { type: timestamp, notnull: true }
    playerOne: { type: integer, notnull: true }
    playerTwo: { type: integer, notnull: true }
    winner: { type: integer, notnull:true, default:0 }
  relations:
    User: { onUpdate: cascade, local: playerOne, foreign: id}
    User: { onUpdate: cascade, local: playerTwo, foreign: id}

That doesn't work. It builds fine, but the SQL it generates only includes a constraint for playerTwo. I've tried a few other things:

User: { onUpdate: cascade, local: [playerOne, playerTwo], foreign: id}

Also:

    User: [{ onUpdate: cascade, local: playerOne, foreign: id}, { onUpdate: cascade, local: playerTwo, foreign: id}]

Those last two throw errors when I try to build. Is there anyone out there who understands what I'm trying to do and can help me achieve it?

Answer 1

If you say $game->getUser(), it's not clear which user you're referring to. Because they reference the same table, you need to give the two relations different names and declare two different foreignAliases for them. Basically, your game table needs to be able to refer to each relation with a different name (e.g. UserOne, UserTwo) and vice versa. It'll confuse Doctrine otherwise and relations won't be set up correctly.

I had a similar problem earlier... using MySQL Workbench to generate a visual representation of the schema after building the models helped make sure the relations were all exactly as needed.

Hope that helps.

Answer 2

Try different names for relations:

  relations:
    UserOne: { onUpdate: cascade, local: playerOne, foreign: id, class: User }
    UserTwo: { onUpdate: cascade, local: playerTwo, foreign: id, class: User }

Edited: also note added "class: User" parameter, which explicitly tells about type of the relation.