How to convert the SQL query into Laravel format

Question

I want to convert the below SQL written query into laravel format.please help

SELECT id, first_name, last_name,email,tazkira_no, COUNT(id) As NoRegister FROM
applicants WHERE is_valid=0 AND is_new =1 GROUP BY
tazkira_no HAVING 
COUNT(*) > 1

what I tried is :

$object = DB::table('applicants')
        ->select('first_name','last_name','email','tazkira_no')
        ->groupBy('tazkira_no')
        ->havingRaw('COUNT(*) > 1')
        ->get();
     return $object;

but not working and gives me the error :

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'scors.applicants.first_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select first_name, last_name, email, tazkira_no from applicants group by tazkira_no having COUNT(*) > 1)

you are using two different db versionn for sql and lavravel ??? — ScaisEdge, Feb 18 '19 at 10:50

Mihir Bhende · Answer 1 · 2019-02-18T10:53:49.803

1

You can not have non-aggregated columns(columns which are not in group by or not using aggregates like sum, count, group_concat etc) in the select when query has a group by clause

You need to disable only_full_group_by mode in mysql to make this query work in general. Strangely, if it works in SQL written directly in Mysql client, then it should also work in Laravel considering both are using same MySQL server and user.

Check more info here

Also, in your database configuration, disable strict queries by setting 'strict' => false, in config/database.php

edited Feb 18 '19 at 10:53

answered Feb 18 '19 at 10:51

Mihir Bhende

8,677
1
30
37

Did you disable strict more in database configurations? – Mihir Bhende Feb 18 '19 at 10:54
I see no **ONLY_FULL_GROUP_BY ** in sql mode of my db configuration – Zaker Husien Yusofi Feb 18 '19 at 10:57
I was talking about `'strict' => false,` confiuration `in config/database.php`. The `ONLY_FULL_GROUP_BY` option is on mysql side – Mihir Bhende Feb 18 '19 at 10:58

score 0 · Accepted Answer · answered Feb 18 '19 at 10:52

0

Set 'strict' => false, in config/database.php -> mysql it will allow you to run this query.

answered Feb 18 '19 at 10:52

Afraz Ahmad

5,193
28
38

How to convert the SQL query into Laravel format

2 Answers2