CODEWITHSUNDEEP
phpyii2query-builder
Veshraj Joshi
Veshraj Joshi

Reputation: 3579

subquery in andFilterWhere in yii2

I have to execute this query

select count(id) as numberofcompanies, sum(offered_value) as total_offered_value from campaign_comapany
       where ( campaign_id in (select id from campaing where user_id = current_user ) or campaing_company.sp_user_id = current_user)
             and campaign_company.status = '3'

And I wrote the query like this in yii2- 

$query->select(['count(id) as numberofcompanies','sum(offered_value) as total_offered_value'])
                                 ->from('campaign_company')
                                 ->andFilterWhere(['or',['in','campaign_id',(new yii\db\Query())->select(['id'])->from('campaign')->where(['=','user_id',Yii::$app->user->id])],['=','campaign_company.sp_user_id',Yii::$app->user->id]])
                                 ->andWhere(['=','status','3'])
                                 ->one();

But it gives me error with unknow column campaign_company.user_id but working if I just use where like following-

$query->select(['count(id) as numberofcompanies','sum(offered_value) as total_offered_value'])
                                 ->from('campaign_company')
                                 ->where(['in','campaign_id',(new yii\db\Query())->select(['id'])->from('campaign')->where(['=','user_id',Yii::$app->user->id])])
                                 ->andWhere(['=','status','3'])
                                 ->one();

What should I do to get the result mentioned sql query, I don't want to hit database server two time, thanks in advance

Upvotes: 1

Views: 2313

Answers (2)

uldis
uldis

Reputation: 362

Use join:

$query->select(['count(id) as numberofcompanies','sum(offered_value) as total_offered_value'])
       ->from('campaign_company')
       ->innerJoin('campaign','`campaign`.`id` = `campaign_company`.`campaign_id`') 
       ->where([
           ['=','campaign.user_id',Yii::$app->user->id])],
           ['=','campaign.campaign_company.sp_user_id',Yii::$app->user->id],
           ['=','campaign_company.status','3']
         ]
       ->one();

Upvotes: 1

topher
topher

Reputation: 14860

As is it is difficult to follow your logic. To simplify your code and your query, add the first condition as plain sql. Also since the second condition is required, you can swap the positions as follows:

$query
    ->select(['count(id) as numberofcompanies','sum(offered_value) as total_offered_value'])
    ->from('campaign_company')
    ->where(['status' => '3'])
    ->andWhere(['
        campaign_id in (select id from campaign where user_id = :current_user )
        or campaign_company.sp_user_id = :current_user1', 
        [':current_user' => Yii::$app->user->id, ':current_user1' => Yii::$app->user->id]])
    ->one();

Upvotes: 1

Related Questions