如何用Laravel Eloquent Model实现指定列值统计SQL查询？

需求原生SQL #

SELECT
  <column_name>,
  COUNT(<column_name>) AS `value_occurrence` 

FROM
  <my_table>

GROUP BY 
  <column_name>

ORDER BY 
  `value_occurrence` DESC

LIMIT 1;

你的错误代码 #

TestModel::select('column_name', 'COUNT(column_name) AS occurrences')
           ->groupBy('column_name')
           ->orderBy('occurences')
           ->limit(10)
           ->get();

修正后的代码（多种写法） #

写法一：修正原代码的问题

use Illuminate\Support\Facades\DB;

TestModel::select('column_name', DB::raw('COUNT(column_name) AS occurrences'))
    ->groupBy('column_name')
    ->orderBy('occurrences', 'desc')
    ->limit(1)
    ->first();

写法二：使用selectRaw简化

TestModel::selectRaw('column_name, COUNT(column_name) AS occurrences')
    ->groupBy('column_name')
    ->orderByDesc('occurrences')
    ->limit(1)
    ->first();

写法三：拆分select语句

TestModel::groupBy('column_name')
    ->select('column_name')
    ->selectRaw('COUNT(column_name) AS occurrences')
    ->orderByDesc('occurrences')
    ->first();

关键修正点 #

• 聚合函数处理：直接在select里写COUNT(column_name)会被Laravel解析为字段名，需要用DB::raw()或selectRaw()包裹，确保SQL正确执行
• 拼写错误：orderBy里的occurences少了一个字母r，修正为occurrences
• 排序方向：原生SQL要求降序排序，需添加desc参数，或用orderByDesc()方法
• 结果数量：原生SQL限制返回1条，将limit(10)改为limit(1)
• 结果获取：用first()替代get()，直接返回单个模型实例，而非包含单个模型的集合

内容的提问来源于stack exchange，提问作者Aiden Pearce