How to convert SELECT statement to Eloquent?

Question

This works in existing website, trying to convert to Laravel 5.8 code:

SELECT
  DATE_FORMAT(created_at, '%M %Y') AS 'article',
  DATE_FORMAT(created_at, '%m')AS 'm',
  DATE_FORMAT(created_at,'%Y') AS 'y',
  COUNT(id) AS 'total'
FROM posts
GROUP BY DATE_FORMAT(created_at, '%Y%M')
ORDER BY m DESC

I tried:

$archives = DB::select("SELECT DATE_FORMAT(created_at, '%M %Y') AS 'article',DATE_FORMAT(created_at, '%m')AS 'm', DATE_FORMAT(created_at,'%Y') AS 'y', COUNT(id) AS 'total' FROM posts GROUP BY DATE_FORMAT(created_at, '%Y%M') ORDER BY m DESC");`

I got:

Error: SQLSTATE[42000]: Syntax error or access violation: 1055 'amohdb.posts.created_at' isn't in GROUP BY (SQL: SELECT DATE_FORMAT(created_at, '%M %Y') AS 'article',DATE_FORMAT(created_at, '%m')AS 'm', DATE_FORMAT(created_at,'%Y') AS 'y', COUNT(id) AS 'total' FROM posts GROUP BY DATE_FORMAT(created_at, '%Y%M') ORDER BY m DESC)

Carol Lillefield Carol Lillefield · Accepted Answer · 2019-06-02T00:41:25

Answer turned out to be much simpler than I thought

$archives =  Post::select(DB::raw("DATE_FORMAT(created_at, '%M %Y') as article"),
DB::raw("COUNT(id) as total"))
->groupBy('article')
->get();

How to convert SELECT statement to Eloquent?

1 Answers