CGridView displaying "Select records NOT IN" [Solved]

Yii 1.1 General Discussion
1

Hi guys

I have the following three tables:




Parent	Junction	Child

A	A1		1

B	A2		2

C	B4		3

	C2		4

I want to display (in a CGridView) all the available Child records that ARE NOT yet linked to a particular parent record in the junction table.

E.g. if the parent record is C, then I want to display Child records 1, 3 & 4 in the CGridView.

How do I write the child-model’s search function?

I can’t use two separate compare statements (one for the parentID and one for the childID) because it would include unwanted records.

I thus have to use a single compare statement that would jointly compare parentID + childID - or something similar like a single relation that uses the parentID + childID combination.

PS: I don’t use composite PK’s. I have separate auto incrementing PK’s. But obviously you can’t use such PK to perform this filtering. So I have no choice but to make this composite filtering work.

Regards

2

I got it working this way:

Add this to the child model’s search() function:


	

public function search($parentID=null) /* If $parentID was not received, it defaults to null */

{

	$criteria=new CDbCriteria;

		

	/* Only include records that are not yet linked to the parentID in the

	junction table. 

	Only CGridViews that need this functionality will pass $parentID to this

	function. For all other gridviews $parentID will be null and this condition

	will be ignored (all records will be included).*/


	If ($parentID <> null) {

		$criteria->condition = 't.child_id NOT IN

			(SELECT	tbl_juntion.child_id

			FROM	tbl_junction

			WHERE   tbl_junction.parent_id = :parentIDparameter

			)';

		$criteria->params=array(':parentIDparameter'=>$parentID);

	}

In your CGridView that displays the “not yet used” child records, pass $parentID to the model’s search function:




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

	'id'=>'child_grid',

	'dataProvider'=>$model->search($parentID), 

	'filter'=>$model,

	'columns'=>array(...

Ps: I get the $parentID in the controller and then pass it to the view.

Hope this helps someone.

3

Dear Friend,

Really great work!

How can I declare a filter in CGridView thereby If I choose a particular parent in filter cell, I can get all its non-children in the grid.

4

Hi seenivasan

I think the answer to your question might be very lengthy.

I don’t select the parent in a filter on the child-gridview. I have a separate gridview where the user can select the parent.

I then send the parent’s id to the controller and the controller pass it to the child-gridview and child-model.

But if you insist on using the child-gridview filter, I suppose you can create an extra column and use it for the filter. In the child-model you will have a separate public parameter to store this value and then you can filter on it.

[s]

So just beneath the "If statement" that I put in the model you will add something like this:

$criteria->compare(‘child_id’,$this->public_parameter,false);[/s]

So you can use this public_parameter as $parentID.

Also in your model you will have to add:

public $public_parameter; /* here public_parameter starts with $ */

And in your model’s rules:

array(‘public_parameter,…’, ‘safe’, ‘on’=>‘search’),

Have a look at this wiki (note the handling of the public_parameter in the gridview):

http://www.yiiframework.com/wiki/323/dynamic-parent-and-child-cgridciew-on-single-view-using-ajax-to-update-child-gridview-via-controller-with-many_many-relation-after-row-in-parent-gridview-was-clicked/

Regards

Gerhard

5

The generic approach to this problem are the Group Conditions of ACL. Before you show your CGridView, you simply do:




Util::addGroupRestr('not', 'first_group_to_exclude', 'second_group_to_exclude' ...);

All the results you get are not part of any of the groups above. Note that the ‘and’ condition is also supported and nesting to infinity.

Regards