sql - active - ¿Cómo usar CUALQUIER lugar en lugar de IN en una cláusula WHERE con Rails?

active record rails

Question

Solía ​​tener una consulta como:

MyModel.where(id: ids)

Lo que genera una consulta sql como:

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

Ahora quiero cambiar esto para usar ANY lugar de IN . Creé esto:

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

Ahora, cuando uso una matriz vacía ids = [] obtengo el siguiente 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''

Answer 1

Hay dos variantes de expresiones IN :

• expression IN (subquery)
• expression IN (value [, ...])

Del mismo modo, dos variantes con ANY construcción:

• expression operator ANY (subquery)
• expression operator ANY (array expression)

Una subconsulta funciona para cualquier técnica, pero para la segunda forma de cada una, IN espera una lista de valores (como se define en el SQL estándar) mientras que = ANY espera una matriz .

¿Cuál usar?

ANY es una adición posterior más versátil, se puede combinar con cualquier operador binario que devuelva un valor booleano. IN quema a un caso especial de ANY . De hecho, su segunda forma se reescribe internamente:

IN se reescribe con = ANY
NOT IN se reescribe con <> ALL

Verifique el resultado de EXPLAIN para cualquier consulta para ver por usted mismo. Esto prueba dos cosas:

• IN nunca puede ser más rápido que = ANY .
• = ANY no va a ser sustancialmente más rápido.

La elección debe decidirse por lo que es más fácil de proporcionar : una lista de valores o una matriz (posiblemente como una matriz literal, un valor único).

Si los ID que va a pasar provienen de la base de datos de todos modos, es mucho más eficiente seleccionarlos directamente (subconsulta) o integrar la tabla de origen en la consulta con un JOIN (como comentó @mu ).

Para pasar una larga lista de valores de su cliente y obtener el mejor rendimiento , use una matriz, unnest() y únase, o proporciónela como expresión de tabla usando VALUES (como comentó @PinnyM ). Pero tenga en cuenta que un JOIN conserva posibles duplicados en el conjunto / conjunto proporcionado, mientras que IN o = ANY no lo hacen. Más:

• Optimizando una consulta de Postgres con un IN grande

En presencia de valores NULL, NOT IN es a menudo la elección incorrecta y NOT EXISTS sería correcto (y también más rápido):

• Seleccionar filas que no están presentes en otra tabla

Sintaxis para = ANY

Para la expresión de matriz, Postgres acepta:

• un constructor de matriz (la matriz se construye a partir de una lista de valores en el lado de Postgres) de la forma: ARRAY[1,2,3]
• o una matriz literal de la forma ''{1,2,3}'' .

Para evitar conversiones de tipo no válidas, puede emitir explícitamente:

ARRAY[1,2,3]::numeric[]
''{1,2,3}''::bigint[]

Relacionado:

• PostgreSQL: problema al pasar la matriz al procedimiento
• Cómo pasar una matriz de tipo personalizado a la función Postgres

O podría crear una función Postgres tomando un parámetro VARIADIC , que toma argumentos individuales y forma una matriz a partir de ellos:

• Pasar múltiples valores en un solo parámetro

¿Cómo pasar la matriz de Ruby?

Suponiendo que id sea integer :

MyModel.where(''id = ANY(ARRAY[?]::int[])'', ids.map { |i| i})

Pero solo estoy incursionando en Ruby. @mu proporciona instrucciones detalladas en esta respuesta relacionada:

• ¿Enviar matriz de valores a una consulta sql en ruby?