The question:
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('« newer') ?></div>
<div class="next panel"><?php next_posts_link('older »') ?></div>
</div>
}
The Solutions:
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
Method 1
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('« previous vacancies',$max_num_pages) ?></div>
<div class="next panel"><?php next_posts_link('more vacancies »',$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
Method 2
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.
Method 3
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('« newer') ?></div>
<div class="next panel"><?php next_posts_link('older »') ?></div>
</div>
Method 4
<?php
global $wpdb, $paged;
query_posts($query_string . '&posts_per_page=9');
$paged = (get_query_var('paged')) ? get_query_var('paged') : 1;
$author = isset($_GET['author_name']) ? get_userdatabylogin($author_name) : get_userdata(intval($author));
query_posts($query_string . '&posts_per_page=9');
$args = array(
'post_type' => 'post',
'meta_query' => array(
'meta_key' => 'autor',
'post_status' => 'publish',
'meta_value' => $author->id,
),
'paged' => $paged,
'posts_per_page' => 9,
'order' => 'DESC'
);
$postsQuery = new WP_Query( $args );
?>
Template:
<h1>Články od <?php echo $author->display_name; ?></h1>
<ul class="thumbnails">
<?php while ( $postsQuery->have_posts() ) : $postsQuery->the_post(); ?>
<li class="span3">
<div class="thumbnail">
<a href="<?php the_permalink(); ?>" rel="nofollow noreferrer noopener" rel="nofollow noreferrer noopener">
<?php the_post_thumbnail(array(260, 259)); ?>
</a>
<?php
$class = '';
if (in_category('fashion')) {
$class = "link-fashion";
} else if (in_category('beauty')) {
$class = "link-beauty";
} else if (in_category('gourmet')) {
$class = "link-gourmet";
} else if (in_category('lifestyle')) {
$class = "link-lifestyle";
} else if (in_category('about-us')) {
$class = "link-about";
}
?>
<a href="<?php the_permalink(); ?>" rel="nofollow noreferrer noopener" rel="nofollow noreferrer noopener">
<h2 class="<?= $class ?>">
<span></span>
<?php
// short_title('...', 25);
echo get_the_title();
?>
</h2>
</a>
<?php the_excerpt(); ?>
<hr>
</div>
</li>
<?php endwhile; ?>
</ul>
<?php wp_pagenavi(); ?>
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0