insert into postgresql ejemplo

PostgreSQL donde todo en conjunto

Question

¿Cuál es la forma más fácil y rápida de lograr una cláusula donde todos los elementos de una matriz deben coincidir, no solo uno cuando se utiliza IN ? Después de todo, debería comportarse como $ all de mongodb .

Pensando en las conversaciones grupales donde conversation_users es una tabla de unión entre conversation_id y user_id, tengo algo como esto en mente:

WHERE (conversations_users.user_id ALL IN (1,2))

ACTUALIZACIÓN 16.07.12

Agregar más información sobre el esquema y el caso:

1. La tabla de unión es bastante simple:

    Table "public.conversations_users"
   Column | Type | Modifiers | Storage | Description
   -----------------+---------+-----------+---------+-------------
   conversation_id | integer | | plain |
   user_id | integer | | plain |

2. Una conversación tiene muchos usuarios y un usuario pertenece a muchas conversaciones. Para encontrar a todos los usuarios en una conversación, estoy usando esta tabla de unión.

3. Al final, estoy tratando de descubrir un scope Ruby on Rails que me encuentre una conversación dependiendo de sus participantes, por ejemplo:

   scope :between, ->(*users) {
   joins(:users).where(''conversations_users.user_id all in (?)'', users.map(&:id))
   }

ACTUALIZACIÓN 23.07.12

Mi pregunta es sobre encontrar una coincidencia exacta de personas. Por lo tanto:

La conversación entre (1,2,3) no coincidirá si se solicita (1,2)

Answer 1

Esto preserva los objetos ActiveRecord .

En el siguiente ejemplo, quiero saber las hojas de tiempo que están asociadas con todos los códigos en la matriz.

codes = [8,9]
Timesheet.joins(:codes).select(''count(*) as count, timesheets.*'').
where(''codes.id'': codes).
group(''timesheets.id'').
having(''count(*) = ?'', codes.length)

Debería tener los objetos ActiveRecord para trabajar. Si quiere que sea un verdadero ámbito, puede simplemente usar su ejemplo anterior y pasar los resultados con .pluck(:id) .

Answer 2

Estoy colapsando a esos usuarios en una matriz. También estoy usando un CTE (lo que está en la cláusula WITH) para hacer esto más legible.

=> select * from conversations_users ;
conversation_id | user_id
-----------------+---------
1 | 1
1 | 2
2 | 1
2 | 3
3 | 1
3 | 2
(6 rows)
=> WITH users_on_conversation AS (
SELECT conversation_id, array_agg(user_id) as users
FROM conversations_users
WHERE user_id in (1, 2) --filter here for performance
GROUP BY conversation_id
)
SELECT * FROM users_on_conversation
WHERE users @> array[1, 2];
conversation_id | users
-----------------+-------
1 | {1,2}
3 | {1,2}
(2 rows)

EDITAR (Algunos recursos)

• funciones de matriz: http://www.postgresql.org/docs/9.1/static/functions-array.html
• CTE: http://www.postgresql.org/docs/9.1/static/queries-with.html

Answer 3

Según la respuesta de @Alex Blakemore, el alcance equivalente de Rails 4 en su clase de Conversation sería:

# Conversations exactly with users array
scope :by_users, -> (users) {
self.by_any_of_users(users)
.group("conversations.id")
.having("COUNT(*) = ?", users.length) -
joins(:conversations_users)
.where("conversations_users.user_id NOT IN (?)", users)
}
# generates an IN clause
scope :by_any_of_users, -> (users) { joins(:conversations_users).where(conversations_users: { user_id: users }).distinct }

Tenga en cuenta que puede optimizarlo en lugar de hacer un Rails - (menos) podría hacer un .where("NOT IN") pero sería muy complejo de leer.

Answer 4

Si bien la respuesta @Alex ''con IN y count() es probablemente la solución más simple, espero que esta función PL / pgSQL sea la más rápida:

CREATE OR REPLACE FUNCTION f_conversations_among_users(_user_arr int[])
RETURNS SETOF conversations AS
$BODY$
DECLARE
_sql text := ''
SELECT c.*
FROM conversations c'';
i int;
BEGIN
FOREACH i IN ARRAY _user_arr LOOP
_sql := _sql || ''
JOIN conversations_users x'' || i || '' USING (conversation_id)'';
END LOOP;
_sql := _sql || ''
WHERE TRUE'';
FOREACH i IN ARRAY _user_arr LOOP
_sql := _sql || ''
AND x'' || i || ''.user_id = '' || i;
END LOOP;
/* uncomment for conversations with exact list of users and no more
_sql := _sql || ''
AND NOT EXISTS (
SELECT 1
FROM conversations_users u
WHERE u.conversation_id = c.conversation_id
AND u.user_id <> ALL (_user_arr)
)
*/
-- RAISE NOTICE ''%'', _sql;
RETURN QUERY EXECUTE _sql;
END;
$BODY$ LANGUAGE plpgsql VOLATILE;

Llamada:

SELECT * FROM f_conversations_among_users(''{1,2}'')

La función crea dinámicamente ejecuta una consulta del formulario:

SELECT c.*
FROM conversations c
JOIN conversations_users x1 USING (conversation_id)
JOIN conversations_users x2 USING (conversation_id)
...
WHERE TRUE
AND x1.user_id = 1
AND x2.user_id = 2
...

Esta forma se desempeñó mejor en una extensa prueba de consultas para la división relacional .

También puede crear la consulta en su aplicación, pero asumí que desea usar un parámetro de matriz. Además, este es probablemente el más rápido de todos modos.

Cualquiera de las consultas requiere un índice como el siguiente para ser rápido:

CREATE INDEX conversations_users_user_id_idx ON conversations_users (user_id);

Una clave primaria de varias columnas (o única) en (user_id, conversation_id) es igual de buena, pero una en (conversation_id, user_id) (como bien puede haberlo hecho) sería inferior . Encontrará una explicación breve en el enlace anterior, o una evaluación exhaustiva bajo esta pregunta relacionada en dba.SE

También asumo que tienes una clave principal en conversations.conversation_id .

¿Puede ejecutar una prueba de rendimiento con EXPLAIN ANALYZE en la consulta @Alex ''y esta función e informar sus conclusiones?

Tenga en cuenta que ambas soluciones encuentran conversaciones en las que participan al menos los usuarios de la matriz, incluidas las conversaciones con usuarios adicionales.
Si desea excluirlos, elimine el comentario de la cláusula adicional en mi función (o agréguela a cualquier otra consulta).

Dime si necesitas más explicación sobre las características de la función.

Answer 5

Supongo que realmente no quieres comenzar a jugar con tablas temporales.

Su pregunta no estaba clara en cuanto a si desea conversaciones con exactamente el conjunto de usuarios o conversaciones con un superconjunto. Lo siguiente es para el superconjunto:

with users as (select user_id from users where user_id in (<list>)
),
conv as (select conversation_id, user_id
from conversations_users
where user_id in (<list>)
)
select distinct conversation_id
from users u left outer join
conv c
on u.user_id = c.user_id
where c.conversation_id is not null

Para que esta consulta funcione bien, supone que tiene índices en user_id en usuarios y conversaciones_usuarios.

Para el conjunto exacto. . .

with users as (select user_id from users where user_id in (<list>)
),
conv as (select conversation_id, user_id
from conversations_users
where user_id in (<list>)
)
select distinct conversation_id
from users u full outer join
conv c
on u.user_id = c.user_id
where c.conversation_id is not null and u.user_id is not null

Answer 6

Suponiendo que la tabla de unión sigue las buenas prácticas y tiene definida una única clave compuesta, es decir, una restricción para evitar filas duplicadas, algo como la siguiente consulta simple debería hacer.

select conversation_id from conversations_users where user_id in (1, 2)
group by conversation_id having count(*) = 2

Es importante tener en cuenta que el número 2 al final es la longitud de la lista de user_ids. Obviamente, esto debe cambiar si la lista user_id cambia de longitud. Si no puede suponer que su tabla de unión no contiene duplicados, cambie "count (*)" por "count (distinct user_id)" con algún posible costo de rendimiento.

Esta consulta busca todas las conversaciones que incluyen a todos los usuarios especificados, incluso si la conversación también incluye usuarios adicionales.

Si solo desea conversaciones con el conjunto de usuarios especificado, un enfoque es utilizar una subconsulta anidada en la cláusula where de la siguiente manera. Tenga en cuenta que la primera y la última línea son las mismas que la consulta original, solo que las dos líneas medias son nuevas.

select conversation_id from conversations_users where user_id in (1, 2)
and conversation_id not in
(select conversation_id from conversation_users where user_id not in (1,2))
group by conversation_id having count(*) = 2

De manera equivalente, puede usar un operador de diferencia de conjuntos si su base de datos lo admite. Aquí hay un ejemplo en la sintaxis de Oracle. (Para Postgres o DB2, cambie la palabra clave "menos" por "excepto").

select conversation_id from conversations_users where user_id in (1, 2)
group by conversation_id having count(*) = 2
minus
select conversation_id from conversation_users where user_id not in (1,2)

Un buen optimizador de consultas debería tratar las dos últimas variaciones de forma idéntica, pero consulte con su base de datos particular para estar seguro. Por ejemplo, el plan de consulta de Oracle 11GR2 ordena los dos conjuntos de identificadores de conversación antes de aplicar el operador menos, pero omite el paso de clasificación para la última consulta. Entonces, cualquier plan de consulta podría ser más rápido dependiendo de múltiples factores, como el número de filas, núcleos, caché, índices, etc.

Answer 7

crea una tabla de mapeo con todos los valores posibles y usa esto

select
t1.col from conversations_users as t1
inner join mapping_table as map on t1.user_id=map.user_id
group by
t1.col
having
count(distinct conversations_users.user_id)=
(select count(distinct user_id) from mapping)

Answer 8

select id from conversations where not exists(
select * from conversations_users cu
where cu.conversation_id=conversations.id
and cu.user_id not in(1,2,3)
)

esto se puede convertir fácilmente en un alcance de rieles.