Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

I have my own SQL string to select Custom Post Type posts with specific WHERE clause. I've used the offset and limit to return appropriate posts depending on a page being displayed. That's working fine.

Now, I would like to make previous_posts_link() and next_posts_link() functions work. They both are called from get_posts_nav_link which uses global $wp_query.

Is there a way I can re-assign global $wp_query with my SQL string or $wpdb->get_results results or anything else? So the native previous_posts_link() and next_posts_link() WP functions would work.

If not, how can I reproduce the prev and next post link functions?

I would really appreciate any help and advice! I'm totally stuck with this.
Thanks :)

NOTE: I have just noticed that previous_posts_link() is working correctly on all the pages, but no idea why and in this case, why next_posts_link doesn't work :S

Here is the code:

$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;

$sql = "
SELECT SQL_CALC_FOUND_ROWS  wp_posts.*, wp_postmeta.* 
FROM wp_posts 
INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id) 
WHERE 1=1  
    AND wp_posts.post_type = 'movie' 
    AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') 
    AND ((wp_postmeta.meta_key = '_expiry_date' AND CAST(wp_postmeta.meta_value AS DATE) >= '".$current_date."') 
        OR (mt1.meta_key = '_expiry_date' AND CAST(mt1.meta_value AS CHAR) = ''))
GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_date DESC
LIMIT ".$offset.", ".$post_per_page;

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

if($movies_all_current) {
global $post;

//loop
foreach( $movies_all_current as $key=>$post ) {
    setup_postdata($post);
    //display each post
    //...
} //end foreach ?>

    //navigation
<div class="navigation">
    <div class="previous panel"><?php previous_posts_link('&laquo; newer') ?></div>
    <div class="next panel"><?php next_posts_link('older &raquo;') ?></div>
</div>
}
share|improve this question

3 Answers 3

up vote 10 down vote accepted

Ok, I got there at the end. I couldn't use WP_Query class as I really needed to have my own pretty big and complex SQL. Here is what I ended up having:

In functions.php I have my custom SQL and logic for counting the values needed for the WP pagination logic:

function vacancies_current( ){
    global $wpdb, $paged, $max_num_pages, $current_date;

    $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;

    /* 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 SQL_CALC_FOUND_ROWS  {$wpdb->posts}.*
        FROM {$wpdb->posts}
        ....
        GROUP BY {$wpdb->posts}.ID 
        ORDER BY {$wpdb->posts}.post_date 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);

    return $sql_result;
}

Then in my template file I'd have:

<?php 
    $vacancies_current = vacancies_current();
    /*followed by a standart loop to display your results */ 
 ?>
<div class="navigation">
    <div class="previous panel"><?php previous_posts_link('&laquo; previous vacancies',$max_num_pages) ?></div>
    <div class="next panel"><?php next_posts_link('more vacancies &raquo;',$max_num_pages) ?></div>
</div>

The trick was in supplying previous_posts_link() and next_posts_link the $max_num_pages value and obviously in calculating it correctly.

This works very well. Hope it will help someone :)

Dasha

share|improve this answer
    
+1 nice work. I came across this (and borrowed heavily, thanks) while researching my answer to stackoverflow.com/questions/16057059/…. I was wondering if you know of a way to use a custom SQL statement like this, but in a pre_get_posts() action as per codex.wordpress.org/…? I find this solution is susceptible to the 404 on last page issue, as per wordpress.org/support/topic/…. How did you overcome this? –  Sepster Apr 23 '13 at 14:34

Take a look at Custom Queries - which allow you to modify the wp_query call in many interesting and useful ways, and push the results back into your global query object.

share|improve this answer

Expanding on Anu's answer. Instead of relying on your custom sql query you could use the WP_Query class and let WordPress handle all the heavy SQL lifting. This would surely solve your navigation issue.

Example query for movie post type within your _expiry_date meta_key:

$today = getdate();
$args = array(
    'post_type' => 'movie',
    'meta_query' => array(
            'meta_key' => '_expiry_date',
            'meta_value' => $today,
            'meta_compare' => '< '
                    ),
    'posts_per_page' => -1,
     'order'    => 'DESC'
    );

    $movie_query = new WP_Query( $args );

    while ( $movie_query->have_posts() ) : $movie_query->the_post(); 
    // Do stuff
   endwhile; ?>

 <div class="navigation">
<div class="previous panel"><?php previous_posts_link('&laquo; newer') ?></div>
<div class="next panel"><?php next_posts_link('older &raquo;') ?></div>
</div>
share|improve this answer
    
thank you for the answer, however, I can't rely on WP_Query class as I need to construct my own custom SQL. I got there at the end, see my reply if interested :) –  dashaluna Sep 16 '11 at 14:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.