AR with inner join

Yii 1.1 General Discussion
1

Hello,

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 i.to = m.id 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(

                        'criteria'=>array(

                            'with'=>array(

                                'invitation'=>array(

                                    'on'=>'invitation.to = t.id 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 'invitation.to' 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 (invitation.to = t.id 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.

2

The following criteria works without "on clause":




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

                        'criteria'=>array(

                            'with'=>array(

                                'invitations'=>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'))

ORDER BY time DESC

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…

3

Why do you use INNER JOIN?

/Tommy

4

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.

5

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.

6

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

/Tommy