Thank you so much for the quick reply. I am loving what I see so far.
Thank you so much for the quick reply. I am loving what I see so far.
You guys are AWESOME!
Just looking at ‘through’ committed yesterday. I am still new so likely missing something simple but I can’t get this feature to work. I should probably wait for docs but thought I would throw it out here…
I am trying to access Person to Mortgage through MortgageRelationship
Mortgage AR relationship as follows:
‘relationship’=>array(self::HAS_MANY,‘MortgageRelationship’,‘mortgage_id’),
‘person’=>array(self::HAS_MANY,‘Person’,‘person_id’,‘through’=>‘relationship’),
Person Relationships as follows:
‘relationship’=>array(self::HAS_MANY,‘MortgageRelationship’,‘person_id’),
‘mortgages’=>array(self::HAS_MANY,‘Mortgage’,‘mortgage_id’,‘through’=>‘relationship’),
MortgageRelationship table
person_id
mortgage_id
relationship_type
Error:
Column not found: 1054 Unknown column ‘relationship.person_id’ in ‘on clause’
generated SQL
SELECT t.mortgage_id AS t0_c0, person.person_id AS t2_c0, person.first_name AS t2_c1, person.middle_initial AS t2_c2, person.last_name AS t2_c3, person.birth_date AS t2_c4, person.social_insurance_number AS t2_c5, person.home_phone AS t2_c6, person.work_phone AS t2_c7, person.mobile_phone AS t2_c8, person.preferred_phone AS t2_c9, person.mailing_address AS t2_c10, person.email AS t2_c11, person.contact_type AS t2_c12
FROM Mortgages t
LEFT OUTER JOIN People person
ON (relationship.person_id=person.person_id)
WHERE (t.mortgage_id IN (1, 2))
Thanks again!
mikeax
Documentation will be later (new ER diargams, more information about all relation types, etc). But i’m try to help you now. Need to see controller and both models dump.
thanks creocoder,
You want to see SQL dump? or you want to see entire Class Mortgage, Person, MortgageRelationship?
appreciate your help. A lot!
mikeax
Mortgage, Person classes. And controller action. SQL dumps for all 3 tables can help too.
mikeax
This is because CActiveDataProvider add ‘limit’ to criteria and one query breaks in two queries. Current walkaround:
$dataProvider=new CActiveDataProvider('Mortgage');
It will be fixed soon, need more investigation…
Thank you! Will this feature be feasible for large recordsets? I am still getting the hang of how Yii crafts SQL. I will need to use this in a list view with a count of several thousand and page size of 200.
Display row like:
person[0].first_name (relationship.type) & person[1].first_name (relationship.type) mortgage.other_data
perhaps this is a different topic.
You have certainly solidified our decision to move to Yii.
M
Yes It Is 
Glad to hear.
ok, last one on this i hope. It works perfectly except
i have to use
$data->person[0]->relationship[0]->type;
when I expected to use
$data->person[0]->relationship->type;
As there is only one relationship per person. Is this expected?
mikeax
Yes, it’s expected. See, you have in your Person model:
public function relations()
{
return array(
...
'relationship'=>array(self::HAS_MANY,'MortgageRelationship','person_id'),
...
);
}
It’s mean that on person has many relationships.
You need to change this to self::HAS_ONE and set UNIQUE index on person_id in MortgageRelationship table, if you want $data->person[0]->relationship->type usage.
Got it.
Now it appears based on your first answer that I can’t search Mortgages based on Person last_name because I can’t use the ‘with’. Search Mortgage based on person.last_name is fairly crucial for me. Will this be possible with AR or will I have to craft SQL via DAO?
So really want pseudo code:
SELECT * FROM Mortgages WHERE mortgage.person.last_name LIKE ‘%term%’ AND mortgage.person.relationship.type=‘PrimaryApplicant’
Am I going the wrong direction? Will I need pure SQL?
Thanks
Yes, this will be possible. Fix ready, after it will be commited, you can remove workaround.
I tried this functionally today and found it to be broken.
I have 3 tables (unit, unit_person, person).
unit_person table
unit_id
person_id
type_id <- classification of the person for the unit
Unit model relations
‘unitPersons’ => array(self::HAS_MANY, ‘UnitPerson’, ‘unit_id’),
‘persons’ => array(self::HAS_MANY, ‘Person’, ‘person_id’, ‘through’=>‘unitPersons’),
Person model relations
‘personUnits’ => array(self::HAS_MANY, ‘UnitPerson’, ‘person_id’),
‘units’ => array(self::HAS_MANY, ‘Unit’, ‘unit_id’, ‘through’=>‘personUnits’),
In my controller
$dataProvider = new CActiveDataProvider(Unit::model());
In my view
echo $data->unit_name . ‘<br>’;
foreach ($data->persons as $person)
{
echo $person->last_name . ’ ’ . $person->personUnits[0]->type_id . ‘<br>’;
}
The type_id returned for the person is not correct. It always returns the first type_id found for that user in the unit_person table.
Please help!
Does this work?
foreach ($data->persons as $i=>$person)
{
//echo $person->last_name . ' ' . $person->personUnits[$i]->type_id . '<br>';
echo $person->last_name . ' ' . $person->unitPersons[$i]->type_id . '<br>';
}
(I don’t yet understand the through support fully, but to me it seems more straightforward to iterate over unitPersons. The relationship between unitPersons and Person would be an implicit BELONGS_TO, wouldn’t it?)
/Tommy
Hmmm… i think this is expected
$person->personUnits[0]->type_id;// first occurrence
$person->personUnits[1]->type_id;// next occurrence
no?
This did not work:
foreach ($data->persons as $i=>$person)
{
//echo $person->last_name . ' ' . $person->personUnits[$i]->type_id . '<br>';
echo $person->last_name . ' ' . $person->unitPersons[$i]->type_id . '<br>';
}
Get a undefined offset: 1 error
Tommy, I took your idea to iterate over unitPersons and it worked great. Thanks!
UnitPerson model relations
public function relations() {
return array(
'unit' => array(self::BELONGS_TO, 'Unit', 'unit_id'),
'person' => array(self::BELONGS_TO, 'Person', 'person_id'),
);
}
foreach ($data->unitPersons as $unitPerson)
{
echo $unitPerson->person->last_name . ' ' . $unitPerson->type_id . '<br>';
}
I have 3 tables Product, Order, and association table ProductOrder created for MANY-MANY relationship
For ProductOrder class:
public function relations()
{
return array(
'product' => array(self::BELONGS_TO, 'Product', 'productId'),
'order' => array(self::BELONGS_TO, 'Order', 'orderId'),
);
}
For Product class:
public function relations()
{
return array(
'product' => array(self::HAS_MANY, 'ProductOrder', 'productId'),
);
}
For Order class:
public function relations()
{
return array(
'order' => array(self::HAS_MANY, 'ProductOrder', 'orderId'),
);
}
I was able to retrieve only linked fields (productId, orderId) but not unlinked field (productOrderId) on ProductOrder object. Could someone please show me how I can accomplish the latter? Thank you.
Since you have a n:m relationship and don’t need to access additional fields from the association table, you should change the relationships in Order and Product to self::MANY_MANY. You don’t need the ProductOrder class.
Product.php
public function relations()
{
return array(
'orders' => array(self::MANY_MANY, 'Order', 'product_order(productId, orderId)'),
);
}
The query syntax will be the same as in the HAS_MANY case.
Read more in this section of The Definitive Guide to Yii. There’s also a lot of forum threads on this, as well as wiki articles and some related extensions.
/Tommy
Tommy,
Thanks for answering with the very helpful link.