1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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

Discussion in 'XenForo Questions and Support' started by DeltaHF, Dec 4, 2014.

  1. DeltaHF

    DeltaHF Well-Known Member

    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?
     
  2. Mike

    Mike XenForo Developer Staff Member

    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 likes this.
  3. DeltaHF

    DeltaHF Well-Known Member

    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: Dec 4, 2014
  4. Mike

    Mike XenForo Developer Staff Member

    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.
     
  5. thedude

    thedude Well-Known Member

    @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.
     
    Mike likes this.
  6. DeltaHF

    DeltaHF Well-Known Member

    Odd, ordering by post_date doesn't seem to work, either.
    I just checked for orphan posts (SELECT * FROM xf_post WHERE thread_id = NULL), but couldn't find any.
    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.
     

Share This Page