如何用SQL查询LearnDash课程、课时及章节的修订记录

我有一份数据库备份，想要获取某一课程的所有修订记录，以及该课程下课时的修订记录，还有每个课时下章节的修订记录。

我已经写出了查询课程修订记录的SQL语句：

SELECT * FROM wp_posts WHERE post_type='revision' AND post_parent = 1

但我不知道怎么获取和该课程关联的课时。在wp_posts表中，我识别出以下post_type：

• sfwd-courses 对应课程
• sfwd-lessons 对应课时
• sfwd-topic 对应章节
• revision 对应修订记录

所有条目都是wp_post类型，但只有revision类型的记录有post_parent信息，以sfwd开头的类型post_parent值都是0，找不到WordPress关联课程与课时的方式。

我是LearnDash新手，SQL能力一般，希望得到帮助！

补充说明 #

以下是获取课时的PHP代码，我认为课时的课程ID存储在wp_postmeta表的meta_key和meta_value中，或许能帮助理解结构并写出对应的SQL查询：

/**
 * Makes wp_query to retrieve lessons a course
 *
 * @since 2.1.0
 *
 * @param  int $course_id Course ID.
 * @return array    array of lessons
 */
public function select_a_lesson( $course_id = null ) {
    if ( ! is_admin() ) {
        return array();
    }

    if ( ! empty( $_REQUEST['ld_action'] ) || ! empty( $_GET['post'] ) && is_array( $_GET['post'] ) ) {
        return array();
    }

    $opt = array(
        'post_type'   => 'sfwd-lessons',
        'post_status' => 'any',
        'numberposts' => -1,
        'orderby'     => learndash_get_option( 'sfwd-lessons', 'orderby' ),
        'order'       => learndash_get_option( 'sfwd-lessons', 'order' ),
    );

    if ( empty( $course_id ) ) {
        if ( empty( $_GET['post'] ) ) {
            $course_id = learndash_get_course_id();
        } else {
            $course_id = learndash_get_course_id( $_GET['post'] );
        }
    }

    if ( ! empty( $course_id ) ) {
        $opt['meta_key']   = 'course_id';
        $opt['meta_value'] = $course_id;
    }

    $posts = get_posts( $opt );
    if ( learndash_use_select2_lib() ) {
        $post_array = array(
            '-1' => sprintf(
                // translators: placeholder: Lesson.
                esc_html_x( 'Search or select a %s', 'placeholder: Lesson', 'learndash' ),
                LearnDash_Custom_Label::get_label( 'lesson' )
            ),
        );
    } else {
        $post_array = array(
            '0' => sprintf(
                // translators: placeholder: Lesson.
                esc_html_x( 'Select a %s', 'placeholder: Lesson', 'learndash' ),
                LearnDash_Custom_Label::get_label( 'lesson' )
            ),
        );
    }

    if ( ! empty( $posts ) ) {
        foreach ( $posts as $p ) {
            $post_array[ $p->ID ] = $p->post_title;
        }
    }

    return $post_array;
}

解决方案 #

根据你提供的PHP代码，确实LearnDash是通过wp_postmeta表来关联课程和课时的——课时的course_id元数据对应所属课程的ID。同理，章节（sfwd-topic）和课时的关联也是通过wp_postmeta表的lesson_id元字段来实现的。

下面是可以一次性获取目标课程、下属课时、下属章节所有修订记录的SQL查询（把1替换成你的目标课程ID）：

1. 合并查询所有层级的修订记录 #

-- 课程的修订记录
SELECT '课程' AS 层级, p.post_title AS 原内容标题, r.*
FROM wp_posts r
JOIN wp_posts p ON r.post_parent = p.ID
WHERE r.post_type = 'revision' AND r.post_parent = 1

UNION ALL

-- 课程下属课时的修订记录
SELECT '课时' AS 层级, p.post_title AS 原内容标题, r.*
FROM wp_posts r
JOIN wp_posts p ON r.post_parent = p.ID
JOIN wp_postmeta pm ON p.ID = pm.post_id
WHERE r.post_type = 'revision' 
  AND p.post_type = 'sfwd-lessons'
  AND pm.meta_key = 'course_id' 
  AND pm.meta_value = 1

UNION ALL

-- 课程下属章节的修订记录
SELECT '章节' AS 层级, p.post_title AS 原内容标题, r.*
FROM wp_posts r
JOIN wp_posts p ON r.post_parent = p.ID
JOIN wp_postmeta pm_lesson ON p.ID = pm_lesson.post_id
JOIN wp_postmeta pm_course ON pm_lesson.meta_value = pm_course.post_id
WHERE r.post_type = 'revision' 
  AND p.post_type = 'sfwd-topic'
  AND pm_lesson.meta_key = 'lesson_id'
  AND pm_course.meta_key = 'course_id' 
  AND pm_course.meta_value = 1;

2. 分步查询（按需使用） #

查询课程下属的所有课时ID

SELECT post_id AS lesson_id
FROM wp_postmeta
WHERE meta_key = 'course_id' AND meta_value = 1;

查询指定课时下属的所有章节ID

SELECT post_id AS topic_id
FROM wp_postmeta
WHERE meta_key = 'lesson_id' AND meta_value = [课时ID];

查询课时的修订记录

SELECT * FROM wp_posts WHERE post_type='revision' AND post_parent IN (
    SELECT post_id FROM wp_postmeta WHERE meta_key='course_id' AND meta_value=1
);

查询章节的修订记录

SELECT * FROM wp_posts WHERE post_type='revision' AND post_parent IN (
    SELECT post_id FROM wp_postmeta WHERE meta_key='lesson_id' AND meta_value IN (
        SELECT post_id FROM wp_postmeta WHERE meta_key='course_id' AND meta_value=1
    )
);

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