Doctrine: Multiple One-To-One Relation to same table
by rp
I am currently using sfGuardUser table from the sfDoctrineGuardPlugin to link up to a messages table which stores the user id of sender and receiver. To ensure that the data schema is properly normalised I need to make sure that both the user id are actually foreign keys to the sfGuardUser table. Here are the schemas to get a better under standing of what I am talking about:
sfGuardUser:
actAs: [Timestampable]
columns:
id:
type: integer(4)
primary: true
autoincrement: true
username:
type: string(128)
notnull: true
unique: true
algorithm:
type: string(128)
default: sha1
notnull: true
salt: string(128)
password: string(128)
is_active:
type: boolean
default: 1
is_super_admin:
type: boolean
default: false
last_login:
type: timestamp
indexes:
is_active_idx:
fields: [is_active]
relations:
groups:
class: sfGuardGroup
local: user_id
foreign: group_id
refClass: sfGuardUserGroup
foreignAlias: Users
permissions:
class: sfGuardPermission
local: user_id
foreign: permission_id
refClass: sfGuardUserPermission
foreignAlias: Users
Message:
actAs:
Timestampable: ~
columns:
user_from_id: { type: integer(4), notnull: true }
user_to_id: { type: integer(4), notnull: true }
date_sent: { type: timestamp }
date_read: { type: timestamp }
alertSent: { type: boolean, default: false, notnull: true }
subject: { type: string(255), notnull: true }
body: { type: string(255), notnull: true }
relations:
Sent:
class: sfGuardUser
local: user_from_id
foreign: id
type: one
Recv:
class: sfGuardUser
local: user_to_id
foreign: id
type: one
A couple of things to remember on how I got it working
- The name of the relationship has to be unique, if the same one is used the last one will be used
- You need to use
classproperty to tell it the name of the Model class to use - Have to 100% ensure that the datatype and size are the same otherwise the
ALTER...sqls generated won’t work. I had issues of usinginteger(4)andintegerand it took me 10mins of scratching my head to wonder what was wrong
Hope this helps someone with a similar problem.
Thx man,
I’ve been looking for the solution for a long time,
I’ve found here all that is missing in “expert” advice
http://www.symfony-project.org/blog/2008/11/12/call-the-expert-customizing-sfdoctrineguardplugin