Using 'through' To Get Data Of A Join Table

[size="5"]Schema:[/size]




user

---------------

id

email

password


recurrent_bill:

------------------

id

date_created

due_every(days)

amount




site

----------------

id

user_id

domain





addon

----------------

id

name

recurrent_bill_id




site_addon

--------------------

id

addon_id

site_id

date_created

expiry_date

last_billed_date



[size="5"]Relations:[/size]

User can have Many Sites

Site can have Many Addons

An Addon can belong to many Sites

An Addon has a Recurrent Bill ID

A Recurrent Bill Id can belong to many Addons

[size="5"]What is required:[/size]

A way to pull out siteAddon records that user owns using a scope that exists inside siteAddons Model.

[size="5"]Why is it required?[/size]

Billing purposes.

[size="5"]How do i expect it would work out:[/size]

A daily cron runs a yii command and loads Due Addon invoices such that:

a- inner joins site_addon with site on site.id = site_addons.site_id [This will be used to extract user_id and domain to which an addon belongs]

b- inner joins site_addon with addons on addon.id = site_addon.id

b- inner joins addon with recurrent_bill on recurrent_bill.id = addon.recurrent_bill_id [This is used to gather the due_every column for the addon’s relevant recurrent_bill]

c- applies the scope called due() which checks for:

CURDATE() > DATE_ADD(site_addon.last_billed_date , INTERVAL recurrent_bill.due_every DAY)

to filer site_addon records that are past their due date so an invoice can be issued.

[size="5"]What i have tried so far:[/size]




//relations inside User model:

'sites' => array(self::HAS_MANY, 'Site', 'user_id'),

'siteAddons' => array(self::HAS_MANY, 'SiteAddon', array('id' => 'site_id'), 'through' => 'sites')






//Controller:

$u = User::model()->with('siteAddons')->findByPk(5);

CVarDumper::dump($u, 10, true);



and i get:




PHP warning


array_diff() [<a href='function.array-diff'>function.array-diff</a>]: Argument #1 is not an array


/home/apps/public_html/yii-1.1.12.b600af/framework/db/ar/CActiveRecord.php(1956)


1944     public function mergeWith($criteria,$fromScope=false)

1945     {

1946         if($criteria instanceof CDbCriteria)

1947             $criteria=$criteria->toArray();

1948         if(isset($criteria['select']) && $this->select!==$criteria['select'])

1949         {

1950             if($this->select==='*')

1951                 $this->select=$criteria['select'];

1952             else if($criteria['select']!=='*')

1953             {

1954                 $select1=is_string($this->select)?preg_split('/\s*,\s*/',trim($this->select),-1,PREG_SPLIT_NO_EMPTY):$this->select;

1955                 $select2=is_string($criteria['select'])?preg_split('/\s*,\s*/',trim($criteria['select']),-1,PREG_SPLIT_NO_EMPTY):$criteria['select'];

1956                 $this->select=array_merge($select1,array_diff($select2,$select1));

1957             }

1958         }

1959 

1960         if(isset($criteria['condition']) && $this->condition!==$criteria['condition'])

1961         {

1962             if($this->condition==='')

1963                 $this->condition=$criteria['condition'];

1964             else if($criteria['condition']!=='')

1965                 $this->condition="({$this->condition}) AND ({$criteria['condition']})";

1966         }

1967 

1968         if(isset($criteria['params']) && $this->params!==$criteria['params'])

Stack Trace

#0	

+  /home/apps/public_html/yii-1.1.12.b600af/framework/db/ar/CActiveRecord.php(1956): array_diff(false, array("{{site}}.id", "{{site}}.user_id", "{{site}}.domain", "{{site}}.status", ...))

#1	

+  /home/apps/public_html/yii-1.1.12.b600af/framework/db/ar/CActiveRecord.php(2111): CBaseActiveRelation->mergeWith(array("select" => false))

#2	

+  /home/apps/public_html/yii-1.1.12.b600af/framework/db/ar/CActiveRecord.php(2204): CActiveRelation->mergeWith(array("select" => false), false)

#3	

+  /home/apps/public_html/yii-1.1.12.b600af/framework/db/ar/CActiveFinder.php(247): CHasManyRelation->mergeWith(array("select" => false))

#4	

+  /home/apps/public_html/yii-1.1.12.b600af/framework/db/ar/CActiveFinder.php(265): CActiveFinder->buildJoinTree(CJoinElement, "sites", array("select" => false))

#5	

+  /home/apps/public_html/yii-1.1.12.b600af/framework/db/ar/CActiveFinder.php(280): CActiveFinder->buildJoinTree(CJoinElement, "siteAddons")

#6	

+  /home/apps/public_html/yii-1.1.12.b600af/framework/db/ar/CActiveFinder.php(49): CActiveFinder->buildJoinTree(CJoinElement, array("siteAddons"))

#7	

+  /home/apps/public_html/yii-1.1.12.b600af/framework/db/ar/CActiveRecord.php(1295): CActiveFinder->__construct(User, array("siteAddons"))

#8	

+  /home/apps/public_html/yii-1.1.12.b600af/framework/db/ar/CActiveRecord.php(1426): CActiveRecord->query(CDbCriteria)

#9	

–  /home/apps/public_html/www.cloudsite.com/protected/controllers/SiteController.php(53): CActiveRecord->findByPk(5)

48         $viewOptions = array(

49             'title' => '',

50             'viewName' => 'index',

51         );

52         

53         $u = User::model()->with('siteAddons')->findByPk(5);

54		   CVarDumper::dump($u, 10, true);

55         exit;



[size="5"]What i have researched on this:[/size]

Found:

http://code.google.com/p/yii/issues/detail?id=2767

This seemed quite relevant but the trace attached there is different. Also the patch [ http://code.google.com/p/yii/issues/detail?id=2767#c6 ] is for CDbCriteria, something that never comes up in my trace. Tried it, no use.

Then i thought that may be its not working because indirectly its a Many-Many relationship e.g.

User – Has_Many --> Site

Site – Has_Many --> Addons

So:

User <-- Many_Many --> SiteAddons

and ‘through’ doesn’t support Many_Many.

BUT the bug report linked above also uses Many_Many, and that is actually a direct Many_Many, wonder why no one told him that ‘through’ doesn’t support that. May be i am missing something.

[size="5"]What other options i think are possible:[/size]

Create a column in site_addon of user_id OR

Create a table user_addon with (user_id, addon_id)

and use that for billing purpose. I have a feeling this way would be fast but changing addons of a site would result in insert in 2 tables, site_addon and user_addon, ouch. If we use one table its a little better but violates Normalization.

I spent all day on this, asked on IRC but i can’t somehow seem to hold a grasp of it. Any, literally any, help would be heavily appreciated.

I’m not sure it’s the problem, as I would expect another kind of error, but you don’t have a ‘site_id’ field in you ‘site_Addon’ table

Correction:

There is no domain_id, it was actually site_id. Modified in original post. Thanks for pointing it out. So back to square one.

Still not working?

[s]Ok, I’m quite guessing since I’m too lazy to try. I think it’s tricky. Technically, using a HAS_MANY relation in the with() property means you’re looking for an array of models, while findByPk() retrieves one model only, I think the combination is what causes the error.

So, imho


$u = User::model()->with('siteAddons')->findAll('t.id = 5');

This way you get all the linked models at once (eager loading), and you’ll be able to:


var_dump($u);

foreach ($u as $user) {

    var_dump($user->siteAddons);

}

[/s]

Maybe the ‘together’ option?


$u = User::model()->with('siteAddons')->together()->findByPk(5);

Nope, nothing new with together(), same error message.