CODEWITHSUNDEEP
postgresqlruby-on-rails-4where-clause
legendary_rob
legendary_rob

Reputation: 13012

Rails where.not with multiple arguments

I have an array of ID's I would like to preserve and then delete the rest, we have 3 types of users, Admins, Investors and Students. The list I have is for Students id's, so I would like to delete all .

If I try run this 

User.where.not(id: GOOD_LIST).count(:all)

I will get 15k records. but if I try add an additional condition

User.where.not(id: GOOD_LIST).where(type: "Student").count(:all)
or
User.where.not('id IN ? AND type = ?', GOOD_LIST, "Student").count(:all)

It will return 0 records.

I tried just using the where method

User.where('id NOT IN ?', GOOD_LIST).where(type: "Student").count(:all)

but got the following error message

(10.7ms)  SELECT COUNT(*) FROM "users"  WHERE "users"."type" = 'Student' AND (id NOT IN 39999,40000,40001,40002,40003,40004,40005,40006,40007,40008)
PG::SyntaxError: ERROR:  syntax error at or near "39999"
LINE 1: ...  WHERE "users"."type" = 'student' AND (id NOT IN 39999,4000...

What is the best way of doing this?

Upvotes: 0

Views: 630

Answers (1)

Sergio Tulentsev
Sergio Tulentsev

Reputation: 230441

That generated SQL is missing parentheses around the list. So we should add them in your template, like this

.where('id NOT IN (?)', GOOD_LIST)

Upvotes: 1

Related Questions