How To Do "not In" Sql In Ar Model

Yii 1.1 General Discussion
1

Hi guys

I want to retrieve all records in parentTable that are not referenced in childTable.

i.e. I want all parent records that do not have any child records yet.

It works if I use the following “NOT IN” sql code in the parentTable’s search() function:


$criteria->condition = 'parentID NOT IN

	(SELECT	childTable.parentID_fk

	FROM	childTable

	WHERE	childTable.parentID_fk = :par1

	)';

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

The problem is that the sql function (which gathers all the relevant child records) does not incorporate the child model’s defaultScope() and thus retrieves the wrong child records (unless I repeat all the defaultScope() conditions in the above sql).

Is there any other - more Active Record - way of achieving the same result - without using sql?

Thanx

2

CDbCriteria.addNotInCondition is your friend

3

Thanx Kokomo, just what I needed.

For those interested, it works like this:

I have a many-many relation between parentTable and outerparentTable.

Between them I have a junction table called childTable.


parentTable    -> 	childTable 	<- 	outerparentTable

(parentID)	(parentID_fk, outerparentID_fk)	(outerparentID)

When I display a record of parentTable, I also want to display a list of all the "available" outerparentTable records that are NOT YET linked to parentTable.

  1. In the controller, I store the parentTable’s parentID in a variable called storedParentID. (I store it either in session or in a base-model where the outerparentTable’s model will be able to access it.)

I then do the following in the outerparentTable’s search() function:

  1. I get a list of all current Children, being childTables’ outerparentID_fk, where parentID_fk = storedParentID.

  2. Then I filter the outerparentTable models by including only records with outerparentID that are NOT IN the currentChildren list.




/* This is in outerparentTable's search() function */


$currentChildren = childTable::model()->findColumn(

	'outerparentID_fk', 'parentID_fk = '. $this->storedParentID);


$criteria->addNotInCondition('outerparentID', $currentChildren);

To create the list, you can use findAll() and then foreach().

But I used findColumn() which is part of the CAdvancedArFindBehavior extension.