XF 1.4 Query to find thin threads

[dethfire]

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]

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.

 

[Paul B]

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]

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

 

[TheBigK]

I'd be interested in this too.

 

[dethfire]

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]

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.

 

[dethfire]

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

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

 

[Valhalla]

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]

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?

 

[Paul B]

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]

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

Not moderated but "deleted"

 

[dethfire]

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]

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]

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?

 

[Mike]

Yes, that should be ok.

I'll just point out that we don't officially recommend editing the DB directly though.