Complex WP SQL Query

After hours of searching and testing I finally came to a solution. This code and SQL shows events with a parent id of 0 and is ordered by the dates of child events.

Raw SQL cannot be used to replace the default way WP queries the database but there are always some kind of workaround that can be used.

The PHP Code:

<?php 
function pre_get_posts($query) {       
    global $wpdb;
    if(!is_admin() && $query->is_main_query()) {
        if(is_post_type_archive('event')) {
            $my_query = $wpdb->prepare('SELECT * FROM %1$s AS A LEFT JOIN (SELECT * FROM (SELECT * FROM %1$s WHERE post_type="event" AND post_date > "'.date('Y-m-d').'" ORDER BY post_date ASC) AS B WHERE B.post_type = "event" AND B.post_status IN ("publish", "future") AND B.post_parent != 0 GROUP BY B.post_parent ORDER BY B.post_date ASC) AS C ON A.ID=C.post_parent WHERE A.post_type = "event" AND A.post_status IN ("publish", "future") AND A.post_parent = 0 ORDER BY C.post_date ASC;', $wpdb->posts);
            $ids = $wpdb->get_col($my_query);

            $query->set('post__in', $ids);
            $query->set('order', 'ASC');
            $query->set('orderby', 'C.post_date');
            $query->set('posts_per_page', -1);
            $query->set('post_status', array('publish', 'future'));
        }
    }
}
?>

The Formatted SQL Query:

SELECT *
FROM wp_posts AS A
LEFT JOIN
    (
        SELECT *
        FROM (
            SELECT *
            FROM wp_posts
            WHERE post_type="event"
            AND post_date > "'.date('Y-m-d').'"
            ORDER BY post_date ASC
        ) AS B
        WHERE B.post_type = "event"
        AND B.post_status IN ("publish", "future")
        AND B.post_parent != 0
        GROUP BY B.post_parent
        ORDER BY B.post_date ASC
    ) AS C
ON A.id=C.post_parent
WHERE A.post_type = "event"
AND A.post_status IN ("publish", "future")
AND A.post_parent = 0
ORDER BY C.post_date ASC; 

@kaiser – Thank you for the tips. I’m still learning to use this site so I will keep it in mind.

If there are any simpler solutions to accomplish the same results, please share 🙂