Yii Framework Forum: How To Do "not In" Sql In Ar Model - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

How To Do "not In" Sql In Ar Model Get parent records that do not have any child records Rate Topic: -----

#1 User is offline   Gerhard Liebenberg 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 342
  • Joined: 07-January 12
  • Location:Stillbay - Western Cape - South Africa

Posted 01 March 2013 - 04:53 AM

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
0

#2 User is offline   kokomo 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 289
  • Joined: 23-July 10

Posted 01 March 2013 - 05:36 AM

CDbCriteria.addNotInCondition is your friend Posted Image
0

#3 User is offline   Gerhard Liebenberg 

  • Advanced Member
  • PipPipPip
  • Yii
  • Group: Members
  • Posts: 342
  • Joined: 07-January 12
  • Location:Stillbay - Western Cape - South Africa

Posted 01 March 2013 - 08:37 AM

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:

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

3. 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.
1

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users