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

XF 1.4 Query to find thin threads

Discussion in 'XenForo Questions and Support' started by dethfire, Jan 20, 2015.

  1. dethfire

    dethfire Well-Known Member

    I'm looking to prune some old unreplied threads. I'm looking for help in making a sql query that finds threads with 0 replies and with less than 100 characters. thanks!!
     
  2. dethfire

    dethfire Well-Known Member

    Figured it out

    SELECT xf_thread.thread_id, xf_thread.title, xf_post.message
    FROM xf_thread
    INNER JOIN xf_post ON xf_thread.thread_id = xf_post.thread_id
    WHERE xf_thread.reply_count =0
    AND char_length( xf_post.message ) <100
    LIMIT 0 , 30

    However, now I want to delete these threads. How do I do that correctly? I need to delete the thread records and the corresponding post records.
     
  3. Brogan

    Brogan XenForo Moderator Staff Member

    They need to be deleted from the front end or ACP.
    There are too many interconnected tables and records to do it as a query.
     
  4. dethfire

    dethfire Well-Known Member

    Unfortunately I can't set the character count in batch update threads. Right now I'm removing them manually one by one, but I have over 8000 I need to remove. Maybe I need a custom addon. hmmm
     
    Mouth likes this.
  5. TheBigK

    TheBigK Well-Known Member

    I'd be interested in this too.
     
    Mouth and dethfire like this.
  6. dethfire

    dethfire Well-Known Member

    Could a work around be that I use a query to set these threads as "deleted" and then use the XF batch update to permanently delete soft deleted threads?
     
  7. Valhalla

    Valhalla Well-Known Member

    Perhaps you could set the thread prefix on the threads you have found, and then use the Batch Update Threads tool on those threads.
     
  8. dethfire

    dethfire Well-Known Member

    Would that be easier/better than unapproving them? Again I find about 8000 threads via a custom sql query.
     
  9. Valhalla

    Valhalla Well-Known Member

    Your ultimate goal is to be able to use the Batch Update Threads tool to do either of those things because, as explained above, this would be better than manipulating records in the DB.

    My suggestion was to set the "prefix_id" for the thread records you have found.
     
  10. dethfire

    dethfire Well-Known Member

    Right but I'm just asking why is setting the prefix preferred over setting it to deleted if both equally act as a flag for when I use the batch update?
     
  11. Brogan

    Brogan XenForo Moderator Staff Member

    One drawback setting them to unapproved is the system isn't really designed around having thousands of moderated threads.

    So in that respect, the prefix is a better approach.
     
  12. dethfire

    dethfire Well-Known Member

    Not moderated but "deleted" :)
     
  13. dethfire

    dethfire Well-Known Member

    Does this look right?

    UPDATE xf_thread
    SET xf_thread.discussion_state = 'deleted'
    FROM xf_thread
    INNER JOIN xf_post ON xf_thread.thread_id = xf_post.thread_id
    WHERE xf_thread.reply_count =0
    AND char_length( xf_post.message ) < 20
     
  14. Mike

    Mike XenForo Developer Staff Member

    While that would probably work, there is at least one other table that is involved with deleted threads where as there isn't with prefixes. It likely wouldn't make a difference, but setting a prefix (provide you set it to a valid one for that forum) is slightly "safer".
     
    dethfire likes this.
  15. dethfire

    dethfire Well-Known Member

    If I go ahead and delete them permanently via batch update then everything should be cleaned up?
     
  16. Mike

    Mike XenForo Developer Staff Member

    Yes, that should be ok.

    I'll just point out that we don't officially recommend editing the DB directly though. :)
     
    dethfire likes this.

Share This Page