Filtering Array Of Values For Many To Many Relation Between 2 Tables

Yii 1.1 General Discussion
1

I’ve had good success setting up filtering on lots my models from this article.

However, problem I’ve run into is filtering on a column of values returned by a many-to-many relationship in some of my database tables. I have a ‘service’ database table, which consists of singular entities and group entities. The table has a many-to-many relationship to itself, linking group entities to their members and vice-versa. I tried following the comments here, but without success. When I do so, attempting to filter on the Member column on returns lines matching on the service_name column.

Any clues or assistance would be much appreciated :).

Service model:


	public function relations()

	{

		return array(

			'childServiceGrps' => array(self::MANY_MANY, 'Service', 'service_contains_service(service_service_id, service_service_id1)'),

			'parentServiceGrps' => array(self::MANY_MANY, 'Service', 'service_contains_service(service_service_id1, service_service_id)'),

		);

	}

	

	public function getMembers() {

		return implode( '<br/>', CHtml::listData( $this->childServiceGrps,'service_id','service_name' ) );

	}


	public function searchGrp()

	{

		$criteria=new CDbCriteria;


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

		$criteria->compare('t.service_type','group',true);

		

		if( !empty( $this->member_filter ) ) {

			$criteria->addCondition( 't.service_id IN (SELECT childServiceGrps.service_id FROM service childServiceGrps WHERE childServiceGrps.service_name LIKE :relNameLike)' );

			$criteria->params[':relNameLike'] = '%' . $this->member_filter . '%';

		}

		

		return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

			'pagination'=>array(

					'pageSize'=>20,

			),

		));

	}

Service Group view:


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

    'dataProvider'=>$model->searchGrp(),

	'filter'=>$model,

    'columns'=>array(

        'service_name',

       	array(

   			'name'=>'member_filter',

			'header'=>'Members',

 		  	'type'=>'html',

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

		),

    	'comments',

    ),

)); ?>
2

Hi 9ball,

I think you have to join the link table (service_contains_service) and the related child table (service) explicitly.

Something like this:




t.service_id IN 

(SELECT childServiceGrps.service_id 

FROM service_contains_service link

JOIN service childServiceGrps ON childServiceGrps.service_id = link.service_id1

WHERE link.service_id = t.service_id

AND childServiceGrps.service_name LIKE :relNameLike)

Not tested, though …

3

Thanks softark. Looks like I’m getting closer (it doesn’t error out), but I’m still not any results.

I modified query to look like such:


t.service_id IN 

(SELECT childServiceGrps.service_id 

FROM service_contains_service AS link

JOIN service childServiceGrps ON childServiceGrps.service_id = link.service_service_id1

WHERE link.service_service_id = t.service_id

AND childServiceGrps.service_name LIKE :relNameLike)
4

Hmm, have you checked the raw SQL that Yii has created? I hope it will help you spot the issue.