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
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?
CDbCriteria.addNotInCondition is your friend
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.
- 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:
I get a list of all current Children, being childTables’ outerparentID_fk, where parentID_fk = storedParentID.
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);
To create the list, you can use findAll() and then foreach().
But I used findColumn() which is part of the CAdvancedArFindBehavior extension.