偏移量如何对分页起作用？(获取结果)

时间：2013-05-07 作者：Mark Bean

我跟着this link 并使用分页创建了自己的自定义查询，但我真的不了解偏移量是如何工作的。

这种分页方式效果不佳，偏移量为零值：

function spiciest(){
global $wpdb, $paged, $max_num_pages;

$paged = (get_query_var(\'paged\')) ? get_query_var(\'paged\') : 1;
$post_per_page = intval(get_query_var(\'posts_per_page\')); //6
$offset = ($paged - 1)*$post_per_page;
/* Custom sql here. I left out the important bits and deleted the body
 as it will be specific when you have your own. */
$sql = "
    SELECT DISTINCT * FROM $wpdb->posts
    INNER JOIN (SELECT *, SUBSTRING(name, 6) as \'post_ID\',
    votes_up  AS votes_balance,
    votes_up + votes_down AS votes_total
    FROM thumbsup_items) AS thumbsup
    ON $wpdb->posts.ID = thumbsup.post_ID
    WHERE $wpdb->posts.post_status = \'publish\'
    AND $wpdb->posts.post_type = \'post\'
    AND $wpdb->posts.post_password = \'\'
    ORDER BY votes_up DESC, votes_balance DESC
    LIMIT ".$offset.", ".$post_per_page."; ";

$sql_result = $wpdb->get_results( $sql, OBJECT);

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

print_r("offset ". $offset."\\n") ;
print_r("\\n"."sql_posts_total ". $sql_posts_total."\\n") ;
print_r("\\n"."max_num_pages ". $max_num_pages."\\n") ;
return $sql_result;
}

2 个回复
最合适的回答,由SO网友:Mark Bean 整理而成

这里的问题是限制，它只计算第一页，而不是整个查询。

我通过提供一个辅助SQL查询来计算最大页面数，从而解决了这个问题。谢谢我的朋友们给我这个提示。

这是完整的代码。

function.php

function spiciest(){
global $wpdb, $paged, $max_num_pages;

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


// query normal post
$query_spicy = "
    SELECT DISTINCT * FROM $wpdb->posts
    INNER JOIN (SELECT *, SUBSTRING(name, 6) as \'post_ID\',
    votes_up  AS votes_balance,
    votes_up + votes_down AS votes_total
    FROM thumbsup_items) AS thumbsup
    ON $wpdb->posts.ID = thumbsup.post_ID
    WHERE $wpdb->posts.post_status = \'publish\'
    AND $wpdb->posts.post_type = \'post\'
    AND $wpdb->posts.post_password = \'\'
    ORDER BY votes_up DESC, votes_balance DESC";


//query the posts with pagination
$spicy = $query_spicy . " LIMIT ".$offset.", ".$post_per_page."; ";

$spicy_results = $wpdb->get_results( $spicy, OBJECT);

// run query to count the result later
$total_result = $wpdb->get_results( $query_spicy, OBJECT);


$total_spicy_post = count($total_result);
$max_num_pages = ceil($total_spicy_post / $post_per_page);


return $spicy_results;
}

TEMPLATE CODES:

<?php
 $spiciest = spiciest();

 if ($spiciest):
    global $post;
    foreach ($spiciest as $post) :
        setup_postdata($post);
?>

/**** PUT TEMPLATE TAGS HERE *****/


<?php
    endforeach;
endif;

?>
然后在这里分页，请注意数组中的总数。

 global $wp_rewrite, $wp_query, $max_page, $page;
 $wp_query->query_vars[\'paged\'] > 1 ? $current = $wp_query->query_vars[\'paged\'] : $current = 1;

$pagination = array(
    \'base\' => @add_query_arg(\'page\',\'%#%\'),
    \'format\' => \'\',
    \'total\' => $max_num_pages,
    \'current\' => $current,
    \'prev_text\' => __(\'PREV\'),
    \'next_text\' => __(\'NEXT\'),
    \'end_size\' => 1,
    \'mid_size\' => 2,
    \'show_all\' => false,
    \'type\' => \'list\'
);

if ( $wp_rewrite->using_permalinks() )
        $pagination[\'base\'] = user_trailingslashit( trailingslashit( remove_query_arg( \'s\', get_pagenum_link( 1 ) ) ) . \'page/%#%/\', \'paged\' );

if ( !empty( $wp_query->query_vars[\'s\'] ) )
        $pagination[\'add_args\'] = array( \'s\' => get_query_var( \'s\' ) );

echo paginate_links( $pagination );

SO网友:Matt van Andel

官方WordPress Codex上有一篇关于这个主题的详细文章：http://codex.wordpress.org/Making_Custom_Queries_using_Offset_and_Pagination

基本上，WordPress使用偏移量来计算任何给定页面要显示的帖子。手动设置偏移时，它会覆盖该偏移。

解决此问题有两个步骤：

确保偏移量仅应用于第一页请参阅我链接的文章，以深入了解为什么会发生这种情况以及如何解决它。

结束
标签： theme-development   pagination   wpdb   小码农  

相关推荐

从wpdb-作者/用户目录页面选择

我正在wordpress网站上工作，在那里我创建了一个目录页面（authors.php），其中列出了所有用户和特定的作者元。它工作得很好，但我正试图找出如何只显示填写了所需作者元的用户。我尝试了很多不同的事情，但都没有达到我所需要的。这是我的密码<?php $order = \'user_nicename\'; $users = $wpdb->get_results(\"SELECT * FROM $wpdb->users ORDER BY $order\");