Representation of NOT IN combinations

Question

I would like to use squeel in order to build query like:

SELECT * from `table`
WHERE (`field1`, `field2`)
NOT IN (
  (1,"value_a"),
  (2,"value_b"),
  (3,"value_a"),
  ...
)

I want to know if there is any way to compare multiple fields with an array via IN or NOT IN statement.

Something like this (I am aware that example does not work) would be a nice way to express what I mean:

array = [[1,"value_a"], [2,"value_b"], [3, "value_a"]]
Table.where{ (field1 & filed2).not_in array }

Is something like this possible at all?

Update

I know how to get the same final result using multiple ... & (a != b) & (c != d) & ..., but that was not what I have asked.

jdoe · Answer 1 · 2012-12-20T22:25:10.880

1

Maybe this way?

Product.where{
  (title != 'foo') & (description != 'bar') |
  (title != 'baz') & (description != 'xyz')
}

UPDATE

If you already have an array with values to be excluded, you can make a scope like this:

scope :without_title_and_description, lambda{|array| 
  where{array.map{|c1,c2| (title != c1) & (description != c2)}.inject(:|)} 
}

And use it:

array = [[1,"value_a"], [2,"value_b"], [3, "value_a"]]
Product.without_title_and_description(array)

Produces:

  Product Load (1.0ms)  SELECT "products".* FROM "products" WHERE (((("products"
."title" != 1 AND "products"."description" != 'value_a') OR ("products"."title"
!= 2 AND "products"."description" != 'value_b')) OR ("products"."title" != 3 AND
 "products"."description" != 'value_a')))

edited Dec 20 '12 at 22:25

answered Dec 20 '12 at 18:21

jdoe

15,665
2
46
48

No, as that will invoke: `SELECT products.* FROM products WHERE (((products.title != 'foo' AND products.description != 'bar') OR (products.title != 'baz' AND products.description != 'xyz')))` which, while close (in getting correct results) is not what I am trying to achieve. – Krule Dec 20 '12 at 21:12
@Krule Yeap, it's not the same approach, but it gives the same result. If you have an array with values and you want to use it, then see my UPDATE. – jdoe Dec 20 '12 at 22:25
thank you for the effort, but, result, while important, was never an issue. I am trying to optimise a query on a large data set and that solution is what I already have. – Krule Dec 21 '12 at 10:13

score 0 · Accepted Answer · answered Dec 27 '12 at 14:33

Main problem is that, while Arel (and with that Squeel) supports some fairly complex queries, this level of query complexity is not covered. With that in mind, reverting back to SQL and manually constructing needed query is the only solution other than extending Arel itself with this functionality.

existing_compound_ids = [[1,"value_a"],[2,"value_b"],[3, "value_a"]]

compound_collection = existing_compound_ids.inject([]) do |aftermath, member|
  aftermath << "('#{ member[0] }','#{ member[1] }')"
end.join(",")

Table.where("(`tables`.`field1`, `tables`.field2) NOT IN (#{compound_collection})")

Will construct following SQL:

SELECT `tables`.* from `tables` WHERE ((field1, field2)
NOT IN (('1','value_a'),('2','value_b'),('3','value_a')))

I would love to see better, or more elegant, solution, but I have not found one yet.

Representation of NOT IN combinations

Update

2 Answers2

UPDATE