CODEWITHSUNDEEP
phpmysqlyii2
Sajid Mehmood
Sajid Mehmood

Reputation: 523

How to write a mysql query in yii2 search model

I want to write a mysql query in Yii2 search model but when performing searching criteria it gives the errors on joins. This is my search model.

class StudentRegistrationSearch extends StudentRegistration {

/**
 * @inheritdoc
 */

public function rules() {
    return [
        [['id', 'student_id', 'recordstatus', 'addedbyuserid'], 'integer'],
        [[ 'registration_date', 'dateadded', 'let'], 'safe'],
    ];
}

/**
 * @inheritdoc
 */
public function scenarios() {
    // bypass scenarios() implementation in the parent class
    return Model::scenarios();
}

/**
 * Creates data provider instance with search query applied
 *
 * @param array $params
 *
 * @return ActiveDataProvider
 */
public function search($params) {
    $query = StudentRegistration::find()->where(['recordstatus' => 1]);
    $query = <<<EOD
       SELECT 
        students.student_name,
        students.`id`,
        students.`reg_no`,
        reg.`registration_date`,
        exam.`exam_year`,
        exam.`exam_title`
            FROM students
            LEFT JOIN student_registration reg ON (reg.`student_id` = students.`id`)
            LEFT JOIN student_reg_detail detail ON(detail.`student_register_id` = reg.`id`)
            LEFT JOIN def_exams exam ON(exam.`id` = detail.reg_exam_id)
            WHERE students.`recordstatus` = 1 AND reg.`recordstatus` = 1 AND detail.`recordstatus` = 1
            ORDER BY exam.exam_year DESC, exam.exam_title,reg.registration_date,students.student_name; EOD;
            $query = Yii::$app->db->createCommand($query);
            $query = $query->queryAll();

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

    $this->load($params);

    if (!$this->validate()) {
        // uncomment the following line if you do not want to any records when validation fails
        // $query->where('0=1');
        return $dataProvider;
    }
    $query->andFilterWhere([
        'id' => $this->id,
        'student_id' => $this->student_id,
        'registration_date' => $this->registration_date,
        'recordstatus' => $this->recordstatus,
        'dateadded' => $this->dateadded,
        'addedbyuserid' => $this->addedbyuserid,
        'let' => $this->let,
    ]);
    $query->orderBy('student_id');
    return $dataProvider;
}}

I want to show the data from multiple table in the single grid and then perform filter operation but simple query is not working. Can you please help me someone. Thanks in advance.

Upvotes: 0

Views: 1114

Answers (1)

Vikas Chaudhary
Vikas Chaudhary

Reputation: 728

Try something like following

$query = (new yii\db\Query())
        ->from(['s' => 'students'])
        ->select(['s.student_name', 's.id', 's.reg_no', 'reg.registration_date', 'exam.exam_year', 'exam.exam_title'])
        ->leftJoin(['reg' => 'student_registration', 'stu.student_id = s.id'])
        ->leftJoin(['detail' => 'student_reg_detail', 'stu.student_id = s.id'])
        ->leftJoin(['exam' => 'def_exams ', 'exam.id = detail.reg_exam_id'])
        ->where(['s.recordstatus' => 1, 'reg.recordstatus' => 1, 'detail.recordstatus' => 1])
        ->orderBy('exam.exam_year DESC, exam.exam_title,reg.registration_date,students.student_name')
;

OR

$query = Students::find()
        ->from(['s' => Students::tablename()])
        ->select(['s.student_name', 's.id', 's.reg_no', 'reg.registration_date', 'exam.exam_year', 'exam.exam_title'])
        ->leftJoin(['reg' => 'student_registration', 'stu.student_id = s.id'])
        ->leftJoin(['detail' => 'student_reg_detail', 'stu.student_id = s.id'])
        ->leftJoin(['exam' => 'def_exams ', 'exam.id = detail.reg_exam_id'])
        ->where(['s.recordstatus' => 1, 'reg.recordstatus' => 1, 'detail.recordstatus' => 1])
        ->orderBy('exam.exam_year DESC, exam.exam_title,reg.registration_date,students.student_name')
;


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

Upvotes: 1

Related Questions