1

I have tried the following SQL query without any success. Can anybody that have a better understanding of SQL help?

Here is what I am trying to achieve:

  • Get a list of all main events (ME) where the parent_id = 0.
  • Order the ME list based on the post date of the earliest child events which is obtained from a sub query.
  • Perform this query instead of the default WP query.

I am not sure how to achieve the same results in the default WP_Query object.

The SQL Code:

SELECT * FROM `wp_posts` AS ME LEFT JOIN 
    (
    SELECT * FROM `wp_posts` AS SE 
    WHERE SE.`post_type`="event" 
    AND SE.`post_status` IN ("publish", "future") 
    AND SE.`post_parent` != 0 
    GROUP BY SE.`post_parent` 
    ORDER BY SE.`post_date` ASC 
    LIMIT 1
    ) AS SE2 
ON ME.ID = SE2.post_parent 
WHERE ME.post_parent = 0 
AND ME.post_type="event" 
AND ME.post_status IN("publish", "future") 
ORDER BY SE2.post_date

The code where it will be used.

function pre_get_posts($query) {       
    global $wpdb;
    if(!is_admin() && $query->is_main_query()) {
        //check if the post type matches this post type
        if(is_post_type_archive('event')) {
             //will this work?
             $query->set('query', 'SELECT * FROM `wp_posts` AS ME LEFT JOIN (SELECT * FROM `wp_posts` AS SE WHERE SE.`post_type`="event" AND SE.`post_status` IN ("publish", "future") AND SE.`post_parent` != 0 GROUP BY SE.`post_parent` ORDER BY SE.`post_date` ASC LIMIT 1) AS SE2 ON ME.ID = SE2.post_parent WHERE ME.post_parent = 0 AND ME.post_type="event" AND ME.post_status IN("publish", "future") ORDER BY SE2.post_date');
        }
    }
}
4
  • 1
    Please properly format your SQL query. Everyrhing is in one looooooong row, very difficult to read and to debug as almost everything is out of bounce :-) Feb 24, 2015 at 10:38
  • I was in quite a rush when I posted. I hope it's properly formatted now. Thank you. Feb 24, 2015 at 10:49
  • 2
    @KobusBeets Hint: When people see a scroll bar for code, they normally just skip the question. Aside from that, you may want to explain (in an edit) why you didn't use a custom WP_Query? Also: \WP_Query::set( $key, $val ) only allows setting a query var, not executing a random SQL statement. It looks like you ain't got debugging activated. Enable debugging - blueprint for wp-config.php - and file an edit. Last note: Please do not ask when are "in quite a rush". Just do it later.
    – kaiser
    Feb 24, 2015 at 10:50
  • @kaiser - Thank you for the hints. I enabled debugging and actually saw that what I tried was not going to work. I finally came up with a solution but need to still test it with some data. It worked with a few events but need to load more. Cheers. Feb 24, 2015 at 13:19

2 Answers 2

1

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 :)

1
  • First, $GLOBALS['wpdb']->last_query will give you a query that you can dump right after running it. And second, I'm pretty sure that you could do the same with a normal WP_Query.
    – kaiser
    Feb 24, 2015 at 13:22
0

The very thing I tried to accomplish appear not to be complex at all.

SQL Code:

SELECT   *
FROM     wp_posts
WHERE    post_type = "event"
AND      post_status IN ("publish", "future")
AND      post_date > "2015-02-26"
AND      post_parent != 0
GROUP BY post_parent
ORDER BY post_date ASC;

There were two ways I could do this. I am using the second option for performance reasons.

Option 1: The first option runs two queries. The first query is to get the ID's of "future child" events and the second is to only include the child records with the provided ID's.

add_action('pre_get_posts', 'modify_event_pre_get_posts');
function modify_event_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 WHERE post_type = "event" AND post_status IN ("publish", "future") AND post_date > '.date('Y-m-d').' AND post_parent != 0 GROUP BY post_parent ORDER BY post_date ASC;', $wpdb->posts);
            $ids = $wpdb->get_col($my_query);

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

Option 2: The second option only runs one query. It changes the default WP database query to fetch the desired results from the database. Take note that I used two hooks here namely pre_get_posts, and posts_groupby.

add_action('pre_get_posts', 'modify_event_pre_get_posts');
add_filter('posts_groupby', 'modify_event_posts_groupby');

function modify_event_posts_groupby($group_by) {
    global $wpdb;
    if(!is_admin() && is_main_query()) {
        if(is_post_type_archive('event')) {
            $group_by = "{$wpdb->posts}.post_parent";
        }
    }
    return $group_by;
}

function modify_event_pre_get_posts($query) {
    global $wpdb;
    if(!is_admin() && $query->is_main_query()) {
        if(is_post_type_archive('event')) {
            $query->set('post_status', array('publish', 'future'));
            $query->set('date_query', array('after' => date('Y-m-d')));
            $query->set('order', 'ASC');
            $query->set('orderby', 'post_date');
            $query->set('post_parent__not_in', array(0));
        }
    }
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.