Average grade calculation broken on WP 6.4+ due to comment_total_sum_meta_value_filter

[donnapep]

Description

The comment_total_sum_meta_value_filter used by Sensei_Utils to compute average grades on the Reports → Courses → Lessons overview is broken on WordPress 6.4+.

Root cause

Sensei_Utils::comment_total_sum_meta_value_filter hooks into comments_clauses and replaces the SQL fields clause with COUNT(*) AS total, SUM(commentmeta.meta_value) AS meta_sum. This worked in older WP versions where WP_Comment_Query used $wpdb->get_results().

In WP 6.4, WP_Comment_Query::get_comments() was changed to call $wpdb->get_col() (source), which only returns the first column of the result set. The meta_sum column is silently discarded, and the COUNT(*) value is interpreted as a comment ID and fetched from the comment cache.

Current behavior

• Trunk shows 0% — the returned WP_Comment has no total or meta_sum properties, so the calculation falls through to 0 / 1 = 0.
• Comments-based service (PR Add reports listing service for HPPS integration #7932) shows N/A — with 73+ matching comments, sensei_check_for_activity returns an array (not an object), so the !is_object() check returns null.

Expected behavior

For lesson 113 with 73 graded students and a grade sum of 4124.36, the correct average is 56.50%.

Suggested fix

Replace the sensei_check_for_activity + comment_total_sum_meta_value_filter approach with a direct $wpdb query in the comments-based path:

$avg = $wpdb->get_var( $wpdb->prepare(
    "SELECT AVG(cm.meta_value)
     FROM {$wpdb->comments} c
     INNER JOIN {$wpdb->commentmeta} cm
       ON cm.comment_id = c.comment_ID AND cm.meta_key = 'grade'
     WHERE c.comment_post_ID = %d
       AND c.comment_type = 'sensei_lesson_status'
       AND c.comment_approved IN ('graded', 'passed', 'failed')",
    $post_id
));

Additional context

• The tables-based path (HPPS) is not affected — it queries AVG(qs.final_grade) directly from sensei_lms_quiz_submissions.
• There is also a data parity issue: comments stores grade = 0 for auto-passed students, while tables has final_grade = NULL for those same students, producing different averages (56.50 vs 72.36 for lesson 113). This is a separate migration gap.
• Discovered during PR Add reports listing service for HPPS integration #7932 review.

🤖 Generated with Claude Code

[claude]

Claude encountered an error —— View job

---

Plan

• Analyze issue and gather context
• Find and read relevant source files
• Implement the fix (replace comment_total_sum_meta_value_filter with direct $wpdb->get_var() query)
• Write/update tests (3 new tests covering graded/passed/failed/no-students cases)
• Run lint (make lint)
• Run tests (make test-php)
• Add changelog entry (make changelog)
• Commit and push
• Open PR

Changes so far

includes/class-sensei-analysis-course-list-table.php: Replaced the broken add_filter/comment_total_sum_meta_value_filter/remove_filter pattern with a direct $wpdb->get_var() query using AVG(). The old approach stopped working on WP 6.4+ because WP_Comment_Query::get_comments() switched to $wpdb->get_col(), which silently drops all columns except the first.

tests/unit-tests/test-class-sensei-analysis-course-list-table.php: Added 3 new tests for the average grade calculation.

[donnapep]

@claude Please retry.

[donnapep]

@claude Try again.

[donnapep]

@claude Try to fix this issue.

[donnapep]

@claude Please fix this!