Yii Framework Forum: Cactivefinder Bug When One Fetching Relation That Uses Through + Another Nested Relation That Uses The Same Through Table - Yii Framework Forum

Jump to content

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

Cactivefinder Bug When One Fetching Relation That Uses Through + Another Nested Relation That Uses The Same Through Table

#1 User is offline   kipras 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 3
  • Joined: 31-August 12

Posted 09 July 2013 - 04:05 AM

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'"
)));


The problem:
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:
Attached File  bug.zip (381.16K)
Number of downloads: 0

Extract it to apps/bug/ of your yii installation, and run it in your browser, e.g.:

Quote

localhost/[path_to_yii]/apps/bug/index.php?r=site/index


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.
0

#2 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,110
  • Joined: 16-February 11
  • Location:Japan

Posted 09 July 2013 - 09:57 AM

Hi kipras,

I would design the relations like the following:
// Base.php:
public function relations()
{
        return array(
                'throughsForA' => Array(self::HAS_MANY, 'through', 'Base_id'),
                'throughsForB' => Array(self::HAS_MANY, 'through', 'Base_id'),
        );
}

// Through.php:
public function relations()
{
        return array(
                'innerAs' => Array(self::HAS_MANY, 'innerA', 'Through_id'),
                'innerBs' => Array(self::HAS_MANY, 'innerB', 'Through_id'),
        );
}

'throughsForA' and 'throughsForB' might look redundant, but it's necessary to have them both when you want to join the same table twice in a single query.

And then the query could be:
Base::model()->findAll(new CDbCriteria(Array(
        'with' => Array(
                'throughsForA',
                'throughsForA.InnerAs',
                'throughsForB',
                'throughsForB.InnerBs',
        ),
        'condition' => "innerAs.innerAField = 'innerA 1'"
)));

As far as I know, when you want to join 'InnerAs', you have to specify not only 'throughsForA.InnerAs' but also 'throughsForA'.

And it might be something like the following if we want to use 'through' ...
// Base.php:
public function relations()
{
        return array(
                'throughsForA' => Array(self::HAS_MANY, 'through', 'Base_id'),
                'throughsForB' => Array(self::HAS_MANY, 'through', 'Base_id'),
                'innerAs' => Array(self::HAS_MANY, 'InnerA',
                         Array('id' => 'Through_id'), 'through' => 'throughsForA'),
                'innerBs' => Array(self::HAS_MANY, 'InnerB',
                         Array('id' => 'Through_id'), 'through' => 'throughsForB'),
                ),
        );
}

Base::model()->findAll(new CDbCriteria(Array(
        'with' => Array(
                'InnerAs',
                'InnerBs',
        ),
        'condition' => "innerAs.innerAField = 'innerA 1'"
)));

I'm not sure if it works or not, because I don't have much experience in 'through'.
0

#3 User is offline   kipras 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 3
  • Joined: 31-August 12

Posted 10 July 2013 - 03:39 AM

Thanks softark, i didn't know you need to specify two relations when joining the same table twice. I created redundant relations and now it works fine.

I still think this is a framework issue though, because this is a bit counter intuitive and difficult to track down when this problem happens. Perhaps a warning should be issued when eagerly loading the same relation twice (via 'through').

Anyway, thanks for the info!
0

#4 User is offline   softark 

  • Keep It Simple
  • Yii
  • Group: Moderators
  • Posts: 2,110
  • Joined: 16-February 11
  • Location:Japan

Posted 10 July 2013 - 04:38 AM

Yes, I agree with you that it's a little confusing. But in other situations you may want to join the link table just once for both A and B.

Declaring redundant relations can sometimes be a good idea when you deal with a complicated query. I often make 2 dedicated definitions for a HAS_MANY relation, one for filtering the main model by the relation, and the other for fetching the related models themselves.
0

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