0

I followed this link and created my own custom query with pagination, But I don't really understand how the offset work.

This pagination does not work well, and I'm getting zero value for offset:

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;
}
  • What doesn't work? The pagination I see appears to be working just fine. Of course, you linked to an entire site so it is a bit hard to figure out exactly where to look. – s_ha_dum May 7 '13 at 13:14
0

The problem here is the LIMIT, it'll just count the first page and not the entire query.

I had solved it by providing a secondary SQL query for counting the max pages. thanks for my friends for this tip.

here's the complete code.

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;

?>

and then the PAGINATION here, please note of the TOTAL in array.

 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 );
0

There is a detailed article on this subject on the official WordPress Codex: http://codex.wordpress.org/Making_Custom_Queries_using_Offset_and_Pagination

Basically, WordPress uses offset to calculate which posts to show for any given page. When you set offset manually, it overrides that.

There are two steps to fixing the problem:

  1. Ensure offset is only applied on the first page.
  2. Manually calculate offset for each page by subtracting your offset from WordPress's automatically calculated offset.

See the article I linked for a very in-depth breakdown of why this happens and how to fix it.

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.