I’m working on a project which requires me to set exclusive “flag” fields on a record, and am trying to work out the best way to handle it with Yii. To illustrate what I mean:
-
I have two tables, tbl_organisation and tbl_person
-
There is a one to many relationship from tbl_person to tbl_organisation (one organisation has many people)
-
However for an organisation, there is usually one and only primary contact, and one billing contact (there may be others later on).
The first way of representing it involved two-way foreign keys relationships, which can cause problems with constraint conflicts, so at the moment I’m looking at having:
-
A foreign key from tbl_person (foreign key) -> tbl_organisation (primary key)
-
is_billing_contact and is_primary_contact boolean fields in tbl_person.
I’m then building a behaviour to check and set the fields in is_billing_contact & is_primary_contact, ensuring that you never have more than one billing or primary contacts for each organisation.
I currently have the relationships:
'people' => array(self::HAS_MANY, 'Person', 'fk_organisation_id', 'index'=>'id'),
in my Organisation class, and
'organisation' => array(self::BELONGS_TO, 'Organisation', 'fk_organisation_id')
in my Person class.
For checking, I use the following code fragment to get the current flagged contact for an organisation:
$count = 0;
foreach ($this->getOwner()->$collection as $record)
{
if (1==$record->$flagField)
{
$flaggedRecords[] = $record;
++$count;
}
}
//check to see if we have too many flagged records contacts for this collection, error if so
if ($count>1)
{
throw new Exception ("Data Integrity Error - $count Records Found with $flagField");
return false;
}
else if (0==$count) // none found
{
return false;
}
else
{
return array_pop($flaggedRecords);
}
}
Is this the best way to handle it?
Also, the code I’ve included doesn’t seem very “yii”… is there a way I could do the same using the query builder?
Thanks!
Iain