Yii Framework Forum: Selecting join table with mant to many? - Yii Framework Forum

Jump to content

  • (3 Pages)
  • +
  • 1
  • 2
  • 3
  • You cannot start a new topic
  • You cannot reply to this topic

Selecting join table with mant to many? Rate Topic: ***** 3 Votes

#21 User is offline   mikeax 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 21
  • Joined: 19-February 11
  • Location:Washington

Posted 28 February 2011 - 12:31 AM

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

#22 User is offline   mikeax 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 21
  • Joined: 19-February 11
  • Location:Washington

Posted 01 March 2011 - 03:48 PM

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!
0

#23 User is offline   creocoder 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 198
  • Joined: 09-March 09
  • Location:*.php

Posted 01 March 2011 - 09:37 PM

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.
No good, no bad, only consequence.
0

#24 User is offline   mikeax 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 21
  • Joined: 19-February 11
  • Location:Washington

Posted 01 March 2011 - 09:51 PM

thanks creocoder,

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

appreciate your help. A lot!
0

#25 User is offline   creocoder 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 198
  • Joined: 09-March 09
  • Location:*.php

Posted 01 March 2011 - 10:00 PM

mikeax
Mortgage, Person classes. And controller action. SQL dumps for all 3 tables can help too.
No good, no bad, only consequence.
0

#26 User is offline   mikeax 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 21
  • Joined: 19-February 11
  • Location:Washington

Posted 01 March 2011 - 11:20 PM

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

#27 User is offline   creocoder 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 198
  • Joined: 09-March 09
  • Location:*.php

Posted 01 March 2011 - 11:49 PM

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...
No good, no bad, only consequence.
0

#28 User is offline   mikeax 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 21
  • Joined: 19-February 11
  • Location:Washington

Posted 02 March 2011 - 12:04 AM

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
0

#29 User is offline   creocoder 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 198
  • Joined: 09-March 09
  • Location:*.php

Posted 02 March 2011 - 12:33 AM

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.
No good, no bad, only consequence.
0

#30 User is offline   mikeax 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 21
  • Joined: 19-February 11
  • Location:Washington

Posted 02 March 2011 - 01:18 AM

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

#31 User is offline   creocoder 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 198
  • Joined: 09-March 09
  • Location:*.php

Posted 02 March 2011 - 01:31 AM

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.
No good, no bad, only consequence.
0

#32 User is offline   mikeax 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 21
  • Joined: 19-February 11
  • Location:Washington

Posted 02 March 2011 - 08:55 AM

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
0

#33 User is offline   creocoder 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 198
  • Joined: 09-March 09
  • Location:*.php

Posted 04 March 2011 - 05:46 AM

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.
No good, no bad, only consequence.
0

#34 User is offline   SDavenport 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 2
  • Joined: 11-February 11

Posted 28 March 2011 - 05:43 PM

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!
0

#35 User is online   tri 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,651
  • Joined: 20-November 08
  • Location:Stockholm, Sweden

Posted 28 March 2011 - 06:23 PM

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

Don't forget to read The Definitive Guide to Yii (en) (sv) | The class reference has the details
0

#36 User is offline   mikeax 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 21
  • Joined: 19-February 11
  • Location:Washington

Posted 28 March 2011 - 07:59 PM

Hmmm... i think this is expected

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

no?
0

#37 User is offline   SDavenport 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 2
  • Joined: 11-February 11

Posted 29 March 2011 - 09:05 AM

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

0

#38 User is offline   spiderman 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 3
  • Joined: 29-May 11

Posted 16 June 2011 - 07:54 AM

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

#39 User is online   tri 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,651
  • Joined: 20-November 08
  • Location:Stockholm, Sweden

Posted 16 June 2011 - 11:23 AM

View Postspiderman, 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
Don't forget to read The Definitive Guide to Yii (en) (sv) | The class reference has the details
0

#40 User is offline   spiderman 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 3
  • Joined: 29-May 11

Posted 16 June 2011 - 08:21 PM

View Posttri, 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.
0

Share this topic:


  • (3 Pages)
  • +
  • 1
  • 2
  • 3
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users