Find with left join and clasule where in the two tables

Yii 2.0 General Discussions
1

Hello,

I tried do a query on find() but without success:

$list = $this->find()

        ->leftJoin(


            'member_post', 


            ['member_post.memberId' => 'member.id'])  //this it's ok


        ->where([


               'member.status' => 1,


               'member_post.postId'=>1 //this isn't ok because return all posts(1, 2, 3,...)


        ])


        ->with('memberPost')


        ->asArray()


        ->all();

To do the query with success I use:

$sql = "select m., mt. from member m left join member_post mp on m.id=mp.memberId where m.status=1 and mp.status=1";

$list = \Yii::$app->db->createCommand($sql, [])->queryAll();

But I prefer the first method because I want the structure like this:

["id":"1", "name": "xpto", "memberPost":["memberId":1, "postId":1]] (is better because can exist more association between member and post)

And with the second method the items come:

["id":"1", "name": "xpto", "memberId":"1", "postId":"1"]

Someone can help m? Any question feel free to ask.

Thanks!

2

In Yii 2, the main models and the related models are always fetched with the separated queries.

In the above, 2 queries will be executed. In the 1st query, leftJoin() and where() are used to filter the main models (members). It will find all the members with the status being 1 and having a post of id being 1. But the result set of the 1st query is not used to populate the related models. Instead, Yii will execute the 2nd query to get the related models(memberPosts). Because you have not specified any condition, the 2nd query will gather all the posts according to the definition of the relation.

This should work as expected:




$list =  $this->find()

            ->leftJoin( // this is for the main models

                'member_post', 

                ['member_post.memberId' => 'member.id'])

            ->where([  // this is for the main models

                   'member.status' => 1,

                   'member_post.postId'=>1

            ])

            ->with([    // this is for the related models

                   'memberPost' => function($query) {

                           $query->where(['postId' => 1]);

                    },

            ])

            ->asArray()

            ->all();

Please take a look at the following wiki:

Drills : Search by a HAS_MANY relation in Yii 2.0

Especially Task #4 and Task #4-B

3

Many thanks! Worked perfectly ;)

Now I realize better how use the relationship between the tables! I was with problems to understand…

Thanks!!