基于Laravel实现按JSON内容统计结果排序用户数据

我有一个存储学生课程进度的course_student表，表结构如下：

Schema::create('course_student', function (Blueprint $table) {
    $table->primary(['course_id', 'user_id']);
    $table->char('user_id');
    $table->char('course_id');
    $table->timestamp('lesson_timestamp')->nullable();
    $table->text('course_progress')->nullable();
    $table->foreign('user_id')->references('id')->on('users')->onDelete('cascade');
    $table->foreign('course_id')->references('id')->on('courses')->onDelete('cascade');
    $table->timestamps();
});

其中course_progress字段以JSON格式存储各课时的进度数据，示例如下：

{
  "6e980e75-72a6-4260-bbe6-7a764b4e7ce7" : {
    "tools" : 5,
    "exam" : 15,
    "intro" : 5,
    "dictionary" : 5,
    "class" : 35.555555555555557
  },
  "9827d702-c1a1-4fcc-a134-69385ccb7dde" : {
    "intro" : 5,
    "class" : 13,
    "exam" : 15
  },
  "87fd10e3-8140-448e-bf35-64097eb8be48" : {
    "tools" : 5,
    "exam" : 15,
    "intro" : 5,
    "dictionary" : 5,
    "class" : 40
  },
  "52700dd0-caaa-48e7-86f8-d50389fb915f" : {
    "tools" : 5,
    "exam" : 15,
    "intro" : 5,
    "dictionary" : 5,
    "class" : 40
  },
  "daae12a4-b1ac-4138-bbf3-423c3148fc57" : {
    "tools" : 5,
    "exam" : 15,
    "intro" : 5,
    "dictionary" : 5,
    "class" : 0
  }
}

学生学习课程时会更新lesson_timestamp和对应课时的进度数据。目前我可以通过以下Laravel代码按lesson_timestamp对用户进行排序：

public function getUsers()
{
    return $users = User::query()
      ->select('users.id', 'users.email', 'users.lastSeen', 'users.totalTime', 'users.retention')
      ->orderBy(CourseStudent::select('lesson_timestamp')
                            ->whereColumn('user_id', 'users.id')
                            ->orderByRaw('lesson_timestamp is null')
                            ->oldest()
                            ->take(1))
      ->get();
}

现在想知道：是否可以采用类似的查询方式，基于课程进度中class值大于35的条目数量的最大/最小值对用户进行排序？

当然可以实现这种排序逻辑，核心思路是通过数据库的JSON解析能力统计每个用户符合条件的条目数量，再用子查询关联排序，和你现有的lesson_timestamp排序方式逻辑一致。

具体实现（以MySQL 8.0+为例） #

按class>35条目数量降序排序（数量多的用户在前）

public function getUsersSortedByClassProgress()
{
    return User::query()
        ->select('users.id', 'users.email', 'users.lastSeen', 'users.totalTime', 'users.retention')
        ->orderBy(
            // 子查询统计当前用户的class>35的条目总数
            CourseStudent::selectRaw("COUNT(*)")
                ->whereColumn('user_id', 'users.id')
                ->whereRaw("JSON_VALID(course_progress)") // 过滤无效JSON
                ->crossJoinRaw(
                    "JSON_TABLE(
                        course_progress,
                        '$.*' COLUMNS(
                            class DECIMAL PATH '$.class'
                        )
                    ) AS progress_rows"
                )
                ->whereRaw("progress_rows.class > 35")
                ->groupBy('course_student.user_id'),
            'desc' // 降序排列，改为'asc'则按数量升序
        )
        ->get();
}

关键细节 #

1. JSON解析逻辑：JSON_TABLE把course_progress里的每个课时对象拆成一行数据，方便单独判断每个class值是否大于35。
2. 兼容性处理：如果使用PostgreSQL，可以把JSON_TABLE换成jsonb_each(course_progress::jsonb)再提取class字段；低版本MySQL不支持JSON_TABLE的话，建议升级数据库，或者在业务层提前处理统计（但性能会有所下降）。
3. 空值与无进度用户：没有课程进度或course_progress无效的用户，统计结果为0，排序时会按指定顺序（升序在前，降序在后）排列。
4. 性能优化：给course_student.user_id添加索引，避免大表关联时的全表扫描；如果用户量和课程量很大，建议定期把统计结果缓存到用户表的单独字段里，减少实时JSON解析的开销。

内容的提问来源于stack exchange，提问作者Андрей Измайлов