• 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

dethfire

Well-known member
#1
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!!
 

dethfire

Well-known member
#2
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.
 

Brogan

XenForo moderator
Staff member
#3
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.
 

dethfire

Well-known member
#4
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
 

dethfire

Well-known member
#6
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?
 

Valhalla

Well-known member
#7
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?
Perhaps you could set the thread prefix on the threads you have found, and then use the Batch Update Threads tool on those threads.
 

Valhalla

Well-known member
#9
Would that be easier/better than unapproving them? Again I find about 8000 threads via a custom sql query.
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.
 

dethfire

Well-known member
#10
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.
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?
 

Brogan

XenForo moderator
Staff member
#11
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.
 

dethfire

Well-known member
#13
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
 

Mike

XenForo developer
Staff member
#14
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

Well-known member
#15
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".
If I go ahead and delete them permanently via batch update then everything should be cleaned up?