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

DeltaHF

Best Weekly GIF Winner
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:

Code:
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?
 
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.
 
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. :unsure:

Code:
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;
 
Last edited:
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.
 
@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.
 
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.

Code:
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. :confused:

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.
 
Top Bottom