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:

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

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.


(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

mikeax

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


(Mike) #26

Files attached

1323

sample.sql.txt

1324

model_and_controller.php


(Creocoder) #27

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…


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

M


(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

$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) #31

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.


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

Thanks


(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


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!


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

/Tommy


(Mike) #36

Hmmm… i think this is expected




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

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



no?


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

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


(Consultant2b) #40

Tommy,

Thanks for answering with the very helpful link.