Support for ANY in condition DSL

[wuarmin]

Describe the bug

Following code

filter_type_id = "test"
states
  .where { filter_type_ids.any(filter_type_id) | filter_type_ids.is(nil) }
  .order(:order)

produces following sql:

SELECT "states"."id", "states"."name", "states"."order", "states"."filter_type_ids"
FROM "states"
WHERE ("states"."filter_type_ids" IS NULL)
ORDER BY "order"

but the resulting sql should be following:

SELECT "states"."id", "states"."name", "states"."order", "states"."filter_type_ids"
FROM "states"
WHERE (('test' = ANY("states"."filter_type_ids")) OR ("states"."filter_type_ids" IS NULL))
ORDER BY "order"

If I change the order of the OR-condition

states
  .where { filter_type_ids.is(nil) | filter_type_ids.any(filter_type_id) }
  .order(:order)

it works.

My environment

• Ruby version: 3.1.2
• OS: ruby:3.1.2-slim-buster

[flash-gordon]

I wouldn't say it's a bug because filter_type_ids is a fallback identifier from Sequel rather than a ROM attribute. You should use where { self[:filter_type_ids].is(...) | ... }

[wuarmin]

hello @flash-gordon , thanks, that explains the problem, but if I change the code to:

filter_type_id = "test"
states
  .where { self[:filter_type_ids].any(filter_type_id) | self[:filter_type_ids].is(nil) }
  .order(:order)

I get following error:

     Failure/Error:
       states
         .where { self[:filter_type_ids].any(filter_type_id) | self[:filter_type_ids].is(nil) }
         .order(:order).to_a
     
     NameError:
       wrong constant name []
     
               types.const_get(type_name) if types.const_defined?(type_name)

[flash-gordon]

@wuarmin sorry for the confusion, I think the truth is in the middle: there's in, there's no any in rom-sql API, that's why it falls back to Sequel. You should use

.where { filter_type_ids.is(nil) | filter_type_ids.in(*filter_type_id) }

There's no difference between col in (1, 2, 3) and col = any(array[1, 2, 3]) as far as I know.

[wuarmin]

Yes, there's no difference, but I cannot write it that way:

.where { filter_type_ids.is(nil) | filter_type_ids.in(*filter_type_id) }

produces

OR ("states"."filter_type_ids" IN ARRAY['test']::text[])

which is invalid and not what I need, I need

('test' = ANY("states"."filter_type_ids"))

What about adding any to rom-sql API?

[flash-gordon]

which is invalid and not what I need, I need

I'm just saying it should have no difference at the database level. You can use inline SQL with backticks:

where { `filter_type_ids is null or filter_type_ids = any(array([#{filter_type_id.map { "'#{_1}'" }.join(',') }]))`  }

A PR with adding ANY is welcome.