Delete deleted-posts permanently in bulk?

That would be Post.with_deleted.where("deleted_at < 'YYYY-mm-dd'").update_all(...)

7 Likes

Can you let us know how to enable it from the console?

1 Like

Here’s the instructions:

3 Likes

Feature Enabled from console but how i can delete deleted-posts ??? all posts present in deleted-posts

1 Like

I really am no expert on this, but it seems like there’s enough snippets of information in this topic to create the code you’re looking for.

The topic I linked at the start has the warnings from the creators in about this being uncharted territory, and you do it at your own risk. But they also provide the ‘destroy_all’ piece which seems useful, and suggest doing batches to start with.

That topic also has a couple of examples on how to target either posts or topics which (combined with the examples @RGJ has given on how to target different specific posts) should get you pretty close.

I have no experience with this though, so I’m afraid I can’t give it the greenlight as I’ve never done it. :man_shrugging:

2 Likes

In order to delete the old posts on our forum, I used this. It is the result of combining The proper way to completely delete hundred of topics via rails? and @RGJ’s suggestions.

I chose to do this in the end, as I really wanted to remove the data from the database to protect the privacy of users. Rewriting the text to “This post has been deleted” still leaves the edit history intact and relatively easily accessible, so not good enough.

As we had 20,000 topics to destroy, it took a while!

Topic.with_deleted.where("deleted_at < '2021-08-28'").limit(1000).destroy_all

As this left a lot of orphaned posts, I had to follow it up with:

Post.where('topic_id not in (select id from topics)').limit(1000).destroy_all
4 Likes

I imagen that your ./launcher cleanup afterwards also free’d up some space :slight_smile:

1 Like

Unfortunately, despite running as expected the posts table is still full of orphaned posts. The topics table has been cleared nicely. The posts arent reachable when I try and visit them with /t/topic_id, but this is likely due to not having a valid Topic ID. I don’t quite understand why they are still there in the table though.

Can anyone suggest a better way to clean up the posts table?

1 Like

If I am not mistaken there is a field called deleted_at

If it is deleted, then there should be a date time Stamp.

If it is not deleted then is null

Maybe look for the entry that is <> Null and delete

1 Like

Thanks Gav, but the ones that I am targeting aren’t marked as deleted. Rather, their Topic has been deleted and then destroy_all'd.

2 Likes

I was playing around with this and i notice that part of your query was not returning the response you need.

SELECT topic_id from posts does not return an integer, but it returned a string

image

This might be the cause that your db still contains orphaned posts.

2 Likes

I think that you are seeing Data Explorer automagically converting the integer into URL, which it does when the label is topic_id.

When I run this query in Data Explorer, all of the posts that I am attempting to identify are captured (well over 5000):

SELECT id, topic_id
FROM posts
WHERE topic_id not in (select id from topics)
ORDER by id

I’m clearly doing something wrong with my Rails syntax as this is what I’m getting:

[1] pry(main)> Target = Post.where('topic_id not in (select id from topics)')
=> []

Can anyone tell me what I am doing wrong?

1 Like

Well, thanks to @pfaffman I have identified the relevant posts using this:

Post.find_by_sql("select id from posts where topic_id not in (select id from topics)")

I get this output:

[1] pry(main)> Post.find_by_sql(“select id from posts where topic_id not in (select id from topics)”)
=> [#<Post:0x000055df30d4ee90 id: 150>,
#<Post:0x000055df2e538ff0 id: 51097>,
#<Post:0x000055df2e50ba28 id: 83>,
#<Post:0x000055df2e4ee8b0 id: 40636>,
#<Post:0x000055df2e4a92d8 id: 62562>,
#<Post:0x000055df2e4b7978 id: 13522>,
etc

However, I cannot work out how to then apply destroy_all to this selection.

This might help (for my own benefit):

Any suggestions?

1 Like

I think this will do it

posts=
Post.find_by_sql("select id from posts where topic_id not in (select id from topics)")


posts.destroy_all

Or you could add the .destroy_all to your find_by_sql

2 Likes

I’ve tried that. The data seems to be returned as an array with some post identifier and an ID (see Delete deleted-posts permanently in bulk? - #45 by nathankershaw).

This is the error I get when I either add .destroy_all or use the posts= that you suggest:

[2] pry(main)> posts.destroy_all
NoMethodError: undefined method destroy_all' for #<Array:0x000055fe7bc7fc98> from (pry):3:in pry

1 Like

Oh. Then maybe see if

 p=posts.first

Is a post_id. If t that’s it then you can

x=Post.find(p)
x.destroy

Then you could loop through those.

I think you’d need to wrap your query in something to get it to be an array of posts rather than post ids.

1 Like

Thanks Jay. This is what I get:

[3] pry(main)> p=
[3] pry(main)* posts.first
=> #<Post:0x0000563a24cab908 id: 150>
[4] pry(main)> x=Post.find(p)
ArgumentError: You are passing an instance of ActiveRecord::Base to find. Please pass the id of the object by calling .id.
from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/activerecord-6.1.4.1/lib/active_record/relation/finder_methods.rb:467:in `find_one’

Now, tell me about these wrap things. This is so annoying, because I know that @Sam’s rails command here should work, but I think that Rails has changed since then:

1 Like

What did that do? I can’t imagine that rails changed.

1 Like

Does that give you the posts you want to destroy?

1 Like

I did read somewhere online when searching for converting an Active Record Array into an Active Record Relation that things changed between Rails 3.x and Rails 4 and the syntax needs to be different, but it went over my head really.

That seemed to pick up a few when I first ran it, and I duly destroy_all'd them. But there weren’t many. Now doesn’t pick up any at all, while the SQL picks up thousands when run in Data Explorer.

These are orphaned Posts, where the Topic has been Destroy_all'd.

1 Like