how to convert the sql to QueryBuilder in Laravel

Question

$old_records = DB::connection("mysql_old_s")->table('leads')
        ->select(DB::raw('count(*) as  source_count, source'))
        ->groupBy('source')
        ->get();
dd($old_records); // works right

This query works right. I want to add one more column like id or created_at to select

I couldn't do it as following:

$old_records = DB::connection("mysql_old_s")->table('leads')
        ->select('id',DB::raw('count(*) as source_count, source'))
        ->groupBy('source')
        ->get();
dd($old_records); // it gives an error

It gives me an error that is:

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP 
BY clause and contains nonaggregated column 'crmclini_crmv2.leads.id' which is not functionally 
dependent on columns in GROUP BY
clause; this is incompatible with sql_mode=only_full_group_by (SQL: select `id`, count(*) as 
source_count, source , id from `leads` group by `source`)

OMR · Accepted Answer · 2020-11-11 14:36:35Z

1

you should include the columns you select within your group by clause to group by them:

$old_records = DB::connection("mysql_old_s")->table('leads')
        ->select('id',DB::raw('count(*) as source_count, source'))
        ->groupBy(['id','source'])
        ->get();

the second option (witch I do not recommend), you can disable only_full_group_by option in config/database.php:

'connections' => [ ...

'mysql' => [
...
    'strict' => false,
    ...

],
]

unless you have special condition, make the query group by the columns you select

answered Nov 11, 2020 at 14:36

OMR

12.3k5 gold badges26 silver badges39 bronze badges

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

Collectives™ on Stack Overflow

how to convert the sql to QueryBuilder in Laravel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related