Search() in 1.1.1 model - how to add conditions for relational tables?

Yii 1.1 General Discussion
1

Hi,

I have the following problem:

I have a MANY_MANY connection for the tables tbl_ddentity and tbl_deviceclass (mapped via tbl_ddentityxdeviceclass).

Now I added the following relation to DDEntity.php controller:




public function relations()

	{

		return array(

                'deviceClasses'=>array(self::MANY_MANY, 'DeviceClass',

                'ddentityxdeviceclass(sysDDEntityID, sysDeviceClassID)'),

    );

	}

and this one to DeviceClass.php controller:




	public function relations()

	{

		// NOTE: you may need to adjust the relation name and the related

		// class name for the relations automatically generated below.

		return array(

		  'dDEntitys'=>array(self::MANY_MANY, 'DDEntity',

      'ddentityxdeviceclass(sysDeviceClassID, sysDDEntityID)'),

		);

	}

If I access the model via ActiveDataProvider, I can easily access DeviceClasses for a DDEntity via:




foreach ($model->DeviceClasses as $deviceClass) 

   print_r ($deviceClass);

But:

If I try to get only DDEntities which have certain DeviceClasses via the model search function, it seems as if the relations are not already known to Yii:




	public function search()

	{

		$criteria=new CDbCriteria;

                 $criteria->compare('deviceClasses.DeviceClassNumber',$this->DeviceClassNumber);    

             

    

    return new CActiveDataProvider('DDentity', array(

      'criteria'=>$criteria,

      'sort'=>$sort,

      'pagination'=>array(

             'pageSize'=>20,

         ),

    ));

	}

I added "DeviceClassNumber" as a public attribute to DDEntity and now get the SQL error




MYSQL Error: Unknown Column in DeviceClasses.DeviceClassNumber

Now my question:

I could of course generate the needed SQL statement by hand (table joins and where clause).

But as I have already declared the relations, it would be much easier, if I could access them here.

What is the best (easiest and fastest) way to do this?

Thanks a lot,

Simon

P.S.:

I stripped loads of code to make it easier to read. It might be that there is some minor error which prevents this particular code from working. But I hope my general problem is obvious.

2

In the guide, the CActiveDataProvider doc shows an example where a with attribute is added to CDbCriteria.




$dataProvider=new CActiveDataProvider('Post', array(

    'criteria'=>array(

        'condition'=>'status=1 AND tags LIKE :tags',

        'params'=>array(':tags'=>$_GET['tags']),

        'with'=>array('author'),

    ),

    'pagination'=>array(

        'pageSize'=>20,

    ),

));

But it might not be what you want, because as soon as you have a limit (or some pagination) the SQL is far from perfect. See this bug ticket.

In fact, the example does not even work for me (the "with" seems to be ignored).

I propose an ugly fix:




$criteria->join = "LEFT JOIN DeviceClasses ON DeviceClasses.XX=YY";

Please correct me if I’m wrong.

3

Thanks for the reply!

What you say is exactly what I feared:

Despite having declared all relationships in the model, I have to craft the SQL more ore less by hand in the search method.

I already saw the bug you mentioned and thought about a way to avoid it.

4

No solution, just a summary.

For a MANY_MANY relation, the CDbCriteria::with does not have any effect.

For a HAS_MANY relation, you can use your relation in a CDbCriteria. Here a simplified version of my code:




public function search()

{

	$criteria = new CDbCriteria;

	$criteria->compare('t.category', $this->category, true);

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

	$criteria->compare('related.pere', 3);

Of course, related is a relation defined in the function relations().

Be careful with the t. you have to add to the attributes of my model.

The SQL produced is alright, even if a "LEFT OUTER JOIN" is left in the COUNT SELECT.

5

OK, my solution for MANY_MANY relationship queries now is to join on the linking table by adding the following statement:




    $criteria->join = "LEFT JOIN ddentityxdeviceclass ON t.SysDDEntityID=ddentityxdeviceclass.sysDDEntityID";

    $criteria->compare('ddentityxdeviceclass.sysDeviceClassID',$this->DeviceClassID);

    $criteria->group= "t.SysDDEntityID" ; // The group by has no visual effect but as less rows have to be fetched I assume it helps gain performance

Hope this helps somebody else, too!

6

I’m journeying down the convoluted path of MANY_MANY relationships in Yii right now. I’m surprised this functionality is not more functional. Hopefully in the next release…?

Question for you, SimonB. What does the code look like in your view to work with this? What name variable do you use to refer to this relation? I tried using your code in the search / $criteria section of the Model, and referring to it in the CGridView widget using a variable name I invented and gave a label to in the Model, but when I try to run the filter, nothing happens. It’s maddening since it’s difficult to track down the reasons why from the Stack Trace.

Thanks in advance.