Ah, that might be my stumbling block. I was looking for can_permanently_delete in the admin settings and couldn’t see it.
1 Like
The site setting is hidden because we do not encourage its use. That was developed for the cases when some sensitive information was posted and it must be scrubbed from the database completely. One more thing, that operation is not a bulk operation anyway.
Since it is a hidden site setting, the only way to enable it is from the console.
5 Likes
This is almost exactly what I need! Thank you.
How would I do the syntax so that it only covers all posts deleted before xxxx date?
1 Like
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. 
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 
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
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 methoddestroy_all' for #<Array:0x000055fe7bc7fc98> from (pry):3:inpry’
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 tofind. 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