CGridView displaying "Select records NOT IN" [Solved]

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.


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




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(





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

Hope this helps someone.

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.

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.


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


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):



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.