PostgreSQL where all in array [英] PostgreSQL where all in array

问题描述

实现一个必须匹配数组中所有元素的子句的最简单和最快的方法是什么 - 而不仅仅是使用 IN 时的一个?毕竟它应该表现得像 mongodb 的 $all.>

考虑到 session_users 是 session_id 和 user_id 之间的连接表的群组对话，我有这样的想法:

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

<小时>

更新 16.07.12

添加有关架构和案例的更多信息:

1. 连接表相当简单:

    表public.conversations_users"专栏 |类型 |修饰符 |存储 |描述-----------------+---------+-----------+---------+-------------对话_id |整数 ||平原 |用户 ID |整数 ||平原 |

2. 一个对话有多个用户，一个用户属于多个对话.为了找到对话中的所有用户，我使用了这个连接表.

3. 最后，我试图找出一个 ruby​​ on rails scope 找到我的对话取决于它的参与者 - 例如:

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

更新 23.07.12

我的问题是关于找到完全匹配的人.因此:

如果查询(1,2)

，(1,2,3)之间的对话将不匹配

解决方案

假设连接表遵循良好的做法并定义了唯一的复合键，即防止重复行的约束，那么应该像下面的简单查询那样做.

从conversations_users中选择conversation_id where user_id in (1, 2)按具有 count(*) = 2 的对话 ID 分组

需要注意的是，最后的数字2是user_ids列表的长度.如果 user_id 列表改变长度，这显然需要改变.如果您不能假设您的连接表不包含重复项，请以一些可能的性能成本将count(*)"更改为count(distinct user_id)".

此查询查找包含所有指定用户的所有对话即使该对话还包括其他用户.

如果您只想与恰好指定的一组用户进行对话，一种方法是在 where 子句中使用嵌套子查询，如下所示.请注意，第一行和最后一行与原始查询相同，只有中间两行是新的.

从conversations_users中选择conversation_id where user_id in (1, 2)和conversation_id不在(从conversations_users 中选择conversation_id，其中user_id 不在(1,2) 中)按具有 count(*) = 2 的对话 ID 分组

同样，如果您的数据库支持，您可以使用集差运算符.这是 Oracle 语法中的示例.(对于 Postgres 或 DB2，将关键字minus"更改为except.)

从conversations_users中选择conversation_id where user_id in (1, 2)按具有 count(*) = 2 的对话 ID 分组减从conversations_users 中选择conversation_id，其中user_id 不在(1,2) 中

一个好的查询优化器应该同样对待最后两个变体，但请检查您的特定数据库以确保.例如，Oracle 11GR2 查询计划在应用减号运算符之前对两组对话 ID 进行排序，但跳过最后一个查询的排序步骤.因此，根据行数、核心数、缓存、索引等多种因素，任一查询计划都可能更快.

What is the easiest and fastest way to achieve a clause where all elements in an array must be matched - not only one when using IN? After all it should behave like mongodb's $all.

Thinking about group conversations where conversation_users is a join table between conversation_id and user_id I have something like this in mind:

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

---

UPDATE 16.07.12

Adding more info about schema and case:

1. The join-table is rather simple:

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

2. A conversation has many users and a user belongs to many conversations. In order to find all users in a conversation I am using this join table.

3. In the end I am trying to figure out a ruby on rails scope that find's me a conversation depending on it's participants - e.g.:

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

UPDATE 23.07.12

My question is about finding an exact match of people. Therefore:

Conversation between (1,2,3) won't match if querying for (1,2)

解决方案

Assuming the join table follows good practice and has a unique compound key defined, i.e. a constraint to prevent duplicate rows, then something like the following simple query should do.

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

It's important to note that the number 2 at the end is the length of the list of user_ids. That obviously needs to change if the user_id list changes length. If you can't assume your join table doesn't contain duplicates, change "count(*)" to "count(distinct user_id)" at some possible cost in performance.

This query finds all conversations that include all the specified users even if the conversation also includes additional users.

If you want only conversations with exactly the specified set of users, one approach is to use a nested subquery in the where clause as below. Note, first and last lines are the same as the original query, only the middle two lines are new.

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

Equivalently, you can use a set difference operator if your database supports it. Here is an example in Oracle syntax. (For Postgres or DB2, change the keyword "minus" to "except.)

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

A good query optimizer should treat the last two variations identically, but check with your particular database to be sure. For example, the Oracle 11GR2 query plan sorts the two sets of conversation ids before applying the minus operator, but skips the sort step for the last query. So either query plan could be faster depending on multiple factors such as the number of rows, cores, cache, indices etc.

这篇关于PostgreSQL where all in array的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！