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
– ScaisEdge, Commented Feb 18, 2019 at 10:50

Mihir Bhende · Accepted Answer · 2019-02-18 10:53:49Z

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, 2019 at 10:53

answered Feb 18, 2019 at 10:51

Mihir Bhende

9,0952 gold badges33 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mihir Bhende Over a year ago

Did you disable strict more in database configurations?

Zaker Husien Yusofi Over a year ago

I see no **ONLY_FULL_GROUP_BY ** in sql mode of my db configuration

Mihir Bhende Over a year ago

I was talking about 'strict' => false, confiuration in config/database.php. The ONLY_FULL_GROUP_BY option is on mysql side

Afraz Ahmad · Accepted Answer · 2019-02-18 10:52:17Z

0

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

answered Feb 18, 2019 at 10:52

Afraz Ahmad

5,41633 silver badges40 bronze badges

Collectives™ on Stack Overflow

How to convert the SQL query into Laravel format

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related