0

I have Custom Select Query joining two tables and displaying events older than current date. Everything works fine until I try to paginate it.

    <?php
    global $wpdb;
    $date = date("Y-m-d");
    $querystr = "
    SELECT * 
    FROM wp_posts
    JOIN wp_ftcalendar_events ON wp_posts.ID = wp_ftcalendar_events.post_parent
    WHERE wp_posts.post_status =  'publish'
    AND wp_posts.post_type =  'post'
    AND wp_ftcalendar_events.start_datetime < '$date' 
    ORDER BY wp_ftcalendar_events.start_datetime DESC 
     ";

    $total_record = count($wpdb->get_results($querystr, OBJECT_K));

    $paged      = get_query_var('paged') ? get_query_var('paged') : 1;
    $post_per_page  = 3;
    $offset         = ($paged - 1)*$post_per_page;
    $max_num_pages  = ceil($total_record/ $post_per_page);

    $wp_query->found_posts = $total_record;
    $wp_query->max_num_pages = $max_num_pages;

    $limit_query    =   " LIMIT ".$post_per_page." OFFSET ".$offset;  

    $pageposts =   $wpdb->get_results($querystr.$limit_query, OBJECT_K); ?>

     <?php if ($pageposts): ?>

      <?php global $post; ?>

      <?php foreach ($pageposts as $post): ?>

          <?php setup_postdata($post); ?>

          <!-- do stuff -->

     <?php endforeach; ?>

     <?php endif; ?>

     <?php if(function_exists('wp_paginate')) { wp_paginate(); }    ?>  

There's 8 posts in total. I have $post_per_page set to 3 but Wordpress is displaying 2 post on first page and 2 posts on second page. One of the posts on second page is the same as on first page.

When I for example set post_per_page to 6 there's 2 posts on first page and 4 on second. I don't know why is this happening.

0

I found solution. Grouping duplicated posts by ID. Just add this code to select query:

GROUP BY wp_posts.ID 

Whole code (maybe it will help someone - not many examples of working pagination with select query around the internet :)):

<?php
global $wpdb;
$date = date("Y-m-d");
$querystr = "
SELECT * 
FROM wp_posts
JOIN wp_ftcalendar_events ON wp_posts.ID = wp_ftcalendar_events.post_parent
WHERE wp_posts.post_status =  'publish'
AND wp_posts.post_type =  'post'
AND wp_ftcalendar_events.start_datetime < '$date'
GROUP BY wp_posts.ID 
ORDER BY wp_ftcalendar_events.start_datetime DESC 
 ";

$total_record = count($wpdb->get_results($querystr, OBJECT_K));

$paged      = get_query_var('paged') ? get_query_var('paged') : 1;
$post_per_page  = 3;
$offset         = ($paged - 1)*$post_per_page;
$max_num_pages  = ceil($total_record/ $post_per_page);

$wp_query->found_posts = $total_record;
$wp_query->max_num_pages = $max_num_pages;

$limit_query    =   " LIMIT ".$post_per_page." OFFSET ".$offset;  

$pageposts =   $wpdb->get_results($querystr.$limit_query, OBJECT_K); ?>

 <?php if ($pageposts): ?>

  <?php global $post; ?>

  <?php foreach ($pageposts as $post): ?>

      <?php setup_postdata($post); ?>

      <!-- do stuff -->

 <?php endforeach; ?>

 <?php endif; ?>

 <?php if(function_exists('wp_paginate')) { wp_paginate(); }    ?> 

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.