jeudi 2 juillet 2015

How to use 'ANY' in where clause with Rails 4.0 instead of 'IN'?

I used to have a query like :

MyModel.where(id: ids)

Which generates sql query like:

SELECT  "my_models".* FROM "my_models"  WHERE "my_models"."id" IN (1, 28, 7, 8, 12)

Now I want to change this to use ANY instead of IN

I created this:

MyModel.where("id = ANY(VALUES(#{ids.join '),('}))"

Now when I use empty array ids = [] I get the foloowing error:

  MyModel Load (53.0ms)  SELECT "my_models".* FROM "my_models"  WHERE (id = ANY(VALUES()))
ActiveRecord::JDBCError: org.postgresql.util.PSQLException: ERROR: syntax error at or near ")"
ActiveRecord::StatementInvalid: ActiveRecord::JDBCError: org.postgresql.util.PSQLException: ERROR: syntax error at or near ")"
  Position: 75: SELECT "social_messages".* FROM "social_messages"  WHERE (id = ANY(VALUES()))
    from arjdbc/jdbc/RubyJdbcConnection.java:838:in `execute_query'

Aucun commentaire:

Enregistrer un commentaire