How to add a condition on which to join in Yii2 in dataProvider?

Question

Yii

There is a fully working query in sql:

SELECT u.id, u.firstname, u.lastname, bsr.requests, bsr.found_sum
FROM user u
LEFT JOIN billing_stat_request bsr ON (bsr.id = u.id AND bsr.date = '2016-06-03')
WHERE u.company_id = 1

Based on it, you need to create an activeDataProvider.
Created a method in the User model:

public function getBillingStatRequest()
    {
        return $this->hasOne(BillingStatRequest::className(), ['id' => 'id']);
    }

I create dataProvider like this:

$model = User::find()
                ->select('user.id, lastname, firstname, middlename')
                ->where(['company_id' => $company_id])
                ->joinWith('billingStatRequest bsr', true, 'LEFT JOIN');

        $dataProvider = new ActiveDataProvider([
            'query' => $model
        ]);

In this case, it is not taken into account that you also need to join by bsr.date = '2016-06-03'.
Tried to do it like this:

$model = User::find()
                ->select('user.id, lastname, firstname, middlename')
                ->where(['company_id' => '1'])
                ->join('LEFT JOIN', 'billing_stat_request bsr', "bsr.id = user.id AND bsr.date = 2016-06-03" );

        $dataProvider = new ActiveDataProvider([
            'query' => $model
        ]);

Тоже не работает как надо.
Как это реализовать?
Может не через activeDataProvider?
В итоге результаты выборки мне надо отобразить в GridView::widget.

Answer 1

public function getBillingStatRequest()
{
return $this->hasOne(BillingStatRequest::className(), ['id' => 'id'])->andOnCondition(['bsr.date' => '2016-06-03']);
}

Answer 2

вот так у меня работает:

->leftJoin(['bsr'=>'billing_stat_request'], "bsr.id = user.id AND bsr.date = '2016-06-03'");