Bulk suspend users based on criteria


#1

Can I suspend all users who were seen prior to some date, for example December 31, 2016, and add reason for the suspension as membership expired?

I have ~1700 of these.


Bulk amend member digest subscriptions
Apply category notification preferences to existing users
(Jeff Atwood) #2

I believe @jomaxro or @blake have done this for a customer before.


#3

Hmm, I think I can use @David_Taylor’s plugin for this, unless it’s better to do it in the rails console?

I only need to do this once as part of cleaning up stuff from an imported forum.


(Blake Erickson) #4

If you would like to use the rails console to bulk suspend users, something like this should do the trick:

suspend_till = DateTime.new(2057,1,1)
users = User.where(last_seen_at: nil, id: 1..Float::INFINITY, admin: false)
users.each do |u|
  u.suspended_till = suspend_till
  u.suspended_at = DateTime.now
  u.save!
end

Ability to modify user email settings for group
#5

Thanks, Blake.

Since I want to suspend users who were last seen at a defined date, will this work instead of nil?

users = User.where(last_seen_at: < '2016-12-31', id: 1..Float::INFINITY, admin: false)

Also, what does id: 1..Float::INFINITY do in this case?


(Blake Erickson) #6

Yes something like that will work but I don’t think the less than sign works with the hash syntax but you can play around in the rails console first to see the results before you execute the commands in the loop. The infinity thing is just a hack to write greater than 1 so you don’t disable system and discobot who have negative user ids.


#7

Borrowing from @pfaffman’s syntax here, this seems like it will work:

users = User.where("last_seen_at < '2016-12-31'", id: 1..Float::INFINITY, admin: false)

How would I get a count of these users in order to cross-check with an exported csv list of users before executing it?

I would like to include a reason for the suspension, but the users schema doesn’t list it, so what to use?


(Jay Pfaffman) #8

Add

 .count

To the end of that line.


#9

I’ve realized that using last_seen_at for the filter is going to suspend too many current members to fix manually, so I’d like tweak this slightly to suspend all users who are not members of a group.

My attempt with

users = User.where.not(group_id: 41, id: 1..Float::INFINITY, admin: false)

failed because

ERROR: column users.group_id does not exist

What should I use instead?


(Blake Erickson) #10

There is no group_id column on users. There is instead a separate group_users table and groups table. I usually connect to pg directly to list all the tables and poke around, but you can list all the tables in the rails console with ActiveRecord::Base.connection.tables

You will need do do a join like:

users = User.joins(:group_users).where.not(group_users: {group_id: 41}).where(id: 1..Float::INFINITY, admin: false)

#11

There seems to be some funny counting happening.

The count of users not in group_id: 41 comes up as 4830, but there are a total of 2597 users. group_id:41 has 748 members.

Oddly, the dashboard shows 2597 users, but a csv export shows 2572.


(Blake Erickson) #12

You probably need to add a DISTINCT somewhere.


#13

Thanks Blake - distinct does the trick:

User.joins(:group_users).where.not(group_users: {group_id: 41}).distinct.count
=> 2572

But something about my .not is off as it’s counting all users, not just the non-members of the group. The number should be 1823 instead of 2572.

 users = User.joins(:group_users).where(group_users: {group_id: 41}).distinct.count
=> 749

(Blake Erickson) #14

Sorry I’m on my phone but I think the not is turning into a ‘!=‘ and I think we need to do a “not in” query


(Kane York) #15
results = ActiveRecord::Base.exec_sql("SELECT id FROM users WHERE NOT EXISTS ( SELECT 1 FROM group_users AS gu WHERE gu.group_id = 41 AND gu.user_id = users.id )")
users = results.map { |row| User.find row[:id] }

#16

Thanks, Kane.

The count comes up correct:

=> #<PG::Result:0x000055b571a732e0 status=PGRES_TUPLES_OK ntuples=1823 nfields=1 cmd_tuples=1823>

Do I just append the remainder of the code in the console to execute it, like this?

suspend_till = DateTime.new(2057,1,1)
results = ActiveRecord::Base.exec_sql("SELECT id FROM users WHERE NOT EXISTS ( SELECT 1 FROM group_users AS gu WHERE gu.group_id = 41 AND gu.user_id = users.id )")
users = results.map { |row| User.find row[:id] }
users.each do |u|
  u.suspended_till = suspend_till
  u.suspended_at = DateTime.now
  u.save!
end

(Bart) #17

I could use a little help with this; I’m trying to suspend all users in a specific group. When I run the following script it generates a long list of users, runs for some time but the users aren’t actually updated. What am I doing wrong?

suspend_till = DateTime.new(2057,1,1)
users = User.joins(:group_users).where(group_users: {group_id: 49})
users.each do |u|
  u.suspended_till = suspend_till
  u.suspended_at = DateTime.now
  u.save!
end```