Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
I need to use ->groupByRaw("DATE_FORMAT(date, '%Y-%m-%d')") on one of my models and for that I need to set strict to false in config/database.php..
Reason: my date value is declared as datetime in my db. However I need to group it by DATE only.
Now reading a few threads here and there some people say it can open to vulnerabilities, some say it's not a big deal.
I really need a concrete answer, does it really expose my website to vulnerabilities and strict should be kept on at all time?
You can disable strict for just one query. I am looking for link I saved.
https://laracasts.com/discuss/channels/guides/turn-off-only-full-group-by-for-just-one-query-guide
That way just leave setting as is, or adjust the query.
Edit:
But read about it here https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html
@jlrdw so before the query
config()->set('database.connections.mysql.strict', false);
Do I need to re-enable it after the query or it's done automatically?
EDIT I have just tried doing that, but it appears it doesn't work.
Replace with your db name.
my db is mysql
@jlrdw when I dd(config('database.connections.mysql.strict')); the value is indeed set to false but still doesn't work. Only works when I set it to false manually..
@akc4 i remember that setting also affect ONLY_FULL_GROUP_BY
can you try https://stackoverflow.com/questions/366603/group-by-date-only-on-a-datetime-column
depends on what you want to do
SELECT COUNT(id), DATE(created_at) AS u
FROM users
GROUP BY u
I absolutely don't want to do any full RAW query anywhere in my project. Every single query must be eloquent.
As I said what I want is to groupBy date with a different format and you are right ONLY_FULL_GROUP_BY is blocking me from doing it.
And after reading more on the strict mode, it is highly recommended to keep strict mode on at all time.
@akc4 YES as i said, depends on what you want to do
with strict mode ON, write a raw sql and convert it to eloquent
@newbie360 oh I see I'll try that
this is my current query
$data = Matchup::currentStatus('closed')->groupByRaw("DATE_FORMAT(date, '%Y-%m-%d')")->get();
@akc4 try setting config then to false. Try the query. If it still doesn't work it's something with the query.
Change back afterwards.
Remember to run php artisan config:clear
the query works when I manually set it to false in config/database
however, it doesn't work when I set it to true in the config and then try it like you described.
config(['database.connections.mysql.strict' => false]);
$data = Matchup::currentStatus('closed')->groupByRaw("DATE_FORMAT(date, '%Y-%m-%d')")->get();
even when running php artisan config:clear
EDIT: found the solution to this problem here: https://stackoverflow.com/a/53317109/13826039
config(['database.connections.mysql.strict' => false]);
DB::reconnect();
$data = Matchup::currentStatus('closed')->groupByRaw("DATE_FORMAT(date, '%Y-%m-%d')")->get();
config(['database.connections.mysql.strict' => true]);
DB::reconnect();
this works now but now I am wondering will this affect performance.
Did you pull in db?
use Illuminate\Support\Facades\DB;
And
this
config()->set('database.connections.mysql.strict', false);
not
config(['database.connections.mysql.strict' => false]);
I just retested in laravel 8, it worked.
You shouldn't need the DB::reconnect(); At least I never needed it.
even with
config()->set('database.connections.mysql.strict', false);
it absolutely doesn't work without DB::reconnect(); in my case.
@akc4 it might have something to do with eloquent. I used query builder. But glad you got a solution.
@jlrdw thank you for the help however I will go ahead and group them on the client site with javascript. I don't know if calling the DB reconnect function will affect my performance down the road this is a complete headache.
Please or to participate in this conversation.