"through" relation with ActiveRecord failure due to FK exception

I’ve got a schema defined as follows:

[indent]customer:

id

user:

id


customer_id

executive:

id


created_by_customer_id[/indent]

I’ve got the following relationship in user:

[indent] ‘contributedExecutives’ => array(self::HAS_MANY, ‘Executive’, ‘created_by_user_id’),

[/indent]

that works great. I’d like to define a similar relationship in customer, getting all executives that have been created by any users that belong to the customer. I’ve tried various permutations, and I’ve tried using ‘through’ in the relationship, but I can’t seem to make it work. Yii reports:

OK that totally makes sense, but then I’m not quite getting how I can define this relationship. Is it possible with AR?

something like this




return array(

'users'=>array(self::HAS_MANY,'User','user_id'),

'executives'=>array(self::HAS_MANY,'Executive','created_by_user_id','through'=>'users')

);



Yeah that won’t work. The users relation will be fine, but for the executives one, You’ll get the error message I described above (where created_by_user_id is specified with an invalid FK for users).

For a moment, forget about the second relationship and try this "old style" AR (using the relationship you defined before)




//$models = Customer::model()->with(array('users', 'users.contributedExecutives'))->findAll();


// array not needed

$models = Customer::model()->with('users', 'users.contributedExecutives')->findAll();



/Tommy

I see what you’re going for here – for some reason that is returning a funky result.

I’ve got 3 users, 2 have 1 company each associated with them, the 3rd one has none. Total should be 2, but:


count($model->with('users', 'users.contributedExecutives')->findAll());



returns 5. Looks like there may be an outer join issue or something?

Argh I can make this work in straight up SQL so quickly. :) I really would like to use the ActiveRecord relation properly though.

Here is the relevant generated SQL from that line (edited for brevity by removing unnecessary columns):


2011/06/23 14:51:58 [trace] [system.db.CDbCommand] Querying SQL: SELECT `contributedCompanies`.`id` AS

 `t1_c0`, `contributedCompanies`.`created_by_user_id` AS `t1_c48`, `contributedCompanies`.`last_updated_user_id`

 AS `t1_c49`,  FROM `company` `contributedCompanies`  LEFT OUTER JOIN `user` `users` ON

 (`users`.`customer_id`=`contributedCompanies`.`id`) WHERE (`users`.`customer_id`=:ypl0)

The SQL makes me confused. Was it generated by AR? I only see one join and the column aliases starts with t1 not t0 (expected alias for the primary table columns).

Note that in the relationship definitions FK should be used. That would be ‘customer_id’ for the ‘users’ relationship and ‘created_by_user_id’ for the ‘contributedExecutives’ relationship.

Could you please check the schema in your first post, you have ‘created_by_customer_id’ in the ‘executive’ table.

BTW are companies used instead of executives in your SQL example?

/Tommy

Yes, the SQL was generated by AR. It was for a relation I was working on for "contributedCompanies" not "contributedExecutives" but the "company" and "executive" tables are set up identically; sorry for the confusion

Again, at present, this

This is what I have as relations in the Customer model:




        /**

         * @return array relational rules.

         */

        public function relations() {

                return array(

                        'contact' => array(self::BELONGS_TO, 'Contact', 'contact_id'),

                        'users' => array(self::HAS_MANY, 'User', 'customer_id'),


                        'contributedExecutives' => array(self::HAS_MANY, 'Executive', 'created_by_user_id', 'through' => 'users'),

                );

        }

As I said, accessing contributedExecutives AR relation of Customer blows up with this error:

[indent]

CDbException

The relation "users" in active record class "Customer" is specified with an invalid foreign key "created_by_user_id". There is no such column in the table "user".[/indent]

Accessing the "users" relation from the Customer model works just fine.

Here’s the snippet of code:


<?php $this->widget('zii.widgets.CDetailView',                                                                                                                  

                    array(                                                                                                                                      

                          'data' => $model,                                                                                                                     

                          'attributes' => array(                                                                                                                

                                                array('label' => 'Contributed executives',                                                                      

                                                      /*'value' => count($model->with('users', 'users.contributedExecutives')->findAll()),*/                    

                                                      'value' => count($model->contributedExecutives),                                                          

                                                      'type' => 'number',                                                                                       

                                                      ),                                                                                                        

                                                ),                                                                                                              

                          )); ?>

(if I get this working I’ll define a contributedExecutiveCount STAT relationship)

Here is the (shortened) "show create table mysql" output:

[sql]CREATE TABLE executive (

id int(10) unsigned NOT NULL AUTO_INCREMENT,

company_id int(10) unsigned NOT NULL,

firstname varchar(50) DEFAULT NULL,

lastname varchar(50) DEFAULT NULL,

title varchar(100) DEFAULT NULL,

created_by_user_id int(10) unsigned DEFAULT NULL,

PRIMARY KEY (id),

KEY company_id (company_id),

KEY created_by_user_id (created_by_user_id),

CONSTRAINT executive_ibfk_4 FOREIGN KEY (created_by_user_id) REFERENCES user (id),

CONSTRAINT executive_ibfk_1 FOREIGN KEY (company_id) REFERENCES company (id),

) ENGINE=InnoDB[/sql]

If I use the line that is commented out above the relation, it “works”, but I’m getting funky results (count is higher than it should be). Here’s a less abbreviated trace output when I use the ‘with/findAll’ line:

[sql]2011/06/24 07:40:08 [trace] [system.db.CDbCommand] Querying SQL: SELECT t.id AS t0_c0, t.name AS t0_c1, users.id AS t1_c0, users.name AS t1_c1, users.customer_id AS t1_c4, contributedExecutives.id AS t2_c0, contributedExecutives.company_id AS t2_c2, contributedExecutives.firstname AS t2_c3, contributedExecutives.lastname AS t2_c4, contributedExecutives.created_by_user_id AS t2_c13 FROM customer t LEFT OUTER JOIN user users ON (users.customer_id=t.id) LEFT OUTER JOIN executive contributedExecutives ON (contributedExecutives.created_by_user_id=users.id)[/sql]

This returns “5” for my user that I know only has 2 contributed executives. Here’s some simple SQL that I’d like to replicate with AR:

[sql]mysql> SELECT count(executive.id) FROM user,executive WHERE executive.created_by_user_id = user.id AND user.customer_id = 2;

±---------------+

| count(executive.id) |

±---------------+

| 2 |

±---------------+

1 row in set (0.00 sec)

[/sql]

I’m in a hurry now so just want to suggest an alternative.

What about using user as primary table instead of customer?

User BELONGS_TO Customer

User HAS_MANY Executive

Customer BELONGS_TO Contact (as before). BTW, I can’t see a FK contact_id in Customer!?

User.php




'customer' => array(self::BELONGS_TO, 'Customer', 'customer_id'),

'contributedExecutives' => array(self::HAS_MANY, 'Executives', 'created_by_user_id'),



(untested)

/Tommy

Tommy:

Thanks for the input. The User model already has both of the relations that you defined above. And they work fine! The problem arises when I want to get a list/count of ALL of the executives for ALL users belonging to a customer.

There is a FK to contact_id in Customer. If I left that out of any cut-and-pastes my apologies, but we aren’t worry about contact info at the moment. :)

Here is a visual representation of the tables and FKs.

Remember, I’m trying to get a list/count of all executives created by users belonging to a particular customer.

What do you use to draw the schema ? It looks good.

Hah, here’s a free tool: http://ondras.zarovi.cz/sql/demo/

Anyone have ideas on how to make this AR relation work?

Based on the following ER model I would define the following relations:

[center]

[/center]




// User model

'rel_user_cust'=>array(self::BELONGS_TO, 'Customer', 'fk_customer_id'),

'rel_user_contributedExecutives'=>array(self::HAS_MANY, 'Executives', 'fk_created_by_user_id'),

'rel_user_cont'=>array(self::HAS_MANY, 'Contact', 'fk_contact_id'),


// Customer model

'rel_cust_cont'=>array(self::HAS_MANY, 'Contact', 'fk_contact_id'),

'rel_cust_user'=>array(self::HAS_MANY, 'User', 'fk_customer_id'),


// Contact model

'rel_cont_user'=>array(self::BELONGS_TO, 'User', 'fk_contact_id'),

'rel_cont_cust'=>array(self::BELONGS_TO, 'Customer', 'fk_contact_id'),


// get all executives with their corresponding users and customers where contact PK is 1

$execByContact = Contact::model()->with('rel_cont_user.rel_user_contributedExecutives','rel_cont_cust')->findAllByPK(1);

$users = $execByContact->rel_cont_user;

$executives = $execByContact->rel_user_contributedExecutives;

$customers = $execByContact->rel_cont_cust;



Hope this helps and is not completly wrong cause it is not tested ::)


//customer

return array(

'users'=>array(self::HAS_MANY,'User','fk_customer_id'),

'executives'=>array(self::HAS_MANY,'Executive','fk_created_by_user_id','through'=>'users')

);

The relationship between customer records and user records and contact is not one to many, it is one to one. So those are BELONGS_TO relationships, so…no this is not how it works.

Leave contact completely out of the situation, it is ancillary to the question really and I shouldn’t have brought it into the equation.

Gustavo,

Thank you very much for continuing to try to work on this with me. Unfortunately, if you look back, what you just suggested is exactly what I have been trying to do which is throwing a CDbException complaining that users doesn’t have a foreign key created_by_user_id. Duh, we want it to look at Executive for that, but it seems Yii will not.

Try this




'executives'=>array(self::HAS_MANY,'Executive','users.fk_created_by_user_id','through'=>'users')



(untested)

Edit: no this of course cannot work either. BTW thank you for finally showing us a better schema. Too many names and other things changing until now. Did you try the "old style" query i posted? Or I guess this thread is just about having through to work exactly like you want. Sorry, I have too start experimenting with through myself.

Edit2: Do you get the error without trying to query the relation (just by instantiating the model)? If so read on. I had a look at the example in the guide. It’similar to your case but a m:n relation is used, thus the real chain is 1:n, n:1, 1:n but the relationship declaration chain use 1:n only. Interesting, but I have to admit I don’t understand how it works. In your case the real chain are also 1:n.

/Tommy

This one?


$model->with('users', 'users.contributedExecutives')->findAll()

Yes, I did. Unfortunately, it doesn’t work properly – the problem is it is finding all of the Customer models, not just users for the particular Customer model (held in $model) in question. And while you are right, I’m trying to get “through” to work as I expect it to, or at least I’m wondering if it is even possible to accomplish this relation with Yii, I’m ready to do other things.

This bit of code is kind of nasty, but it works:


$contributedExecutives = array();

foreach ($model->users as $user) {

    $contributedExecutives = array_merge($contributedExecutives, $user->contributedExecutives);

}

and to count the same:


                       

function countContributedExecutives($total, $user) {

    return $total + count($user->contributedExecutives);

}


// model here is an instance of Customer

$contributedExecutivesCount = array_reduce($model->users, 'countContributedExecutives', 0);

Yii, there must be a nicer way to do this??

Hey Preston,

looks like through has many expect a foreign key in first table to connect to a primary key in the second table, like in pivot tables

been that way, this should work:




//customer

return array(

'users'=>array(self::HAS_MANY,'User','fk_customer_id'),

'executives'=>array(self::HAS_MANY,'Executive','','on'=>'users.id=executives.fk_created_by_user_id','through'=>'users')

);



Gustavo

I wish. But that results in a SQL fail because even though you are specifying your own "on" clause, Yii generates an empty "on" clause before it (i.e. it will generate "ON ()" which is not valid SQL):

[sql]CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation:

1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version

for the right syntax to use near ') WHERE (users.id = contributedExecutives.created_by_user_id) AND

(users.`cust’ at line 1.

The SQL statement executed was:

SELECT contributedExecutives.id AS t1_c0,contributedExecutives.created_by_user_id AS t1_c13

FROM `executive` `contributedExecutives` 


LEFT OUTER JOIN `user` `users` ON () 


WHERE (users.id = contributedExecutives.created_by_user_id) 


    AND (`users`.`customer_id`=:ypl0) 

[/sql]

Do we file a bug? :huh: