Wordpress Question posted in
The official Wordpress documentation can be found here.

WordPress – How to query revisions of learndash courses, lessons and chapters in SQL

I have a database backup from where I would like to get all revision for a course, then for its lessons and for each lessons the chapters.

The revision of a course was easy enough: SELECT * FROM wp_posts WHERE post_type='revision' AND post_parent = 1

But I’m a bit lost on even how to get the lessons related to this course.
In the wp_posts table, I managed to identify the post_type:

  • sfwd-courses for a course
  • sfwd-lessons for a lesson
  • sfwd-topic for a chapter
  • revision for a revision

But I can’t figure out how WordPress links a lesson to a course as every entry is just a wp_post.
Only revision type have the post_parent information. For the sfwd- the parent_post value is 0.

I’m new to LearnDash and definitely not a pro with SQL query so any help will be most welcome!

EDIT : here is the PHP code to retrieve a lesson, I thought it might help understand the structure and how to do this in SQL. If I understand correctly, the course ID is stored in the meta_key and meta_value of a lesson?
The table will be wp_postmeta.

/**
     * 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;
    }
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    If anybody wants to do the same one day, I managed to understand how a lesson or a chapter is linked to a course. It was indeed in the wp_postmeta table, using the post_id, meta_key and meta_column columns.

    First step is to get the ID of the current version of the course's lessons:

    SELECT post_id FROM wp_posts JOIN wp_postmeta ON wp_posts.id = wp_postmeta.post_id WHERE wp_posts.post_type='sfwd-lessons' AND wp_postmeta.meta_key='course_id' AND wp_postmeta.meta_value=42

    Then it's the same query as for a course with the list of ID in:

    SELECT * FROM wp_posts WHERE post_type='revision' AND post_parent IN (42, 43)

    The chapters seem to be linked to the course and not the lesson. So same as the first query except for the wp_posts.post_type='sfwd-topic'


  2. 0

    you can try this,
    if wp_posts table contains all the entries for courses, lessons, and revisions.
    (Note: change as your tables)

    — to Get revisions

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

    — to Get all lessons related to the course

    SELECT * FROM wp_posts
WHERE post_type = 'sfwd-lessons' AND post_parent = 1;

    — For each lesson, get all chapters

    SELECT * FROM wp_posts
WHERE post_type = 'sfwd-topic' AND post_parent IN (
    SELECT ID FROM wp_posts
    WHERE post_type = 'sfwd-lessons' AND post_parent = 1
);

    — to Get revisions for each lesson

    SELECT * FROM wp_posts
WHERE post_type = 'revision' AND post_parent IN (
    SELECT ID FROM wp_posts
    WHERE post_type = 'sfwd-lessons' AND post_parent = 1
);

    — to Get revisions for each chapter

    SELECT * FROM wp_posts
WHERE post_type = 'revision' AND post_parent IN (
    SELECT ID FROM wp_posts
    WHERE post_type = 'sfwd-topic' AND post_parent IN (
        SELECT ID FROM wp_posts
        WHERE post_type = 'sfwd-lessons' AND post_parent = 1
    )
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search