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;
}
2
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
)
);
Jay Patel is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1