How to find what index page a post is on?

Question

Here is a simple example of what I am wanting to figure out.

Say I have WordPress set to show 5 posts per page. And say I there is a post 17 posts back chronologically.

The formula would be 17 / 5 = 3.4. Round up 3.4 to a whole page of 4 and that post 17 posts back is on the 4th index page.

Now... How to do this programmatically...

The number of posts on each index page is determined by the get_option( 'posts_per_page' ); option.

If all I have is the post's id, how do I figure out how many posts back it is chronologically?

My head first went to WP_Query, but it is not a suitable option. The blog this is for has thousands of posts and it runs out of memory before I can get anywhere to determine where the post is at.

Does anyone have an clever suggestions?

Answer 1

There's no way to do it directly but there's a way to determine the position of a particular row in a mysql query. You can find out how to do it here- https://stackoverflow.com/questions/3614666/mysql-get-row-position-in-order-by

Once you have the position you can divide it with the value from get_option('posts_per_page') to determine the correct page number.

Answer 2

Thanks to the suggestion of Mridul Aggarwal, I came up with the following query, which returns a post's chronological position for a given post id.

The post id, post title, and post date can be removed and are just used for debugging / verifying the results.

SELECT
    *
FROM (
        SELECT
            ID as id
        , post_date
        , post_title
        , @rownum := @rownum + 1 AS position
        FROM
            wp_posts
        , (SELECT @rownum := 0) AS rownum
        WHERE
            post_type = 'post'
        AND post_status = 'publish'
        ORDER BY
            post_date DESC
    ) as post_position
WHERE
    id = 12345 /* REPLACE THIS WITH THE POST ID YOU WANT */

Make sure you replace the 12345 for the post id with the post id you want to find the position for.

You can run this query using $wpdb->get_results. More details on $wpdb can be found in the Codex.

With the query results, I can then calculate what paginated index page the post is on using the PHP logic below.

// Get posts per page option.
$posts_per_page = get_option( 'posts_per_page' );

// Calculate what index page a post is on.
$page_index = ceil( $position / $posts_per_page );

And that's it! The $page_index variable will hold the magical paginated index page number!