CODEWITHSUNDEEP
mysqlgridviewactiverecordyii2dataprovider
Muhammad Umair
Muhammad Umair

Reputation: 113

yii2 dataprovider with custom query

I have following code of index action

$searchModel = new PatientTestSearch();
 $dataProvider = $searchModel->search(Yii::$app->request->queryParams);

   return $this->render('index', [
            'dataProvider' => $dataProvider,
            'searchModel' => $searchModel,
           ]);

Search function in patientTestSearch is

    $query = PatientTest::find();

    $dataProvider = new ActiveDataProvider([
            'query' => $query,
            'pagination' => ['pageSize' => $pagination],
            'sort' => ['defaultOrder' => ['created_at' => SORT_DESC]]
        ]);
    $query->joinWith('patient');
    $query->joinWith('testGroup');

how to use group concate and group by in above query? i have tried this in controller's action

$dataProvider->query->groupBy(['patient_id']);

but this gives following error

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'wizlaboratory.patient_test.patient_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

If i use sqldataprovider then i cant have filtering option.

Upvotes: 2

Views: 6062

Answers (2)

Muhammad Umair
Muhammad Umair

Reputation: 113

I just change this query 

$query = PatientTest::find();

to this

$query = new Query();
        $query->select('patient.id as id,patient_test.receipt_number,patient.name as patient_id, GROUP_CONCAT(test_group.name) as test_group_id,
                       patient.age as patient_test_age,patient.gender as patient_test_gender,patient.mobile_no as patient_test_mobile,patient_test.test_date,patient_test.is_sample_received,
                       patient_test.datetime')
            ->from('patient_test');

        $query->join = [
            ['LEFT JOIN', 'patient', 'patient_test.patient_id = patient.id'],
            ['LEFT JOIN', 'test_group', 'patient_test.test_group_id = test_group.id']];
        if ($pid != null && $pid != '') {
            $query->where('patient_test.patient_id = ' . $pid);
        }
        $query->groupBy(['patient_test.patient_id', 'patient_test.receipt_number', 'patient_test.test_date', 'patient_test.datetime', 'patient_test.is_sample_received']);
        $query->orderBy('patient.id DESC');

and comment below join query 

//$query->joinWith('patient');
//$query->joinWith('testGroup');

Now everything is working fine

Upvotes: 1

Yupik
Yupik

Reputation: 5032

ONLY_FULL_GROUP_BY mode reject queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on (uniquely determined by) GROUP BY columns.

  • To solve this on database level, you can turn off ONLY_FULL_GROUP_BY mode by running this query in your mysql:

    SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

  • To solve this problem in your $query add all necessary fields to groupBy() method. ($query->groupBy(['attr1', 'attr2']).

Upvotes: 2

Related Questions