什么是相当于SQL查询的WP_QUERY?

时间:2020-04-09 作者:mohjak

问题在于post_author 岗位类型,主要post 涉及post_author: 每一个post 有一个post_author.

我需要有一个所有帖子作者的列表,按作者的帖子数量降序排列,然后按作者的姓名排序,即post_titlepost_author 岗位类型。

SQL中的解决方案

SELECT a.ID, a.post_title FROM wp_posts a
WHERE a.post_type = \'author_cpt\'
AND a.post_status = \'publish\'
ORDER BY (
    SELECT count(DISTINCT p.ID) AS post_count FROM wp_posts p
    LEFT JOIN wp_postmeta pm ON (p.ID = pm.post_id)
    WHERE pm.meta_key = \'author_id\' 
    AND pm.meta_value = a.ID
    AND p.post_type = \'post\' 
    AND p.post_status = \'publish\'
) DESC, a.post_title ASC;

问题是,有没有办法在WP_Query?

当前解决方案

我将解决方案应用于两个后续步骤:

1) 获取所有帖子作者

$queryArgs = [
    \'post_type\' => \'author_cpt\',
    \'post_status\' => \'publish\',
    \'posts_per_page\' => -1,
    \'orderby\'=> \'title\',
    \'order\' => \'ASC\',
];

$allAuthorsQuery = new WP_Query($queryArgs);
2) 循环查询,用post count构造一个新数组,对结果数组进行排序。

$orderedAuthors = [];
if ( $allAuthorsQuery->have_posts() ) {
    while ( $allAuthorsQuery->have_posts() ) {
        $allAuthorsQuery->the_post();
        $postCount = getPostMetaCount(\'\', get_the_ID());
        $orderedAuthors[] = [
            \'ID\' => get_the_ID(),
            \'post_content\' => get_the_excerpt(get_the_ID()),
            \'post_count\' => $postCount,
        ];
    }
}
wp_reset_query();
sortBy(\'post_count\', $orderedAuthors, \'desc\');
辅助函数

/**
 * Get post count by meta_key and meta_value
 */
function getPostMetaCount($key = \'\', $value = \'\',  $type = \'post\', $status = \'publish\')
{
    if (empty($key))
        return;

    if (empty($value))
        return;

    $query = new WP_Query([
        \'post_type\' => $type,
        \'post_status\' => $status,
        \'meta_query\' => [
            [
                \'key\' => $key,
                \'value\' => $value,
            ]
        ],
    ]);

    return $query->found_posts;
}

/**
 * Sort an associative array by $field
 */
function sortBy($field, &$array, $direction = \'asc\')
{
    usort($array, create_function(\'$a, $b\', \'
        $a = $a["\' . $field . \'"];
        $b = $b["\' . $field . \'"];

        if ($a == $b) return 0;

        $direction = strtolower(trim($direction));

        return ($a \' . ($direction == \'desc\' ? \'>\' : \'<\') .\' $b) ? -1 : 1;
    \'));

    return true;
}
当前解决方案的问题是,在数组中应用排序后,我丢失了按标题排序的顺序。

总体目标是将订单过程转换为SQL或等效的WP\\U查询,以便不需要像上述解决方案中引入的那样对数组进行排序。

谢谢

1 个回复
SO网友:simongcc

因为我没有确切的设置,所以我只使用问题中显示的变量进行编码。代码使用类似的meta值设置进行测试。

如果使用WP\\u Query object,则需要多个自定义字段才能达到此效果

$queryArgs = [
    \'post_type\' => \'author_cpt\',
    \'post_status\' => \'publish\',
    \'posts_per_page\' => -1,
    \'orderby\'=> \'title\',
    \'order\' => \'ASC\',
    \'meta_key\'   => \'post_count\',
    \'orderby\'    => \'meta_value_num\', // sort as number
    \'meta_query\' => array(
        array(
            \'key\' => \'post_count\',
        ),
    ),
];

$allAuthorsQuery = new WP_Query($queryArgs);

// to make the following work, need to add save post count for author to author\'s meta value
// and then fetch the author\'s meta value when fetch from WP_QUERY

// structure is sames as $allAuthorsQuerybySQL, but all default fields are fetched
// pros: all fields are fetched
// cons: one additional meta field is required
// var_dump($allAuthorsQuery->get_posts());
初始化新添加的空字段,并在保存帖子时更新帖子计数

下面说明了保存到用户meta的post\\u count参数。根据问题,因为它是一个自定义post\\U类型,所以需要调整保存post计数的位置。用于将元值用于WP_Query 具有author_cpt. 为了获取,需要将post\\u计数保存在与该post类型meta相同的post\\u类型中。

下面说明了这个想法。

// init for first time run, run once is enough after adding post_count field
$authorArgs = [
    \'post_type\' => \'author_cpt\',
    \'post_status\' => \'publish\',
    \'posts_per_page\' => -1,
    \'orderby\'=> \'title\',
    \'order\' => \'ASC\',
];

$allAuthorsQuery = new WP_Query($authorArgs);

$authorIDs = array();
if ( $allAuthorsQuery->have_posts() ) {
    while ( $allAuthorsQuery->have_posts() ) {
        $allAuthorsQuery->the_post();
        $authorIDs[] = get_post_meta( get_the_ID(), \'author_id\', true );      
    }
    wp_reset_postdata();
}

// to avoid query inside query, so handle separately, add to each author\'s post_count field
foreach ( $authorIDs as $key => $authorID ) {
    $postQuery = new WP_Query([
        \'post_type\' => \'post\',
        \'post_status\' => \'publish\',
        \'author\' => $authorID,
    ]);

    update_user_meta( $authorID, \'post_count\', $postQuery->found_posts );
}

// save to author\'s new custom field
update_user_meta( $post->post_author, \'post_count\', $query->found_posts ); 


// update post count when save a new post
add_action( \'save_post\', \'ws363633_save_post_count\', 10, 3 );
function ws363633_save_post_count( $post_ID, $post, $update ) {
    if ( defined(\'DOING_AUTOSAVE\') && DOING_AUTOSAVE )
    return $post_ID;

    // Only save post\'s post count
    if ( isset( $_POST[\'post_type\'] ) && ( $post_type_object = get_post_type_object( $_POST[\'post_type\'] ) ) && $post_type_object->public && $_POST[\'post_type\'] === \'post\' ) {

        if ( current_user_can( \'edit_post\', $post_ID ) ) {
            $query = new WP_Query([
                \'post_type\' => \'post\',
                \'post_status\' => \'publish\',
                \'author\' => $post->post_author,
            ]);

            // save to author\'s new custom field
            update_user_meta( $post->post_author, \'post_count\', $query->found_posts );

            // for intercept debug
            // var_dump(get_user_meta( $post->post_author, \'post_count\', $query->found_posts ));
            // var_dump($query->found_posts);
            // exit();
        }
    }

    return $post_ID;
}
如果使用原始解决方案,可以使用以下查询方法
// cons: 
// only fetch the fields you have added to the SQL statement

// pros: 
// flexible
$allAuthorsQuerybySQL = $wpdb->get_results("SELECT a.ID, a.post_title FROM {$wpdb->prefix}posts a
WHERE a.post_type = \'restaurant\'
AND a.post_status = \'publish\'
ORDER BY (
    SELECT count(DISTINCT p.ID) AS post_count FROM wp_posts p
    LEFT JOIN {$wpdb->prefix}postmeta pm ON (p.ID = pm.post_id)
    WHERE pm.meta_key = \'author_id\' 
    AND pm.meta_value = a.ID
    AND p.post_type = \'post\' 
    AND p.post_status = \'publish\'
) DESC, a.post_title ASC", OBJECT );

// var_dump($allAuthorsQuerybySQL);