Ask your Symfony questions! Pay money and get answers fast! (more info)

Doctrine: Column Aggregation Inheritance, Many to Many Symfony

  • SOLVED

I need to track contacts, emails and recipients by type (to/cc/bcc). Then, I need to be able to grab the individual types of recipients (so, grab list of to, grab list of cc, grab list of bcc).

So, an email has its fields, and then relations to types of recipients. Each recipient corresponds to a Contact - the Recipients table serves as a n:m table between Email and Contacts, with the additional specification of recipient_type.
data/fixtures/email_message.yml

EmailMessage:
msg1:
From: Bob
To: [ Joe ]
Cc: [ John ]

config/doctrine/schema.yml

Contact:
columns:
id: { type: integer(4), primary: true, autoincrement: true }
name: { type: string(50), notnull: true}

EmailMessage:
columns:
id: { type: integer, primary: true, autoincrement: true}
from_contact_id: { type: integer(4) }
subject: { type: string(150) }
body: { type: clob(65532) }
relations:
From: { class: Contact, local: from_contact_id, foreign: id, foreignAlias: EmailMessages }
Recipients: { class: Contact, local: message_id, foreign: recipient_id, refClass: EmailRecipient }
To: { class: Contact, local: message_id, foreign: recipient_id, refClass: ToRecipient }
Cc: { class: Contact, local: message_id, foreign: recipient_id, refClass: CcRecipient }

EmailRecipient:
tableName: email_recipients
columns:
message_id: { type: integer, primary: true}
type: { type: string(5) }
relations:
Contact: { local: recipient_id, foreign: id }

ToRecipient:
inheritance:
extends: EmailRecipient
type: column_aggregation
keyField: type
keyValue: to
columns:
recipient_id: { type: integer(4), primary: true }


CcRecipient:
inheritance:
extends: EmailRecipient
type: column_aggregation
keyField: type
keyValue: cc
columns:
recipient_id: { type: integer(4), primary: true }


Now, if I call something like, the following, it works (displays all types of recipients, to/cc/bcc)
$email_message->getRecipients()

However, if I try to call something like the following, it fails (despite there being a "getTo" function comment in the lib/model/doctrine/base/BaseEmailMessage.class.php file).
$email_message->getTo()


Now, if I comment out the
EmailMessage Relation
#Recipients: { class: Contact, local: message_id, foreign: recipient_id, refClass: EmailRecipient }
the $email_message->getTo() works, but $email_message->getCC() doesn't.

The worst part about this is that fixtures load as I want them to. It seems like the root of the problem is somewhere in the relations definition of the EmailMessage table.

Answers (2)

2010-05-05

Jakub Zalas answers:

Firstly I found it weird that you defined relation 'Contact' in 'EmailRecipient' and column 'recipient_id' in its child classes. So I moved it to the parent.

However, more important is duplication of relation definitions in Contact class.

Fixed schema:


Contact:
columns:
id: { type: integer(4), primary: true, autoincrement: true }
name: { type: string(50), notnull: true}
relations:
RecipientEmailMessages: { class: EmailMessage, local: recipient_id, foreign: message_id, refClass: EmailRecipient }
RecipientToMessages: { class: EmailMessage, local: recipient_id, foreign: message_id, refClass: ToRecipient }
RecipientCcMessages: { class: EmailMessage, local: recipient_id, foreign: message_id, refClass: CcRecipient }

EmailMessage:
columns:
id: { type: integer, primary: true, autoincrement: true}
from_contact_id: { type: integer(4) }
subject: { type: string(150) }
body: { type: clob(65532) }
relations:
From: { class: Contact, local: from_contact_id, foreign: id, foreignAlias: EmailMessages }
Recipients: { class: Contact, local: message_id, foreign: recipient_id, refClass: EmailRecipient }
To: { class: Contact, local: message_id, foreign: recipient_id, refClass: ToRecipient }
Cc: { class: Contact, local: message_id, foreign: recipient_id, refClass: CcRecipient }


EmailRecipient:
tableName: email_recipients
columns:
message_id: { type: integer, primary: true}
type: { type: string(5) }
recipient_id: { type: integer(4), primary: true }
relations:
Contact: { local: recipient_id, foreign: id }


ToRecipient:
inheritance:
extends: EmailRecipient
type: column_aggregation
keyField: type
keyValue: to

CcRecipient:
inheritance:
extends: EmailRecipient
type: column_aggregation
keyField: type
keyValue: cc


Fixtures I used (data/fixtures/fixtures.yml):


Contact:
Bob:
name: Bob
Joe:
name: joe
John:
name: John

EmailMessage:
msg1:
From: Bob
To: [ Joe ]
Cc: [ John ]


Unit test I wrote to verify everything works (test/unit/modelTest.php):


<?php

require_once (dirname(__FILE__) . '/../bootstrap/unit.php');

$test = new lime_test(6, new lime_output_color());

$configuration = ProjectConfiguration::getApplicationConfiguration('frontend', 'test', true);
new sfDatabaseManager($configuration);
$manager = Doctrine_Manager::getInstance();
$test->diag('Loading fixtures...');
Doctrine::loadData(sfConfig::get('sf_data_dir') . '/' . 'fixtures');

$emailMessage = Doctrine_Core::getTable('EmailMessage')->createQuery('m')->select('m.*')->fetchOne();

$recipients = $emailMessage->getRecipients();
$test->isa_ok($recipients, 'Doctrine_Collection', '->getRecipients() returns a collection');
$test->cmp_ok(count($recipients), '===', 2, '->getRecipients() returns 2 recipients');

$to = $emailMessage->getTo();
$test->isa_ok($to, 'Doctrine_Collection', '->getTo() returns a collection');
$test->cmp_ok(count($to), '===', 1, '->getTo() returns one recipient');

$cc = $emailMessage->getCc();
$test->isa_ok($cc, 'Doctrine_Collection', '->getCc() returns a collection');
$test->cmp_ok(count($cc), '===', 1, '->getCc() returns one recipient');


Run test:

./symfony test:unit model


bmv82 comments:

Wow, I feel really silly seeing this as the answer. I had been banging my head against the wall for a while. So, was the main issue the failure to add the relationships to the Contact schema?

2010-05-05

Bill Hunt answers:

Without seeing all of the actual code that's generated and the data you're using, it's a bit hard to nail this down precisely. However, it appears that there's no recipient_id column in EmailRecipient, so I'd guess that the join is breaking on one side and returning all Contacts as a result - I'd guess that it's a bug. The to/cc issue is a bit strange - again, the data would help here - but I'd try switching the order of those two definitions and see if the behavior changes.