AR with inner join


I’ve got Member(AR class) which has id(primary key) and other properties, and corresponds to database table member. I have another table invitation for relationship between members to represent “member a invites member b”. Thus, it has two fields from and to.

My goal is to achieve this query using AR in order to find out whom I invited before:

SELECT username FROM member m INNER JOIN invitation i ON = AND i.from = $_GET['id'];

I filled out the relations() method in Member AR class:

'invitation'=>array(self::MANY_MANY, 'Member', 'invitation(from, to)'),

I also tried to create CActiveDataProvider as follows:

$invitationDataProvider=new CActiveDataProvider('Member', array(




                                    'on'=>' = AND invitation.from = '.$_GET['id'],

                                    'joinType'=>'INNER JOIN',



                            'order'=>'time DESC',



It throws the exceptions:

CDbCommand failed to execute the SQL statement: SQLSTATE[42S22]: Column not found: 1054 Unknown column '' in 'on clause'. The SQL statement executed was: SELECT COUNT(DISTINCT `t`.`id`) FROM `member` `t` INNER JOIN `invitation` `invitation_invitation` ON (`t`.`id`=`invitation_invitation`.`from`) INNER JOIN `member` `invitation` ON (`invitation`.`id`=`invitation_invitation`.`to`) AND ( = AND invitation.from = 74) 

It seems that I did not specify both relationship and criteria because I don’t understand the relationship correctly. Please, correct me. Thank you.

The following criteria works without "on clause":

            $invitationDataProvider=new CActiveDataProvider('Member', array(




                                    'joinType'=>'INNER JOIN',

                                    'order'=>'time DESC',

                                    //'on'=>'invitations.from = '.$_GET['id'],





CWebLogRoute says SQL as shown below:

SELECT `t`.`id` AS `t0_c0`, `invitations`.`id` AS `t1_c0`,

`invitations`.`username` AS `t1_c1`, `invitations`.`password` AS `t1_c2`,

`invitations`.`ssn` AS `t1_c3`, `invitations`.`gender` AS `t1_c4`,

`invitations`.`name` AS `t1_c5`, `invitations`.`nickname` AS `t1_c6`,

`invitations`.`email` AS `t1_c7`, `invitations`.`salary` AS `t1_c8`,

`invitations`.`region` AS `t1_c9`, `invitations`.`job` AS `t1_c10`,

`invitations`.`style` AS `t1_c11`, `invitations`.`personality` AS `t1_c12`,

`invitations`.`body_type` AS `t1_c13`, `invitations`.`image` AS `t1_c14`,

`invitations`.`detail` AS `t1_c15`, `invitations`.`stick_total` AS

`t1_c16`, `invitations`.`impression_total` AS `t1_c17`,

`invitations`.`date_count` AS `t1_c18`, `invitations`.`last_time` AS

`t1_c19`, `invitations`.`last_ip` AS `t1_c20`, `invitations`.`join_time` AS

`t1_c21`, `invitations`.`join_ip` AS `t1_c22`, `invitations`.`login_count`

AS `t1_c23`, `invitations`.`active` AS `t1_c24` FROM `member` `t` INNER

JOIN `invitation` `invitations_invitations` ON

(`t`.`id`=`invitations_invitations`.`from`) INNER JOIN `member`

`invitations` ON (`invitations`.`id`=`invitations_invitations`.`to`)  WHERE

(`t`.`id` IN ('73', '74', '75', '76', '77', '78', '79', '80', '81', '82'))


I want to access the properties of "Member" AR after filtering some rows (on clause).

If I put “on” clause, it complains “invitation” table doesn’t exist because it is trying “INNER JOIN” twice.

I need your help…

Why do you use INNER JOIN?


if i understand you correctly, member has NO relationship with invitation, its invitation that has two fields that BELONG_TO member.

You should define your relationship in Invitation model:

'memberFrom'=>array(self::BELONGS_TO, 'Member', 'from'),

'memberTo'=>array(self::BELONGS_TO, 'Member', 'to'),

Then, inside invitation you can access $model->memberFrom->username.

Hope this gives you a direction.

The way of electus solved my problem quite easily as tri asked me why I need INNER join.

I’d better review SQL first… Thank you.

You have to understand that we (forum users) are not always prepared to spend time on searching for older posts discussing similar issues (which I was aware of existed). I’m still curious, though ;)