XF 1.5 SQL query to mass delete selected conversations for one user

[Floyd R Turbo]

I'm wanting to delete only selected conversations for one user.

In this instance, the #1 user has over 10,000 conversations and most are carry-overs from previous platforms, and most of these are "Welcome" messages upon registration, or mass-PMs about an event, etc (some with 500 participants).

So I want to outright delete those older than say 1 year, or maybe delete all conversations started by this user with a specific title (which is common for thousands of messages)

I don't want to "delete all" but there is no prune function that allows you to select criteria. That's ok, I can SQL this but I need a pointer or two...I could probably figure it out but hoping someone has the quick answer.

Can this be done with an SQL query?

What table is my target for deleting an entire conversation?

Can I delete only all convos started by a user that have a specific title?

Can I delete convos started by a user that are within a specified date range?

TIA
Bud

 

[Mike]

This really isn't something I could recommend doing via a query -- conversations are very complex and have interactions with quite a few tables.

This is very likely to be something that would require a custom script.

 

[Floyd R Turbo]

So is there no way to select all convos in the Conversation page of your account? Only can select one page worth? This account has 530 pages of convos.


If I could do that then at least I could de-select the ones I want to keep which would be easier

 

[Mike]

Your selections are maintained across pages, though you do need to go page by page and I wouldn't recommend doing a huge number in a batch. Realistically, probably no more than 100-200 at a time.

 

[Floyd R Turbo]

That's the frustration. I want to be able to mass-select all conversations or a certain quantity like the last 200 and apply whatever changes.

probably no more than 100-200 at a time.

100-200 pages worth or 100-200 conversations?

 

[Mike]

100-200 conversations.

Unfortunately, if that isn't viable, it could really only be done via a custom script.

 

[Floyd R Turbo]

Coming back to this, I'm not opposed to manual pruning. I've done more dangerous things. Like manually rebuilding 2 weeks worth of conversations when I transitioned (custom conversion, took 2 weeks to convert "live" database after 2 dry runs, so I had to manually rebuild 2 full weeks of content)

In doing that, I'm very familiar with the relationship of the 4 conversation tables.

So my question is pretty simple - if I delete an entry directly out of the conversation_master table, and then rebuild the conversations, does that wipe out the corresponding entries [containing the same conversation_id] out of the message, recipient, and user tables?

If so, this is academic. But if it would require deleting all entries from all 4 tables that have the same conversation ID, then that's a bit more tricky.

Note: this is on XF1

 

[Floyd R Turbo]

I guess to break this down in the basic terms, when all participants leave a conversation, and the conversation is then purged from the database (permanently deleted), are there any other tables beyond the 4 conversation_ tables affected?

 

[Floyd R Turbo]

So in watching the 4 conversation tables in the database while having test users leave a conversation in a couple different ways, here's what I've been able to confirm: if everyone leaves a conversation, all of the table rows in all 4 tables that contain that conversation_id are deleted. Makes sense.

So the only thing I want to confirm is if there is any other table that is affected when a conversation is deleted (on the front end). Aside from logs.

 

[Paul B]

That should be it.

 

[Floyd R Turbo]

Thanks Bro(gan)