Complex WP SQL Query

时间：2015-02-24 作者：Kobus Beets

我尝试了以下SQL查询，但没有成功。任何对SQL有更好理解的人都能提供帮助吗？

以下是我正在努力实现的目标：

获取父项id为0的所有主要事件（ME）的列表根据从子查询获得的最早子事件的发布日期对ME列表进行排序我不知道如何在默认的WP\\u查询对象中获得相同的结果。

SQL代码：

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

将使用它的代码。

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\');
        }
    }
}

2 个回复

最合适的回答,由SO网友:Kobus Beets 整理而成

我试图完成的事情似乎一点也不复杂。

SQL代码：

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;

我有两种方法可以做到这一点。出于性能原因，我使用第二个选项。

选项1：第一个选项运行两个查询。第一个查询是获取“未来子”事件的ID，第二个查询是仅包括具有所提供ID的子记录。

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\'));
        }
    }
}

选项2：第二个选项仅运行一个查询。它更改默认的WP数据库查询，以从数据库中获取所需的结果。请注意，我在这里使用了两个挂钩，即pre\\u get\\u posts和posts\\u 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));
        }
    }
}

SO网友:Kobus Beets

经过数小时的搜索和测试，我终于找到了一个解决方案。此代码和SQL显示父id为0的事件，并按子事件的日期排序。

原始SQL不能用来取代WP查询数据库的默认方式，但总有一些解决方法可以使用。

PHP代码：

<?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\'));
        }
    }
}
?>

格式化的SQL查询：

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-谢谢你的提示。我还在学习使用这个网站，所以我会记住它。

如果有任何更简单的解决方案可以实现相同的结果，请分享：）

结束

小码农CODE

Complex WP SQL Query

相关推荐

帮助SQL查询，如何添加带有选项中存储的值的分类术语？