XF 1.5 SQL query to mass approve & move all "Moderation Queue" content to single thread?

[rdn]

We have 10,000+ Moderation Queue.
And we want to mass approve all of these but merge them all into single thread.

Can someone advice for the correct query?
Thanks!

 

[Jake Bunce]

Here is a start:

https://xenforo.com/community/resources/approve-all-moderated-posts-queries.373/

But in the UPDATE query for posts you can also set the thread_id, like so:

UPDATE xf_post
SET message_state = 'visible', thread_id = X
WHERE message_state = 'moderated';

Change X to the thread_id of the single thread you want to use.

Any moderated threads which are approved will end up empty since you are moving their posts. You may want to just delete those threads by replacing the UPDATE query for threads with this instead:

DELETE
FROM xf_thread
WHERE discussion_state = 'moderated';

This should be safe to do.

Backup first to be safe.

 

[Jake Bunce]

And you will want to rebuild thread info (with post positions) afterwards:

Admin CP -> Tools -> Rebuild Caches

 

[rdn]

This is all I need to do right?

DELETE
FROM xf_moderation_queue
WHERE content_type = 'post';

UPDATE xf_post
SET message_state = 'visible', thread_id = 1018741
WHERE message_state = 'moderated';

DELETE
FROM xf_thread
WHERE discussion_state = 'moderated';

Admin CP -> Tools -> Rebuild Caches

 

[rdn]

I don't need this 2 query anymore Jake?

DELETE
FROM xf_moderation_queue
WHERE content_type = 'thread';

UPDATE xf_thread
SET discussion_state = 'visible'
WHERE discussion_state = 'moderated';

 

[rdn]

DELETE
FROM xf_moderation_queue
WHERE content_type = 'post';

10734 rows affected. (Query took 0.0364 seconds.)

UPDATE xf_post
SET message_state = 'visible', thread_id = 1018741
WHERE message_state = 'moderated';

#1205 - Lock wait timeout exceeded; try restarting transaction

I'm stuck on 2nd query with lock timeout error.

 

[rdn]

DELETE
FROM xf_thread
WHERE discussion_state = 'moderated';

0 rows affected. (Query took 0.6641 seconds.)

 

[rdn]

This is all I need to do right?









Admin CP -> Tools -> Rebuild Caches

From this code, the XF2 equivalent is?

DELETE
FROM xf_approval_queue
WHERE content_type = 'post';