Multiple Joins Using AR

Hi,

I have products table.

each product has its own group (tbl_groups table).

each group is under sub-category.

each sub-category has it’s parent-category.

I want to join all those tables by using Active Records, is that possible?

I already tried to do:


$data = Product::model()->findAll();

$ParentCategoryTitle = $data->group->subCategory->parentCategory->title; // Too many queries...

But its seems like a bad idea (4 queries instead of one big single query).

Thanks! :)

I think you should try eager loading instead of lazy one. If you combine it with a call to togther() at the beginning of your method’s chain, which will set CDbCriteria’s together property to true, then it should give you one query.

Hi thank you for your quick reply.

I’m not sure how to write the code…

I have read about the eager loading in the documentation where you told me to,

this almost perfect:


$data = Product::model()->with(array(	'group',			// Join table

					'group.category',		// 1st hierarchy level - Join table perfectly

					'group.category.parent',	// 2nd hierarchy level - Join table perfectly

					'group.category.parent.parent'	// 3rd hierarchy level - SQL Error...

				))->findAll();

The SQL error I get: “Syntax error or access violation: 1066 Not unique table/alias: ‘parent’”.

Is that what you meant? Am I doing something wrong…?

Thank again.

Ttry calling together() before calling with() in a chain. Like that:




$data = Product::model()->together()->with(...)

Also if you are using relation names you don’t have to use array inside with(). Just put there relation names as function arguments. Check this. If it complains about not unique names, then maybe last relation should be declared as array with additional options? Hard to say…

I understand the problem now…and I asked the wrong question (in my last reply),

What I need is to make a hierarchical model for categories table…

Do you have a post or an article that explains how to do a hierarchical model with same-table-relations?

Thank you!

menu items, categories it’s all the same after all :). Here you go: http://www.yiiframework.com/wiki/61/creating-a-database-driven-hierarchical-structure-combined-with-cmenu-and-superfish

Hope that helps.

Thank you very much!

You helped me a lot :)