i have a question about nested relations in yii

Hi everyone, I am new to yii, I have to say I am very happy with this framework.

I have a question tho:

I have a many to many relation defined between these tables as follows

table product

table category

tabel product_category


'categories' => array(self::MANY_MANY, 'Category', 'product_category(product_id, category_id)'),

within category there is a field: parent_id.

which is related to id in category


'children' => array(self::HAS_MANY, 'Category', 'parent_id'),

'parent' => array(self::BELONGS_TO, 'Category', 'parent_id'),

I want my dataprovider, if a category_id is given, to select all products that are connected to given category.

which works fine with a CActiveDataProvider defined as follows:


$dataProvider=new DataProvider('Product', array(

	'criteria' => array(

		'with' => 'categories',

		'together' => true, 

		'condition' => $condition,

		'params' => $params,

	),

	'pagination'=>array(

      		'pageSize'=>20,

    	),

Now I want my dataprovider also to select all products connected to categories with a parent_id that corresponds with the given category_id. I suspect this can be done by referencing the ‘parent’ and ‘children’ relations defined in the category model. Unfortunately I don’t know if and / or how this can be done.

Any help on this would be appreciated,

thanks in advance, Dick Brouwers

Hi,

I know there are several similar posts in this forum but still I need to be pointed in the right direction with this problem please. As I described before, I don’t know how i get a reference to the ‘children’ of category_id.

To be a little more precise here is the way i constructed $condition and $params:




$condition = 'published_in = :published';

$params = array();

$params[':published'] = $publtype;

if ($producttype > 0)

{

				$condition.= ' AND product_type = :productType';

				$params[':productType'] = $producttype;

			}

			if ($brand > -1) 

			{

				$condition .= ' AND brand_id = :brandID';

				$params[':brandID'] = $brand; 

			}

			if ($category > -1)

			{																	

				$condition .= ' AND category_id = :catID AND parent_id = :parentID';

				$params[':catID'] = $category;

				$params[':parentID'] = $category;

			}

		 

			$dataProvider=new DataProvider('Product', array(

				'criteria' => array( 

					'with' => array('categories','categories.children'),

					'together' => true, 

					'condition' => $condition,

					'params' => $params,

			),

			'pagination'=>array(

      			'pageSize'=>20,

    		),

		));	

		$this->render('index',array(

			'dataProvider'=>$dataProvider,

		));

What I am trying to achieve is that when a user selects a category

It does not only selects the products directly connected to that category but also the products connected the children of that category.

for instance: i have category ‘hardware’ which is parent of category ‘computer’

I want products connected to computer to be selected when user is selecting ‘hardware’.

I put categories.children in my ‘with’ statement but i keep getting an error

CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1052 Column ‘parent_id’ in where clause is ambiguous. The SQL statement executed was: SELECT COUNT(DISTINCT t.id) FROM hnny_product t LEFT OUTER JOIN hnny_product_category categories_categories ON (t.id=categories_categories.product_id) LEFT OUTER JOIN hnny_category categories ON (categories.id=categories_categories.category_id) LEFT OUTER JOIN hnny_category children ON (children.parent_id=categories.id) WHERE (published_in = :published AND category_id = :catID AND parent_id = :parentID)

when I change category_id and parent_id into t.category_id and t.parent_id

I get a message like: t.category not found in this table

so I am kinda stuck if someone could help me here, i would be very happy

kind regards

solved:

everything works fine I just made a little mistake:


$condition .= ' AND category_id = :catID AND parent_id = :parentID';

should be changed in


$condition .= ' AND category_id = :catID OR parent_id = :parentID';

lol that took me day to figure out…

nested relations work fine in yii tho