Where clause in YII2

Question

I have a question how to use the Closure type Active records Query in YII2 with conditional WHERE.

Here is what I want to achieve:

public function getUsers($limit = 10, $type = 1, $company_id = 0) { 
    return User::find()->where( function($query) use ($type, $company_id){       
                $query->where(['type' => $type]); 
                if($company_id != 0) { 
                   $query->andWhere(['company_id' => $company_id]); 
                } 
               })
            ->orderBy([ 'created_at'=> SORT_DESC, ]) 
            ->limit($limit); 
}

Please help if anyone knows about this.

Answer 1

I can not understand what to close here. You can use the condition andFilterWhere()for company_id, but you must set it to null by default, so this condition will be ignored if company_id has not been initialized:

public function getUsers($limit = 10, $type = 1, $company_id = null) { 
    return User::find()
            ->where(['type' => $type])
            ->andFilterWhere(['company_id' => $company_id])
            ->orderBy([ 'created_at'=> SORT_DESC ]) 
            ->limit($limit)
            ->all(); //probably you muiss it
}

http://www.yiiframework.com/doc-2.0/yii-db-querytrait.html#andFilterWhere()-detail

Answer 2

public function getUsers($limit = 10, $type = 1, $company_id = null) 
{
       $query =User::find()
            ->where(['type' => $type])
            ->orderBy([ 'created_at'=> SORT_DESC ]) 
            ->limit($limit)
            ->all(); 

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

      // then filter data
      if($company_id != null)
          $query->andFilterWhere(['company_id' => $company_id]);
}

Answer 3

I had the same problem in Laravel MVC and something like this was ok for me.

Replace

$query->andWhere(['company_id' => $company_id]);

with

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