[mikeax]

Thank you so much for the quick reply. I am loving what I see so far.

[mikeax]

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!

[creocoder]

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.

[mikeax]

thanks creocoder,

You want to see SQL dump? or you want to see entire Class Mortgage, Person, MortgageRelationship?

appreciate your help. A lot!

[creocoder]

mikeax
Mortgage, Person classes. And controller action. SQL dumps for all 3 tables can help too.

[mikeax]

Files attached
 sample.sql.txt (3.76K)
Number of downloads: 15
 model_and_controller.php (11.55K)
Number of downloads: 20

[creocoder]

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...

[mikeax]

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

[creocoder]

Quote

Will this feature be feasible for large recordsets?

Yes It Is

Quote

You have certainly solidified our decision to move to Yii.

Glad to hear.

[mikeax]

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?

[creocoder]

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.

[mikeax]

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

[creocoder]

Quote

Will this be possible with AR or will I have to craft SQL via DAO?

Yes, this will be possible. Fix ready, after it will be commited, you can remove workaround.

[SDavenport]

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!

[tri]

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

This post has been edited by tri: 28 March 2011 - 06:35 PM

[mikeax]

Hmmm... i think this is expected

$person->personUnits[0]->type_id;// first occurrence
$person->personUnits[1]->type_id;// next occurrence

no?

[SDavenport]

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>';
}

[spiderman]

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.

[tri]

spiderman, on 16 June 2011 - 07:54 AM, said:

I have 3 tables Product, Order, and association table ProductOrder created for MANY-MANY relationship
...

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

[spiderman]

tri, on 16 June 2011 - 11:23 AM, said:

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.