Make pagination work for multiple queries

Anytime you make a custom query, you should manage the pagination yourself. Trying to use the default pagination in a custom query is destined for failure. Many have tried, few have succeeded. The methods that succeed are more trouble than simply managing it yourself.

It’s always desirable to make as few queries as you can, so your second approach has a lot of appeal. If you want the default pagination to work here, you need to abandon your custom query and somehow get the main, default query to meet your needs. This can be done by setting and unsetting various WP_Query query vars in the “pre_get_posts” action. If you can get all the posts you need in a single WP_Query, this is the way to go. The default pagination will work here as long as you don’t use the “offset” argument.

If WP_Query will not work for you for any reason and you need to do a custom query, manage the pagination yourself. Keep track of the current page and preferably, even generate pagination links that pass a custom pagination parameter to use in your custom query. Manage what posts end up on any page with the “offset” and “posts_per_page” arguments.

More information is available here:
Making Custom Queries using Offset and Pagination

If you must use multiple queries on one page, the same concepts apply, except now you need to keep track of the individual offsets of each query.

First of all, thanks @bcworkz, for your extensive explanation of the problem in general. Knowing the problems of multiple queries with pagination, I’ve tried the second way:

if ( have_posts() ) {
    while ( have_posts() ) {
        the_post();

             if ( in_category(11114) === false  ) { // Only display posts not in category with 11114 ID
                // Do what you need to do post post type posts
            } 
    }

    rewind_posts(); // Rewind the loop so we can run it again

    while ( have_posts() ) {
        the_post();

           if ( in_category(11114)   ) { // Only display posts from category with ID 11114
                // Do what you need to do page post type posts
            } 
    }
}

But there is a (currently) not solvable problem. Let’s say the “post_per_page” are set to “5” and let’s say the query have these results:

post 1 is in category 11114
post 2 is in category 11114
post 3 is NOT in category 11114
post 4 is NOT in category 11114
post 5 is in category 11114
post 6 is in category 11114
post 7 is in category 11114
post 8 is NOT in category 11114
post 9 is NOT in category 11114
post 10 is in category 11114

Desired result:

Page 1:

(first the posts that are NOT in category 11114, then posts that are in category 11114, 5 posts per page)
post 3 (NOT 11114)
post 4 (NOT 11114)
post 8 (NOT 11114)
post 9 (NOT 11114)
post 1 (in 11114)

Page 2:

post 2 (in 11114)
post 5 (in 11114)
post 6 (in 11114)
post 7 (in 11114)
post 10 (in 11114)

Real result:

Page 1:

post 3 (NOT 11114)
post 4 (NOT 11114)
post 1 (in 11114)
post 2 (in 11114)
post 5 (in 11114)

Page 2:

post 8 (NOT 11114)
post 9 (NOT 11114)
post 6 (in 11114)
post 7 (in 11114)
post 10 (in 11114)

So, with “posts_per_page” = “5”, WP consider the first 5 posts from the query and if it can find a post not in category 11114 outputs it, otherwise it doesn’t BUT IT COUNTS ALSO THE POSTS SKIPPED. I’ve tried to manually change the “post_count” value with something like $wp_query->set(‘post_count’, $post_count – 1) run each time we find a post that is not in category 11114 but it doesn’ work, its value is always “5” (the value of “posts_per_page”).

As you suggest, I could try to move the logic of grouping the posts to the “pre_get_posts” action, but I don’t know what to set there to make the query outputs first the posts that are not in category 11114 and then the posts that are in category 11114…

You would limit each query to just the posts for the current page. If the total posts per page are 10, you may get 10 posts in one category and none in the other, or 5 posts in each, 1 post in one and 9 in the other, etc.

Then for the next page, you offset the query by 10 to get the next 10 posts, again with variable number of posts in each category. For the 3rd page you offset by 20 to get the next 10 posts, etc.

This scheme would work with pre_get_posts because you don’t restrict the posts by category here, that is done by the loops. You cannot know how many posts will occur in each category, but the total will always be 10.

If you absolutely must have 5 posts (or whatever count) per category on each page, you must do multiple queries. Of course, at some point, one category may run out before the other and you’ll have 5 of one and a lesser amount or none of the other.

Sorry, what solution your referring at speaking of “limit each query to just the posts for the current page”? The solution with multiple queries or the solution with “rewind posts”?

Thanks

Meanwhile I’ve found by me this solution, that works, but I’d like to know if there is a solution event without multiple queries…

`global $wp_query;

$paged = ( get_query_var( ‘paged’ ) ) ? get_query_var( ‘paged’ ) : 1;

$post_per_page = 5; // How many post per page – setup as you need

$sticky = get_option( ‘sticky_posts’ );

$category_posts = get_posts(array(‘category_name’ => get_query_var( ‘category_name’ )));

$category_posts_ids = array();

foreach( $category_posts as $post ) {

$category_posts_ids[]=$post->ID; // Array with posts ID

}

$expired_posts = get_posts(array(‘cat’ => 11114));

$expired_posts_ids = array();

foreach( $expired_posts_ids as $post ) {

$expired_posts_ids[]=$post->ID; // Array with posts ID

}

$array_where_to_get_post = array_diff( $category_posts_ids, $sticky, $expired_posts_ids );

// 1/3 only the sticky posts
$stickies_posts_args = array(
‘category_name’ => get_query_var( ‘category_name’ ),
‘post__in’ => get_option( ‘sticky_posts’ ),
‘orderby’ => ‘date’,
‘order’ => ‘DESC’,
‘posts_per_page’ => -1,
‘fields’ => ‘ids’ // important: to avoid memory exhausted error we retrive postids only
);

// 2/3 only the posts not in cat id 11114 and not the sticky posts
$not_expired_posts_args = array(
‘post__in’ => $array_where_to_get_post,
‘orderby’ => ‘date’,
‘order’ => ‘DESC’,
‘posts_per_page’ => -1,
‘fields’ => ‘ids’ // important: to avoid memory exhausted error we retrieve postids only
);

// 3/3 only the 11114 cat id posts and not the sticky posts
$expired_posts_args = array(
‘category_name’ => get_query_var( ‘category_name’ ),
‘post__not_in’ => get_option( ‘sticky_posts’ ),
‘category__in’ => array(11114),
‘orderby’ => ‘date’,
‘order’ => ‘DESC’,
‘posts_per_page’ => -1,
‘fields’ => ‘ids’ // important: to avoid memory exhausted error we retrieve postids only
);

$firstQuery = get_posts($stickies_posts_args);

$secondQuery = get_posts($not_expired_posts_args);

$thirdQuery = get_posts($expired_posts_args);

$mergePosts = array_merge( $firstQuery, $secondQuery, $thirdQuery ); // Merge all queries

$uniquePosts = array_unique($mergePosts); // Create an array with unique posts

// Final query
$args = array(
‘post_type’ => ‘any’,
‘post__in’ => $uniquePosts,
‘paged’ => $paged,
‘orderby’ => ‘post__in’, // order the posts as they are (already ordered in separate queries)
‘order’ => ‘DESC’,
‘posts_per_page’ => $post_per_page,
‘ignore_sticky_posts’ => 1 // otherwise it breaks pagination
);

$wp_query = new WP_Query($args);

if( $wp_query->have_posts() ):

while( $wp_query->have_posts() ): $wp_query->the_post();

// outputs what you want

endwhile;

endif;

echo paginate_links();`

• This reply was modified 7 years, 10 months ago by Aminta.
• This reply was modified 7 years, 10 months ago by Aminta.
• This reply was modified 7 years, 10 months ago by Aminta.
• This reply was modified 7 years, 10 months ago by Aminta.

A single query can work as long as you don’t mind the variable quantity within each category that may range from none to all, but likely at least few. No matter which way you go, you need to limit the number of posts per page on every query, be it one or several queries. This is necessary for pagination to work without running into scalability problems. You need to know how many were output previously to know the offset into the next page’s query.

The WP_Query argument to limit posts is “posts_per_page”. The default query uses the value from your settings, but this can be altered in custom queries or by changing the default query in “pre_get_posts” action. WP translates the posts per page argument into a SQL LIMIT clause, which is why I’ve been calling this a “limit”.

It’s also possible to get the exact same output that your example does through a single query, but it does not scale well because in this case you would not limit the query. All posts need to be acquired in order to pick out all the category posts prior to outputting anything. This is fine for a modest number of posts, but you run into speed and memory issues in very large databases.

Your multiple query solution also does not scale well, this time because it is very processing heavy. In very large databases there will be a very noticeable delay in page loading. My variable posts per category solution scales well, but it doesn’t sound like it’s really what you are after.

Thanks for your detailed explanation. For performance is it not enough to use the argument ‘fields’ => ‘ids’ in the single queries? Surely it avoids memory exhausted errors, as I have seen… So do you have a solution that gets the exact same output that my example with a single query or using “pre_get_posts” action? Thanks!

I’m particularly interested in this solution of yours:

“You would limit each query to just the posts for the current page. If the total posts per page are 10, you may get 10 posts in one category and none in the other, or 5 posts in each, 1 post in one and 9 in the other, etc.

Then for the next page, you offset the query by 10 to get the next 10 posts, again with variable number of posts in each category. For the 3rd page you offset by 20 to get the next 10 posts, etc.

This scheme would work with pre_get_posts because you don’t restrict the posts by category here, that is done by the loops. You cannot know how many posts will occur in each category, but the total will always be 10.

Please, can you write down a small example?

Thanks!

You actually provided the proper example in your second post (first reply after mine). What may have prevented that from working for you is the query. It sounds like the query was returning all results instead of paged results. You really just need the completely default query that includes the normal pagination arguments. Combine this with your multiple loop example and you’ll get the output I’ve described.

An example output using your loop logic with a default paged archive query where posts per page is 4 and there are 10 posts total:

Page 1
Featured Category
–Post 3
Other Posts
–Post 1
–Post 2
–Post 4

Page 2
Featured Category
–Post 5
–Post 7
–Post 8
Other Posts
–Post 6

Page 3
Featured Category
Other Posts
–Post 9
–Post 10

On each page the posts used in each loop will vary but always add up to 4 (except the last page of course). Page 3 illustrates one issue where there are no posts in the featured category, but the title is output anyway because when the title is output we cannot yet easily know if there are no posts in the category for this particular page. If this is a concern, the solution would be to use output buffering and conditionally output the buffered content once the looping has completed and we know if posts would appear or not under each title.

Regarding your question about performance. Yes, only retrieving the ID fields generally solves the memory issues, but the time involved to make each query in an extremely large database still takes the same amount of time regardless of how much or little data is returned. SQL still has to sift through many thousands of posts to identify the correct IDs. Multiply this search time by the 5 queries and add in computationally demanding array manipulations and you have a very sluggish page!

To be fair, most small business and personal scale sites will never be large enough for this to be a real issue. The poor performance of shared hosting will be the performance bottle neck. But enterprise and social network scale sites can easily have enough data for there to be significant delays where throwing more server resources at the problem will only go so far. A highly optimized query implementation is essential.

Hi bcworkz! You were right about the computational problems!

These two lines:

$category_posts = get_posts(array(‘category_name’ => get_query_var( ‘category_name’ )));
$category_posts = get_posts(array(‘category_name’ => get_query_var( ‘category_name’ )));

made exceed the script time limit in production when calculating

$array_where_to_get_post = array_diff( $category_posts_ids, $sticky, $expired_posts_ids );

So I’ve changed this portion of code in:

$nr_of_posts_to_be_analyzed_to_include_only_current_events = 100; // How many post to anatlyze to include only current events, ie events thata are not in category id 11114

 // posts in category, limited in number otherwise - if you set '-1' and you have 7.000 posts - you exceed the script time limit execution 
$category_posts = get_posts(array('category_name' => get_query_var( 'category_name' ), 'posts_per_page' => $nr_of_posts_to_be_analyzed_to_include_only_current_events,'orderby' => 'date', 'order' => 'DESC'));

// expired posts in category, limited in number otherwise - if you set '-1' and you have 7.000 posts - you exceed the script time limit execution 
	$expired_posts = get_posts(array('category__and' => array(11114, get_query_var('cat')), 'posts_per_page' => $nr_of_posts_to_be_analyzed_to_include_only_current_events, 'orderby' => 'date', 'order' => 'DESC'));

Now, about your last considerations, I fear that there is a misunderstanding. The final output that I would get is not like:

Page 1
Featured Category
–Post 3
Other Posts
–Post 1
–Post 2
–Post 4

Page 2
Featured Category
–Post 5
–Post 7
–Post 8
Other Posts
–Post 6

Page 3
Featured Category
Other Posts
–Post 9
–Post 10

But like:

Page 1
Featured Category
-Post 3
-Post 5
-Post 7
-Post 8

Page 2
Featured Category
-Post 9
-Post 10
Other Posts
–Post 1
–Post 2

So my goal is to output first all the “Featured category” posts (in multiple pages if the total number of these posts exceed the “posts_per_page” one) and then output the “Other posts category” posts…

Any idea to achieve this with the “multiple loop” (and not “mulitple queries”) method?

Last: what did you mean with

the solution would be to use output buffering and conditionally output the buffered content once the looping has completed and we know if posts would appear or not under each title.

?

Many thanks!

• This reply was modified 7 years, 10 months ago by Aminta.
• This reply was modified 7 years, 10 months ago by Aminta.
• This reply was modified 7 years, 10 months ago by Aminta.
• This reply was modified 7 years, 10 months ago by Aminta.

Yeah, that output is what I thought you were after, which is why I was surprised when you inquired further about my suggestion. I see my example convinced you it was not right, which really was my intention. The ensuing explanation was more for complete documentation for anyone else stumbling upon this or in case I was wrong about your expectations and you wanted such output ??

Except for relatively small sites, to keep performance levels reasonable, we accept there needs to be at least one query per page and what happens on other pages is not of immediate concern. This is contrary to your desired scheme. To achieve your goals, there needs to be at least an allowance for two queries per page. On most pages, a single query will suffice, but you’ll need two when the page transitions from featured to other posts.

To achieve the one query per page goal, there may need to be up to 3 queries initially. The first query is a count query to get the total number of featured posts. Count queries are relatively “cheap”. The total count needs to be maintained between requests, either passed as an URL parameter, or kept as a cookie or session variable (which is just another type of cookie). A transient variable is a possibility, but that involves more (cheap) queries. Or you could re-query the count as needed.

The second query is for featured posts only (assuming the count from above is not zero). If the count equals the posts per page or more, the third query can be skipped (other posts). When the featured posts fail to fill the page, query for other posts, limited to just enough posts to fill the page. Subsequent other posts queries factor in this fill count determined from the initial featured count. Given a particular page request, you need the featured count. If it’s not where it’s supposed to be, you need to make a featured post count query. Given any page number and the featured count, you can determine which queries are needed and what the offset and limit values are for each query.

Not the single query, double loop solution you hoped for, but much better than 5 queries per page.

My output buffering idea only applied to the scheme you don’t like. You would run out of memory buffering on a larger scale. Suffice to say that buffering output is possible on a limited scale. I don’t see an application for this in the current scheme.

Thanks bcworkz, for having once again extended the answer to a broader perspective. Only one question: can you please a brief code example of:

The total count needs to be maintained between requests, either passed as an URL parameter, or kept as a cookie or session variable (which is just another type of cookie).

I fear I can’t reproduce it…

Thanks

Saving data in cookies involves javascript. There’s plenty of examples to be found, it’s not specific to WP. It’s probably overkill unless it’s vital that URL parameters not be used to pass data. To pass data by URL parameter, you will need to alter the pagination links. I believe all WP pagination template tags have a filter where this can be accomplished. I suspect you may need to build your own pagination functions anyway. A typical, default pagination link might look like example.com/page/3/

Let’s say the initial count query determined there are 7 featured posts. We need pagination links to look more like example.com/page/3/?featured=7

Adding such a parameter using PHP string functions is a bit messy because you need to account for several possibilities like are there other parameters already, is the URL a permalink or file reference, is there a trailing slash on permalinks, etc. There’s a nifty WP function that deals with all of those variables for us and simply returns the URL we need.
https://developer.www.ads-software.com/reference/functions/add_query_arg/

Then on your template, the featured post count in the URL can be retrieved from $_GET[‘featured’]. If the posts per page is 4 and the featured count is 7, then there is no need at all to run the other posts query and loop on page 1. On page 2, the remaining 3 featured posts are output, then an other posts query is run, limited to 1 result, then output as the fourth post in the loop.

Then on page 3 and subsequent pages, there is no need to run a featured query and loop, just run the other posts query with an offset of 1 for page 3, offset 5 for page 4, etc. Limited to the 4 posts per page each time.

I know you asked for a code example, but I’ve not enough specifics to provide anything meaningful. In any case, the critical elements are simply add_query_arg() and $_GET[‘featured’]. If you encode the above logic into your template, that pretty much accomplishes the task.

Determining the correct offset for each page given the featured count and posts per page is a little tricky. I was never good at algebra word problems, so I’ll leave this for you to work through.

Even trickier is working out the pagination link functions. Mainly related to knowing what the last page is. I think another count query is needed. Come to think of it, maybe the normal WP pagination functions can work. If WP thinks it is paginating a normal all posts query, it’ll deliver the pagination links you need. The total post count is the same, you output is just ordered differently. It’s up to your template to figure out which queries to run for any given page.

If the normal pagination works, then use the appropriate filters for these functions to add the featured count URL parameter.