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?
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).
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?
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:
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.
(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 customert LEFT OUTER JOIN userusers ON (users.customer_id=t.id) LEFT OUTER JOIN executivecontributedExecutives 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;
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.
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.
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.
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.
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.
function countContributedExecutives($total, $user) {
return $total + count($user->contributedExecutives);
}
// model here is an instance of Customer
$contributedExecutivesCount = array_reduce($model->users, 'countContributedExecutives', 0);
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)