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.

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.

• posts
• customization
• pagination
• query
• select

1 Answer 1

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(); }    ?>