Dear Team, Please help me out to resolve this! We have too many deleted-posts and we need to delete that permanently but in bulk! [image]

Excellent, glad to hear that did the job for you. So for the sake of combining the answers, one can use the following in the Rails console to destroy all topics that were deleted more than 90 days ago, repeating as many times as necessary if there are more than 1000 topics: Topic.with_deleted.where(deleted_at: ...90.days.ago).limit(1000).destroy_all After that has completed, the following can be used to destroy all posts that have been orphaned from destroyed topics: Post.find_by_sql("select * from posts where topic_id not in (select id from topics)").each { |p| p.destroy } It’s worth noting that the above commands will not destroy deleted posts however, only deleted topics and their orphaned posts. To also destroy deleted posts older than 90 days, use the following, again repeating as necessary: Post.with_deleted.where(deleted_at: ...90.days.ago).limit(1000).destroy_all P.S. Out of interest, did you try destroy_all without limit(1000) and have problems or did you not try it without the limit?

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

[image] IAmGav: 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 destroy_all’d.

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.

[image] IAmGav: does not return an integer, but it returned a string 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?

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)”) => [# , # , # , # , # , # , etc However, I cannot work out how to then apply destroy_all to this selection. This might help (for my own benefit): [image] The Where Subquery - Advanced Active Record: Using Subqueries in Rails Active Record provides a great balance between the ability to perform simple queries simply, and also the ability to access the raw SQL sometimes required to get our jobs done. In this article, we will see a number of real-life examples of business... Any suggestions?

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

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 nathank). 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 # from (pry):3:in pry’

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.

Thanks Jay. This is what I get: [3] pry(main)> p= [3] pry(main)* posts.first => # [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’ [image] pfaffman: I think you’d need to wrap your query in something to get it to be an array of posts rather than post ids. 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: [image]The proper way to completely delete hundred of topics via rails? Post.where('topic_id not in (select id from topics)').limit(100).destroy_all

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

Delete deleted-posts permanently in bulk?

Product Support

pfaffman (Jay Pfaffman) November 28, 2021, 11:29am 51

Does that give you the posts you want to destroy?

Topic		Replies	Views
Purge/clean/delete deleted topics from database Product Support	4	511	July 5, 2025
I am getting 502 error while trying to delete topics created by specific user Product Support	18	1376	June 12, 2023
Administrative Bulk Operations Self-Hosting configuring , reference	9	14863	June 12, 2026
Clean up old topics Product Support	4	678	February 2, 2023
Help Wiping all Posts from a Forum Product Support	18	2454	August 26, 2020

Delete deleted-posts permanently in bulk?

Related topics