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>
}
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
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