Hi,
i seem to have run into a complicated CActiveFinder bug that only happens when you use
a through relation with more than one table being fetched through it.
I created a simple (i think) example which illustrates the problem. It uses an SQLite database.
Let’s say you have four tables and four models generated from them:
CREATE TABLE "base" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "baseField" VARCHAR(45) NOT NULL );
CREATE TABLE "through" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "Base_id" INTEGER NOT NULL , "throughField" VARCHAR(45));
CREATE TABLE "inner_a" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "Through_id" INTEGER NOT NULL , "innerAField" VARCHAR(45) NOT NULL );
CREATE TABLE "inner_b" ("id" INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL , "Through_id" INTEGER NOT NULL , "innerBField" VARCHAR(45) NOT NULL );
Table | Model
-----------------------
base | Base
through | Through
inner_a | InnerA
inner_b | InnerB
Basically you have:
Base ---> Through ---> InnerA
\---> InnerB
The relations are specified like this:
Base.php:
public function relations()
{
return array(
'throughs' => Array(self::HAS_MANY, 'through', 'Base_id'),
'innerBsWithThrough' => Array(self::HAS_MANY, 'InnerB', Array('id' => 'Through_id'),
'through' => 'throughs',
),
);
}
Through.php:
public function relations()
{
return array(
'innerAs' => Array(self::HAS_MANY, 'innerA', 'Through_id'),
'innerBs' => Array(self::HAS_MANY, 'innerB', 'Through_id'),
);
}
InnerA.php:
public function relations()
{
return array(
);
}
InnerB.php:
public function relations()
{
return array(
);
}
When fetching data, the innerA relation is fetched using ‘throughs.InnerAs’,
whereas the innerB relation is fetched using the innerBsWithThrough relation, which uses
the ‘through’ clause.
The problem happens when fetching Base records like this:
Base::model()->findAll(new CDbCriteria(Array(
'with' => Array(
'throughs.innerAs',
'innerBsWithThrough',
),
'condition' => "innerAs.innerAField = 'innerA 1'"
)));
[size="4"]The problem:[/size]
You get an SQL error, because the innerAs table is not JOINed and so it is impossible to specify
the WHERE condition.
However, if you remove the ‘innerBsWithThrough’ relation from being eagerly loaded, then the
‘innerAs’ table is JOINed and everything works as expected. This is what leads me to believe that
this must be a bug, probably somewhere in the CActiveFinder system.
The biggest problem for me is that i cannot work around this issue, there are parts in my real
application (where i found this bug) where i need to query a model in this specific way,
because the ‘innerBsWithThrough’ relation equivalent uses an ‘on’ condition, so i can’t simply
replace it with ‘throughs.innerBs’.
I attached a sample test application with this configuration:
4507
Extract it to apps/bug/ of your yii installation, and run it in your browser, e.g.:
At the very bottom of the "Application Log" you will see the failed SQL statement.
Additional info:
-
operating system - Windows 7 x64
-
Web server - Apache 2
-
browser type - Google Chrome
-
Yii version:
Tested on 1.1.12, 1.1.13 and current 1.1.14 trunk (443c6cf6113716335eb7f680614e9dd4679d33da, 2013-07-08)
Bug is present in all these versions.