Mount Mysql query in Laravel

Asked

Viewed 50 times

0

Gentlemen, I have the following query in mysql

SELECT DATE_FORMAT(created_at, '%Y') as 'year', DATE_FORMAT(created_at, '%m') as 'month', COUNT(id) as 'total' FROM emprestimos GROUP BY DATE_FORMAT(created_at, '%Y%m')

But I’m getting beat up to ride her on the Laravel, some hint on how she’d look?

I’m trying how

DB::table('emprestimos')->select('DATE_FORMAT(created_at, %Y as year)')->select('DATE_FORMAT(created_at, %m as month')->count('id as total')->groupBy('DATE_FORMAT(created_at, %Y%m');

but without success!

Thank you

mysql laravel

  • Try using DB:raw($sqlQuery), here I use brackets to select the ex: ->select(['id as total']) and if you step parameter in select as you, it usually goes in Raw as well ->select([DB:raw('DATE_FORMAT(Xyz) as abc')])

  • tried Emprestimos::select(DB::raw(["DATE_FORMAT(created_at, '%Y') year"]), DB::raw(["DATE_FORMAT(created_at, '%m') Month"]) ->Count('id') ->groupBy([DB::raw(DATE_FORMAT(created_at) %Y%m')]); and didn’t happen @Leonardogetulio

  • Bro, the clasps are in the wrong place. Also you could assemble the select 1 part at a time, then you’ll see where the failure is. Also read the documentation on the Arabic https://laravel.com/docs/5.8/queries

  • Valew @Leonardogetulio

  • Cristiano, if decided post as answer, not direct in the question.

1 answer

0


The resolution was 

$valores = Emprestimos::select([
    DB::raw("DATE_FORMAT(created_at, '%Y') as year"),
    DB::raw("DATE_FORMAT(created_at, '%m') as month"),
    DB::raw("count(id) as total")
])
->groupBy('year')
->groupBy('month')
->get();

  • @Andersoncarloswoss Show, I’ll add!

  • In fact just do YEAR(created_at) and MONTH(created_at)...

Browser other questions tagged mysql laravel

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