That would be
Post.with_deleted.where("deleted_at < 'YYYY-mm-dd'").update_all(...)
That would be
Can you let us know how to enable it from the console?
Here’s the instructions:
Feature Enabled from console but how i can delete deleted-posts ??? all posts present in deleted-posts
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.
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
I imagen that your
./launcher cleanup afterwards also free’d up some space
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
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
Thanks Gav, but the ones that I am targeting aren’t marked as deleted. Rather, their Topic has been deleted and then
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.
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:
 pry(main)> Target = Post.where('topic_id not in (select id from topics)') => 
Can anyone tell me what I am doing wrong?
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:
 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>,
However, I cannot work out how to then apply
destroy_all to this selection.
This might help (for my own benefit):
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
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:
 pry(main)> posts.destroy_all
NoMethodError: undefined method
destroy_all' for #<Array:0x000055fe7bc7fc98> from (pry):3:inpry’
Oh. Then maybe see if
Is a post_id. If t that’s it then you can
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.
Thanks Jay. This is what I get:
 pry(main)> p=
 pry(main)* posts.first
=> #<Post:0x0000563a24cab908 id: 150>
 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
from /var/www/discourse/vendor/bundle/ruby/2.7.0/gems/activerecord-22.214.171.124/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:
What did that do? I can’t imagine that rails changed.
Does that give you the posts you want to destroy?
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