Doubt when performing SQL with Eloquent of Laravel

Asked

Viewed 290 times

3

I’m with a bug I don’t know why.

I have in my controller the following Eloquent:

$balances = BankUser::select(DB::raw('SUM(balance) AS total, 
          created_at, DATE(created_at) AS total_at'))
    ->whereYear('created_at', $year)->whereMonth('created_at', $month)
    >groupBy('total_at')->get();

That is generating me the following mistake:

SQLSTATE[42000]: Syntax error or access violation: 1055 'db_develop.account_bank_user.created_at' isn't in GROUP BY (SQL: select SUM(balance) AS total, created_at, DATE(created_at) AS total_at fromut_banks_userwhere year(created_at) = 2018 and month(created_at) = 12 group by total_at)

But if I copy the SQL that is being generated:

select SUM(balance) AS total, created_at, DATE(created_at) AS total_at fromaccount_bank_userwhere year(created_at) = 2018 and month(created_at) = 12 group bytotal_at``

And run on my Phpmyadmin it query and bring back the records, what’s missing in the code?

php mysql laravel laravel-eloquent
  • 1

    Which version of your Laravel and which database? Can you send me how your Laravel is configured config/database.php

  • 1

    You have to add the created_at in group by

  • 1

    Take a look at the answers to that question

  • 1

    This code is inside a for?

2 answers

3

According to the answers of this question that I found very interesting, I took one of the simplest answers

$balances = 
  BankUser::select(DB::raw('SUM(balance) AS total,created_at, DATE(created_at) AS total_at'))
                    ->whereYear('created_at', $year)->whereMonth('created_at', $month)
                    ->groupBy(DB::raw('Date(created_at)')->get();

Note the groupBy inside uses the DB::raw()

2

I’m very pissed but happy to have tidied up hahaha, thanks for the tips, but I solved it in a way so stupid that it still hasn’t come to light.

$balances = BankUser::select(DB::raw('SUM(balance) AS total, DATE(created_at) AS total_at'))
    ->whereYear('created_at', $year)->whereMonth('created_at', $month)
    ->groupBy('total_at')->get();

That is, I just removed from select the first created_at

Browser other questions tagged php mysql laravel laravel-eloquent

You are not signed in. Login or sign up in order to post.