php - Wordpress - 分页和自定义数据库查询

Question

我想在列出多个自定义帖子的页面上进行分页。对于我的查询，我没有使用通常的 wp_query 和 args，我使用的是自定义数据库选择

这是我的问题

$query = "SELECT distinct($wpdb->posts.id), omvp_posts.* FROM $wpdb->posts
        inner JOIN $wpdb->postmeta AS rating ON( $wpdb->posts.ID = rating.post_id AND rating.meta_key = 'omvp_vote_rating')
        inner JOIN $wpdb->postmeta AS vote ON( $wpdb->posts.ID = vote.post_id AND vote.meta_key = 'omvp_total_vote')
        inner JOIN $wpdb->term_relationships ON ( $wpdb->posts.ID = $wpdb->term_relationships.object_id )
        WHERE $wpdb->posts.post_status = 'publish'
        AND $wpdb->posts.post_type = 'guides'
        " . $CategoryQuery . "
        ORDER BY rating.meta_value, vote.meta_value DESC LIMIT 0,5";

它按我想要的顺序返回我的帖子，但我只收到其中的 5 个

为了循环处理我的结果，我使用了这段代码:

$pageposts = $wpdb->get_results($query, object); 
if ($pageposts):
global $post;
foreach ($pageposts as $post):
setup_postdata($post); 

对于我的导航，我以前使用过这个函数，但我认为因为我使用的是自定义查询，所以这个函数不再起作用了

function wp_numeric_pagination() {
    global $wp_query;

    $big = 999999999;
    $search_for   = array( $big, '#038;' );
    $replace_with = array( '%#%', '' );
    $tag = '<div class="pagination">' . PHP_EOL;
    $tag .= paginate_links( array(
            'base'              => str_replace( $search_for, $replace_with, esc_url( get_pagenum_link( $big ) ) ),
            'format'            => '?paged=%#%',
            'current'           => max( 1, get_query_var('paged') ),
            'total'             => $wp_query->max_num_pages,
            'prev_next'         => True,
            'prev_text'         => __('«'),
            'next_text'         => __('»'),
            'before_page_number' => '<span>',
            'after_page_number'  => '</span>'
        ) ) . PHP_EOL;

    $tag .= '</div>' . PHP_EOL;
    echo $tag;
}

谢谢!

Answer 1

已修复，我必须在自定义查询的开头添加 SQL_CALC_FOUND_ROWS。

在我的查询中声明了那些变量

    $paged = get_query_var( 'paged' ) ? get_query_var( 'paged' ) : 1;
    $post_per_page = intval(get_query_var('posts_per_page'));
    $offset = ($paged - 1)*$post_per_page;

所以我的新查询是

$query = "SELECT SQL_CALC_FOUND_ROWS distinct($wpdb->posts.id), omvp_posts.* FROM $wpdb->posts
        inner JOIN $wpdb->postmeta AS rating ON( $wpdb->posts.ID = rating.post_id AND rating.meta_key = 'omvp_vote_rating')
        inner JOIN $wpdb->postmeta AS vote ON( $wpdb->posts.ID = vote.post_id AND vote.meta_key = 'omvp_total_vote')
        inner JOIN $wpdb->term_relationships ON ( $wpdb->posts.ID = $wpdb->term_relationships.object_id )
        WHERE $wpdb->posts.post_status = 'publish'
        AND $wpdb->posts.post_type = 'guides'
        " . $CategoryQuery . "
        ORDER BY rating.meta_value, vote.meta_value DESC 
        LIMIT " . $offset . ", " . $post_per_page;

在我的循环中添加了一些代码

/* Determine the total of results found to calculate the max_num_pages
for navigation */
$sql_posts_total = $wpdb->get_var( "SELECT FOUND_ROWS();" );
$max_num_pages = ceil($sql_posts_total / $post_per_page);

if ($pageposts):
global $post;
foreach ($pageposts as $post):

并将我的 max_num_paged 传递给我的分页函数

function wp_numeric_pagination($max_num_pages) {
global $wp_query;

$big = 999999999;
$search_for   = array( $big, '#038;' );
$replace_with = array( '%#%', '' );
$tag = '<div class="pagination">' . PHP_EOL;
$tag .= paginate_links( array(
        'base'              => str_replace( $search_for, $replace_with, esc_url( get_pagenum_link( $big ) ) ),
        'format'            => '?paged=%#%',
        'current'           => max( 1, get_query_var('paged') ),
        'total'             => $max_num_pages,
        'prev_next'         => True,
        'prev_text'         => __('«'),
        'next_text'         => __('»'),
        'before_page_number' => '<span>',
        'after_page_number'  => '</span>'
    ) ) . PHP_EOL;

$tag .= '</div>' . PHP_EOL;
echo $tag;
}