Problems with multiple custom filters

I am having problems when I define the criteria for multiple filters.

Given the table layout Org(1-n::1-n)SubCategory(1-n::1)Category I have the following code:

// model


'subCategoriesMm'=>array(self::MANY_MANY, 'Subcategory', 'org_cat_chosen(OrganizationId, SubCategoryId)'),

'subCategories'=>array(self::HAS_MANY,'OrgCatChosen','OrganizationId'),

'categories'=>array(self::HAS_MANY, 'Category', 'CategoryId', 'through' => 'subCategoriesMm.category'),


...


public function getRelatedSubCategoryNames ()

{

  $out=GxHtml::listData($this->subCategoriesMm,'SubCategoryId','Name');

  return implode('<br />', $out);

}

	

public function getRelatedCategoryNames ()

{

  $out=GxHtml::listData($this->categories,'CategoryId','Name');

  return implode('<br />', $out);

}


...


public function search() {

...

$criteria->compare('SubCategoryId',$this->assignedSubCategories);

$criteria->with=array('subCategories');

$criteria->together=true;


$criteria->compare('category.CategoryId',$this->assignedCategories);

$criteria->with=array('categories');

$criteria->together=true;

// view

$this->widget('zii.widgets.grid.CGridView', array(

...

'columns' => array(

	array(

	  'name'=>'assignedCategories',

	  'filter'=>GxHtml::listData(Category::model()->findAll(array('order'=>'name ASC')),'CategoryId','Name'),

	  'type'=>'html',

	  'value'=>'$data->relatedCategoryNames',

	), 

	array(

	  'name'=>'assignedSubCategories',

	  'filter'=>GxHtml::listData(Subcategory::model()->findAll(array('order'=>'name ASC')),'SubCategoryId','Name'),

	  'type'=>'html',

	  'value'=>'$data->relatedSubCategoryNames',

	),

If I only use one or the other (i.e. Category or SubCategory) everything works as expected. When I try to put both into play I get the following error when filtering SubCategory

Integrity constraint violation: 1052 Column 'SubCategoryId' in where clause is ambiguous

So I take care of that by changing

$criteria->compare('SubCategoryId',$this->assignedSubCategories);

to

$criteria->compare('subcategory.SubCategoryId',$this->assignedSubCategories);

and then get the following error

Column not found: 1054 Unknown column 'subcategory.SubCategoryId' in 'where clause'. 

Remember both of these work when only one is in play. Anybody have any ideas?

Hi,

Just thinking out loud here have you tried:

$criteria->compare('t.SubCategoryId',$this->assignedSubCategories);

This calls SubCategoryId in the Org table. This will not work since it is not part of that table.

<Oops>

According to the definition of your relations, it should be ‘subcategories’ instead of ‘subcategory’.

$criteria->compare('subcategoies.SubCategoryId',$this->assignedSubCategories);

</Oops>

[EDIT]

According to the definition of your relations, it should be ‘subCategories’ instead of ‘subcategory’.

$criteria->compare('subCategoies.SubCategoryId',$this->assignedSubCategories);

Unfortunately that is not the problem either.

I believe this needs to be considered as a whole

$criteria->compare('SubCategoryId',$this->assignedSubCategories);

$criteria->with=array('subCategories');

$criteria->together=true;

I mean you have a typo.

You have to disambiguate the column name with the name of the relation.

It should be ‘subCategories.SubCategoryId’ instead of ‘subcategory.SubCategoryId’.

(My previous post was wrong at ‘subcategories’. It should have been ‘subCategories’.)

Great thanks for the help on that. It’s weird that I used the table name on the Category portion though and it worked.

Ok if I go to

$criteria->compare('subCategories.SubCategoryId',$this->assignedSubCategories);

$criteria->with=array('subCategories');

$criteria->together=true;

I still get

Column not found: 1054 Unknown column 'subCategories.SubCategoryId' in 'where clause'.

If I go to

// changed to subCategoriesMm relationship

$criteria->compare('subCategoriesMm.SubCategoryId',$this->assignedSubCategories);  

$criteria->with=array('subCategories');

$criteria->together=true;

Then the search works.

However If a item has multiple Category/SubCategory pairings it will only display the single SubCategory I selected in the dropdown.

The goal and the reason for the addition of

'subCategories'=>array(self::HAS_MANY,'OrgCatChosen','OrganizationId'), 

was to have all SubCategory’s for a given item still displayed.

The kicker with all of this was I had everything working this morning so I know it can work. I forget to version it and continued to make changes on it and ever since I have been running in circles.

I’m not sure, but defining 2 or more "with"s for a criteria might cause a problem by overwriting …

Would you please try the following:

$criteria->with = array('subCategoriesMm', 'subCategories', 'categories');

You might be right. I am pretty sure I had everything working as intended but at this time I am thinking it was a figment of my imagination. Hard to tell at this point since I have gone through so much with it. hehe.

I gave that a try as below and there is no change. Cleaner code but still missing that last part.

$criteria->compare('subCategoriesMm.SubCategoryId',$this->assignedSubCategories);

$criteria->compare('categories.CategoryId',$this->assignedCategories);

$criteria->with = array('subCategoriesMm', 'subCategories', 'categories');

$criteria->together=true;

Changed to this though as I am really not searching on subCategoriesMm

$criteria->with = array('subCategories', 'categories');

Even this does not keep the multiple SubCategories in the result set but apparently it is using subCategories now. This wasn’t working before. Maybe proving the overwriting of ‘with’

I haven’t read the complete thread… but in the last code you have in the compare “subCategoriesMm.SubCategoryId” (MANY_MANY), but in with you have “subcategories” (HAS_MANY)… have you tried with “subCategories.SubcategoryId” in the compare ?

I would suggest you to check the generated SQL command to see what SQL is executed…

Thanks mdomba, that was the last piece of the problem. All is working now.