Scope All Records Without Related Objects

Hi out there,

I am trying to make some more or less complex scopes:

I have 3 models, for simplicity let us call them A B and C, while

A hasMany B

and

B belongsTo C

I want to query a specific A with it’s Bs belonging to scope “bScope”.

Simple.

But now the scope wants to limit the Bs to those which do not belong to a C belonging to scope cScope

So what I try to do is:




$model = A::model()->with('bs:bScope')->findByPk(1);



What I would expect is to find all my scope-stuff in the where clause (which would not work as I would like it too, as c.col = 1 and c.id IS NULL conflicts, C.col = 1 would neet to go into ON clause)

What happens instead is that the scope-stuff wents into the ON clause, where it throws an mysql-error because c.id is unknown there as it is only joined in later:




// expected

SELECT /* ... */

FROM A a

LEFT OUTER JOIN AB ab

	ON ab.a_id = a.id

LEFT OUTER JOIN B bs

	ON bs.id = ab.b_id

LEFT OUTER JOIN C

	ON c.id = bs.c_id

WHERE

	A.id = 1 AND (bs.c_id IS NULL OR c.id IS NULL) AND c.col = 1


// what really happened:

SELECT /* ... */

FROM A a

LEFT OUTER JOIN AB ab

	ON ab.a_id = a.id

LEFT OUTER JOIN B bs

	ON bs.id = ab.b_id AND (bs.c_id IS NULL OR c.id IS NULL) /* c.id is unknown in here as c is not joined yet */

LEFT OUTER JOIN C c

	ON c.id = bs.c_id AND c.col = 1

WHERE

	A.id = 1




The following Code-Snippets showing the relations and the scopes are of course simplified - if there are any non logical errors in it, its because of simplification




<?php

// A.php

class A extends CActiveRecord {

	// ...


	public function relations() {

		return array(

			'ab' => array(self::HAS_MANY, 'AB', 'a_id'),

			'b' => array(

				self::HAS_MANY,

				'B',

				'b_id',

				'through' => 'ab',

			),

		);

	}


	// ...

}


// B.php

class B extends CActiveRecord {

	// ...


	public function relations() {

		return array(

			'c' => array(self::BELONGS_TO, 'C', 'b_id'),

		);

	}

	

	public function bScope()

	{

		$ta = $this->getTableAlias(false,false).".";

		$criteria = new CDbCriteria;

		$criteria->with = 'c:cScope';

		$criteria->addCondition($ta.'c_id IS NULL OR relatedC.id IS NULL');

		$this->getDbCriteria()->mergeWith($criteria);


		return $this;

	}


	// ...

}

// C.php

class C extends CActiveRecord {

	//...

	

	public function scopes()

	{

		return array(

			'cScope' => array(

				'condition' => $this->getTableAlias(false,false).'.col = 1'

			),

		);

	}


	// ...

}



Any Ideas how I can put some conditions into ON-condition and others into WHERE-condition?