Custom SQL query and pagination (wordpress)

[original question below]

Sql request took the following form (thanks, @Mike):

global $wpdb; $myquery = "SELECT wp_posts.ID, wp_posts.post_author, wp_posts.post_date, wp_posts.post_date_gmt, wp_posts.post_content, wp_posts.post_title, wp_posts.post_excerpt, wp_posts.post_status, wp_posts.comment_status, wp_posts.ping_status, wp_posts.post_password, wp_posts.post_name, wp_posts.to_ping, wp_posts.pinged, wp_posts.post_modified, wp_posts.post_modified_gmt, wp_posts.post_content_filtered, wp_posts.post_parent, wp_posts.guid, wp_posts.menu_order, wp_posts.post_type, wp_posts.post_mime_type, wp_posts.comment_count, coalesce(MAX(wp_comments.comment_date),wp_posts.post_date) AS com_date FROM wp_posts LEFT OUTER JOIN wp_comments ON wp_posts.ID = wp_comments.comment_post_id WHERE post_status = 'publish' AND post_type = 'post' GROUP BY ID ORDER BY com_date DESC"; 

Thus, all sorting occurs only in it. Then I count the total number of posts, the number of posts on one page, the current page and the starting number of entries on the page.

 $total_query = "SELECT COUNT(1) FROM (${myquery}) AS combined_table"; $totalposts = $wpdb->get_var( $total_query ); $items_per_page = 2; $currentpage = isset( $_GET['cpage'] ) ? abs( (int) $_GET['cpage'] ) : 1; $offset = ( $page * $items_per_page ) - $items_per_page; 

And I add LIMIT for pagination to the request:

 $myresult = $wpdb->get_results( $myquery . " LIMIT ${offset}, ${items_per_page}"); 

Next, I use paginate_links () , and it works! But comments are not displayed on all pages except the first. Where is the mistake?

 // всё вышеописанное... // пагинация echo paginate_links( array( 'base' => add_query_arg( 'cpage', '%#%' ), 'format' => '', 'prev_text' => __('«'), 'next_text' => __('»'), 'total' => ceil($totalposts / $items_per_page), 'current' => $currentpage )); // вывод постов if($myresult) { foreach ($myresult as $post) { setup_postdata($post); // моя сортировка по категориям (думаю, так делать нехорошо) $cats = get_the_category(); if($cats) { foreach ($cats as $cat) { if ($cat->cat_ID == 1) { // ... заголовки, контент и др. информация о постах break; } } } } wp_reset_postdata(); } 

Original question.

There is a sql request for displaying posts (Wordpress):

 global $wpdb; $myquery = "SELECT wp_posts.ID, wp_posts.post_author, wp_posts.post_date, wp_posts.post_date_gmt, wp_posts.post_content, wp_posts.post_title, wp_posts.post_excerpt, wp_posts.post_status, wp_posts.comment_status, wp_posts.ping_status, wp_posts.post_password, wp_posts.post_name, wp_posts.to_ping, wp_posts.pinged, wp_posts.post_modified, wp_posts.post_modified_gmt, wp_posts.post_content_filtered, wp_posts.post_parent, wp_posts.guid, wp_posts.menu_order, wp_posts.post_type, wp_posts.post_mime_type, wp_posts.comment_count, MAX(wp_comments.comment_date) AS com_date FROM wp_posts LEFT OUTER JOIN wp_comments ON wp_posts.ID = wp_comments.comment_post_id WHERE post_status = 'publish' AND post_type = 'post' GROUP BY ID"; $myresult = $wpdb->get_results($myquery); 

The result (array) of which is then slightly edited, namely:

1. If the post has no comments, then the date of this post is duplicated in the 'Last Comment Date' column.
2. The entire array is sorted by the date of the last comment.

ps Maybe the last 2 actions could be organized in one sql query (there is not enough knowledge)

 // 1. if ($myresult) { foreach ($myresult as $str) { if ($str->com_date) { } else { $str->com_date = $str->post_date; } } } // 2. function cmp($a, $b) { return strcmp($b->com_date, $a->com_date); } usort($myresult,"cmp"); 

Next comes the output of posts:

 if($myresult) { foreach ($myresult as $post) { setup_postdata($post); the_author(); the_title(); // ... другая информация // а здесь нужно организовать пагинацию (!) 

Next you need to organize pagination. In this case, the sorting by the last comment should not go off. How can I do it?

ps I tried to rely on the following solution https://wordpress.stackexchange.com/questions/53194/wordpress-paginate-wpdb-get-results/#53195 But then the comments on the pagination pages are not displayed (displayed only on the main one) and the posts are sorted (obtained in the reverse order).