Rails where.not with multiple arguments

Question

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?

have u tried this User.where("id NOT IN (?) AND type = ?", GOOD_LIST, "Student").count — Amit Sharma
– Amit Sharma, Commented Feb 12, 2016 at 8:06
@SergioTulentsev Your first comment should fix it. While using IN, we need to use like (?). — Arup Rakshit
– Arup Rakshit, Commented Feb 12, 2016 at 8:08
@SergioTulentsev Is correct, And you can write the same code as User.where.not(id: GOOD_LIST).where(type: "Student").count(:all) — Arup Rakshit
– Arup Rakshit, Commented Feb 12, 2016 at 8:10

Sergio Tulentsev · Accepted Answer · 2016-02-12 08:13:04Z

1

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)

answered Feb 12, 2016 at 8:13

Sergio Tulentsev

231k43 gold badges381 silver badges373 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Rails where.not with multiple arguments

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related