Selecting join table with mant to many?

(Mike) #21

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

(Mike) #22

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:



Person Relationships as follows:



MortgageRelationship table





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, 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) #23


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

(Mike) #24

thanks creocoder,

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

appreciate your help. A lot!

(Creocoder) #25


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

(Mike) #26

Files attached





(Creocoder) #27


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…

(Mike) #28

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.


(Creocoder) #29

Yes It Is ;)

Glad to hear.

(Mike) #30

ok, last one on this i hope. It works perfectly except

i have to use


when I expected to use


As there is only one relationship per person. Is this expected?

(Creocoder) #31


Yes, it’s expected. See, you have in your Person model:

	public function relations()


		return array(






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.

(Mike) #32

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?


(Creocoder) #33

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

(Sdavenport) #34

I tried this functionally today and found it to be broken.

I have 3 tables (unit, unit_person, person).

unit_person table



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!

(Ft07) #35

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?)


(Mike) #36

Hmmm… i think this is expected

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

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


(Sdavenport) #37

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


(Consultant2b) #38

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.

(Ft07) #39

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.


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.


(Consultant2b) #40


Thanks for answering with the very helpful link.