Use Joins For Multiple Tables

Yii 2.0 General Discussions
1

I have 3 tables: posts, fans, comments and i want to use joinWith() to get the posts with their comments and the fan name (in fans table) for both post and comments. what i wrote is this query:


facebook_posts::find()->joinwith('fans')->joinWith('comments')->all();

and I added these two functions for the relations:




    public function getfans() {

        return $this->hasOne(Fans::className(), ['id' => 'from_id'])->from(fans::tableName() . ' FBF');

    }

    public function getComments() {

        return $this->hasMany(Comments::className(), ['parent_id' => 'id'])->from(comments::tableName() . ' FBC');

    }

this gives me the posts and the data of the fan who wrote the post and its comments,

but what i need is the data of fan that wrote the comments also, so how can i join comments with fans table ??

2

Not tested this… but you may try simplifying your joins with something like below:




facebook_posts::find()->joinWith([

    'fans',

    'comments' => function($query) {

        $query->joinWith('fans');

    }

])->all();
3

i tried it but i check the query in yii debugger and i get this query:

SELECT “FBC”.* FROM “facebook_comments” “CR” LEFT JOIN “facebook_fans” “FBF” ON “FBC”.“from_id” = “FBF”.“id” WHERE “facebook_comments FBC”.“reply_on” IN (‘1631717’, ‘1631724’);

it is correct but it gives me error in ‘WHERE’ clause,

i dont know why table name is written before the alias ‘FBC’ again in WHERE clause.

4

Not sure why. Try this method instead (looks more better for your scenario):




facebook_posts::find()->joinWith(['fans', 'comments.fans'])->all();

Theoretically, with the above, you should get posts, post fans, post comments, and post comments fans.