yii2 dataprovider with custom query

Question

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.

score 2 · Answer 1 · answered Aug 07 '17 at 06:31

2

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']).

answered Aug 07 '17 at 06:31

Yupik

4,932
1
12
26

I have figured this out using Sqldataprovider. all data is rendring perfectly accept filtering. when i filter something , error occured that "SQLSTATE[HY093]: Invalid parameter number: no parameters were bound". No paramter is bounding in "$query->filterWhere(['Like','patient_test.receipt_number', $this->receipt_number]);" – Muhammad Umair Aug 09 '17 at 05:46
But all your problems could be solved by running one MySQL query. – Yupik Aug 09 '17 at 05:48
i have set those Set global sqlmode command.. but group by problem was not solved – Muhammad Umair Aug 09 '17 at 06:09
yes... and now everything got ok .i just change nack sqldataprovider to activedataprovider and little change in query. filtration is working too now. thanks mate – Muhammad Umair Aug 09 '17 at 06:26

score 1 · Answer 2 · answered Aug 09 '17 at 06:32

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

yii2 dataprovider with custom query

2 Answers2