CODEWITHSUNDEEP
yii2
M.O. Idowu
M.O. Idowu

Reputation: 217

Yii2 - QueryAll is having issue

I am using Query Builder with multiple where clause. When I use this query,

            $query1 = new \yii\db\Query();
        $query1->select('*')
            ->from('assessment_score ca')
            ->where(['AND','ca.is_status' => 0, 'ca.assessment_type' => 'CONTINUOUS ASSESSMENT', 'ca.ca_type' => 'CONTINUOUS ASSESSMENT'])
            ->andFilterWhere(['ca.state_office_id' => $model->report_state_office_id])
            ->andFilterWhere(['ca.study_centre_id' => $model->report_study_centre_id])
            ->andFilterWhere(['ca.programme_id' => $model->report_programme_id])
            ->andFilterWhere(['ca.department_id' => $model->report_department_id])
            ->andFilterWhere(['ca.academic_level_id' => $model->report_academic_level_id])
            ->andFilterWhere(['ca.academic_year_id' => $model->report_academic_year_id])
            ->andFilterWhere(['ca.academic_semester_id' => $model->report_academic_semester_id])
            ->andFilterWhere(['ca.course_id' => $model->report_course_id]);

        $command=$query1->createCommand();
        $ca_data=$command->queryAll();

I got this error

Query Error

Then, when I changed the code to this, no response:

            $selected_list = $_POST['ca'];

        $query1 = new \yii\db\Query();
        $query1->select('*')
            ->from('assessment_score ca')
            ->where(['ca.is_status' => 0])
            ->andWhere(['ca.assessment_type' => 'CONTINUOUS ASSESSMENT'])
            ->andWhere(['ca.ca_type' => 'CONTINUOUS ASSESSMENT'])
            ->andFilterWhere(['ca.state_office_id' => $model->report_state_office_id])
            ->andFilterWhere(['ca.study_centre_id' => $model->report_study_centre_id])
            ->andFilterWhere(['ca.programme_id' => $model->report_programme_id])
            ->andFilterWhere(['ca.department_id' => $model->report_department_id])
            ->andFilterWhere(['ca.academic_level_id' => $model->report_academic_level_id])
            ->andFilterWhere(['ca.academic_year_id' => $model->report_academic_year_id])
            ->andFilterWhere(['ca.academic_semester_id' => $model->report_academic_semester_id])
            ->andFilterWhere(['ca.course_id' => $model->report_course_id]);

        $command=$query1->createCommand();
        $ca_data=$command->queryAll();

How do I re-write the code appropriately to solve the issue of multiple where clause?

Upvotes: 0

Views: 428

Answers (3)

Muhammad Omer Aslam
Muhammad Omer Aslam

Reputation: 23738

You might need to change the query format for the where() statement as you need to provide every condition (name=>value pair) as a separate array rather than just name=>value pairs, you currently have

->where(['AND', 'ca.is_status' => 0, 'ca.assessment_type' => 'CONTINUOUS ASSESSMENT', 'ca.ca_type' => 'CONTINUOUS ASSESSMENT'])

which will create the query like below if no other parameter is provided for andFilterWhere() statements.

SELECT * FROM `assessment_score` `ca` 
WHERE (0) 
AND (CONTINUOUS ASSESSMENT) AND (CONTINUOUS ASSESSMENT)

which is incorrect and throwing the error, you can notice that in your Exception image, so change it to the one below

->where(['AND',
       ['ca.is_status' => 0],
       ['ca.assessment_type' => 'CONTINUOUS ASSESSMENT'],
       ['ca.ca_type' => 'CONTINUOUS ASSESSMENT']
])

which will output the query like 

SELECT * FROM `assessment_score` `ca` 
WHERE (`ca`.`is_status`=0) 
    AND (`ca`.`assessment_type`='CONTINUOUS ASSESSMENT') 
    AND (`ca`.`ca_type`='CONTINUOUS ASSESSMENT')

Your complete query should look like this 

$query1 = new \yii\db\Query();
$query1->select('*')
        ->from('assessment_score ca')
        ->where(['AND',
            ['ca.is_status' => 0],
            ['ca.assessment_type' => 'CONTINUOUS ASSESSMENT'],
            ['ca.ca_type' => 'CONTINUOUS ASSESSMENT']
        ])
        ->andFilterWhere(['ca.state_office_id' => $model->report_state_office_id])
        ->andFilterWhere(['ca.study_centre_id' => $model->report_study_centre_id])
        ->andFilterWhere(['ca.programme_id' => $model->report_programme_id])
        ->andFilterWhere(['ca.department_id' => $model->report_department_id])
        ->andFilterWhere(['ca.academic_level_id' => $model->report_academic_level_id])
        ->andFilterWhere(['ca.academic_year_id' => $model->report_academic_year_id])
        ->andFilterWhere(['ca.academic_semester_id' => $model->report_academic_semester_id])
        ->andFilterWhere(['ca.course_id' => $model->report_course_id]);

$command = $query1->createCommand();
$ca_data = $command->queryAll();

Upvotes: 1

rob006
rob006

Reputation: 22174

All you need is to remove AND from array passed to where():

->where([
    'ca.is_status' => 0, 
    'ca.assessment_type' => 'CONTINUOUS ASSESSMENT', 
    'ca.ca_type' => 'CONTINUOUS ASSESSMENT'
])

If you pass associative array, it will be treated as pairs of column-value for conditions for WHERE in query. If you pass AND as first element, it is no longer a associative array, and query builder will ignore keys and only combine values as complete condition.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

based on yii2 guide for Operator Format

Operator format allows you to specify arbitrary conditions in a programmatic way. It takes the following format:

[operator, operand1, operand2, ...] where the operands can each be specified in string format, hash format or operator format recursively, while the operator can be one of the following:

and: the operands should be concatenated together using AND. For example, ['and', 'id=1', 'id=2']

so in your case should be 

->where(['AND', 'ca.is_status = 0',
   "ca.assessment_type = 'CONTINUOUS ASSESSMENT'",
         "ca.ca_type  = 'CONTINUOUS ASSESSMENT'"])

https://www.yiiframework.com/doc/guide/2.0/en/db-query-builder#operator-format

Upvotes: 0

Related Questions