laravel-5 - 如何将 SELECT 语句转换为 Eloquent？

Question

这适用于现有网站，尝试转换为 Laravel 5.8 代码：

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

我试过了：

$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");`

我有：

错误：SQLSTATE[42000]：语法错误或访问冲突：1055 'amohdb.posts.created_at' 不在 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)

标签： laravel-5eloquent

Answer 1

答案比我想象的要简单得多

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