XF 1.4 Finding the Nth non-deleted post on the forum?

[DeltaHF]

My forum is about to hit a major milestone (10 million posts!), and I want to be able to reliably identify the post ID of the ten millionth public (non-deleted) message posted on the forum. Maybe I'm just being dense, but this problem turned out to be a bit more complicated than I anticipated.

I thought I would simply write a query to select all of the post_ids of all visible posts, and spit out the nth one:

SELECT
   post_id
FROM
   xf_post
WHERE
   message_state = 'visible'
ORDER BY
   post_id
ASC
LIMIT 10000000,1

I tested this query with known nth posts from the past (100th post, 5 millionth, etc.), to see if it returned their ID, but I found it to be several weeks off base. It's even several weeks off base when I try to use it to return the most recent post on the forum!

Is there a better way to do this?

 

[Mike]

Technically, the query you wrote does describe the 10 millionth visible post. However, I assume you don't want to include posts in deleted threads, so you need to join xf_thread and check the discussion_state is visible too.

 

[DeltaHF]

Thanks, Mike, I hadn't considered that posts in deleted threads message_state would remain as "visible".

Here's my updated query with an inner join, though I don't really know what I'm doing. The results still appear to be totally incorrect, but I've got a feeling the syntax is wrong.

SELECT
   xf_post.post_id
FROM
   xf_post
INNER JOIN
   xf_thread
ON
   xf_post.thread_id = xf_thread.thread_id
WHERE
   xf_thread.discussion_state = 'visible'
AND
   xf_post.message_state = 'visible'
ORDER BY
   xf_post.post_id
ASC
LIMIT
   10000000,1;

 

[Mike]

Well strictly speaking the post_id isn't the correct order; post_date would be correct. But otherwise, I think that should generally be correct. I suppose if you have threads that have been orphaned (no forum data) it wouldn't account for those but that's all I can think of.

 

[thedude]

@DeltaHF Narrow down the issue more. You said it's occurring even when finding the 100th post. First set the query order by post_date like Mike suggested (just to eliminate that possible discrepancy), then see which post # it starts looking wrong from. Try LIMIT 5,1... then 10,1, etc until it goes bad. Then you can more easily isolate by hand why your query is off.

 

[DeltaHF]

Odd, ordering by post_date doesn't seem to work, either.

Well strictly speaking the post_id isn't the correct order; post_date would be correct. But otherwise, I think that should generally be correct. I suppose if you have threads that have been orphaned (no forum data) it wouldn't account for those but that's all I can think of.

I just checked for orphan posts (SELECT * FROM xf_post WHERE thread_id = NULL), but couldn't find any.

@DeltaHF Narrow down the issue more. You said it's occurring even when finding the 100th post. First set the query order by post_date like Mike suggested (just to eliminate that possible discrepancy), then see which post # it starts looking wrong from. Try LIMIT 5,1... then 10,1, etc until it goes bad. Then you can more easily isolate by hand why your query is off.

Good idea, I wish I had kept better track of milestone posts in my community through the years. I thought I had the 100th, but apparently not. The most solid reference point I have is the 5 millionth post, which was made on May 12, 2011 with post ID 5309438. The query below, however, returns a post on April 22, 2011, with post ID
5233129.

SELECT
   xf_post.post_id 
FROM
   xf_post
INNER JOIN
   xf_thread
ON
   xf_post.thread_id = xf_thread.thread_id
WHERE
   xf_thread.discussion_state = 'visible'
AND
   xf_post.message_state = 'visible'
ORDER BY
   xf_post.post_date
ASC
LIMIT 4999999,1;

Interestingly, I actually can't get this query to return even the most recent post. For example, according to the forum statistics on the home page, the site has 9,998,470 posts, yet when I search with LIMIT 9998469,1, it returns an empty set.

I should note that my forum has converted forum software twice over the years - from Ikonboard to vBulletin in 2002, then from vBulletin 3.8 to XenForo in October of 2013.