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?