Sensei: Show Lessons for a Learner by completion status. Also show quiz grade

sensei_show_learner_lessons_by_status_and_course.sql

-- Sensei: Show Lessons for a Learner by completion status. Also show quiz grade

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

use <your-database>;

SET @course_id = <your-course-id>;
SET @user_id = <your-user-id>;

SELECT c.comment_ID, c.user_id, c.comment_post_ID, cm.*
FROM wp_comments AS c
JOIN wp_commentmeta AS cm ON cm.comment_ID = c.comment_ID
WHERE
  c.comment_type = 'sensei_course_status' AND
  c.comment_approved = 'complete' AND
  c.comment_post_ID = @course_id
LIMIT 10;

SELECT l.ID, l.post_title, lm.meta_value FROM wp_posts AS l
JOIN wp_postmeta AS lm on l.ID = lm.post_id
WHERE l.post_type = 'lesson' AND
  lm.meta_key = '_lesson_course' AND
  lm.meta_value = @course_id;

-- Show lessons of course for user
SELECT
  c.comment_ID,
  c.comment_approved,
  GROUP_CONCAT(cm.meta_key) AS meta_keys,
  GROUP_CONCAT(cm.meta_value) AS meta_values
FROM wp_comments AS c
JOIN wp_commentmeta AS cm ON cm.comment_id = c.comment_ID
WHERE
  c.user_id = @user_id AND
  c.comment_post_ID IN (
	SELECT l.ID FROM wp_posts AS l
        JOIN wp_postmeta AS lm on l.ID = lm.post_id
        WHERE
	  l.post_type = 'lesson' AND
          lm.meta_key = '_lesson_course' AND
          lm.meta_value = @course_id)
GROUP BY cm.comment_id;