PostgreSQL的，所有的数组 [英] PostgreSQL where all in array
问题描述
什么是实现一个条款，其中阵列中的所有元素都必须匹配的最简单和最快的方式 - 不仅是在使用在？它应该表现毕竟像 MongoDB的$所有。
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.
有关组的谈话，其中conversation_users是一个连接conversation_id和USER_ID之间的桌子思考我有这样的事情记：
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))
更新 12年7月16日
UPDATE 16.07.12
添加有关架构和案例的详细信息：
Adding more info about schema and case:
-
联接表是相当简单：
The join-table is rather simple:
Table "public.conversations_users"
Column | Type | Modifiers | Storage | Description
-----------------+---------+-----------+---------+-------------
conversation_id | integer | | plain |
user_id | integer | | plain |
一个对话有许多用户和用户所属的多次交谈。为了找到我使用这个连接表中的某个会话的所有用户。
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.
在年底，我试图找出在轨红宝石范围的发现是我这取决于它的谈话真实的参与者 - 例如：
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))
}
更新 12年7月23日
我的问题是关于寻找的人完全匹配。因此：
My question is about finding an exact match of people. Therefore:
与对话（1,2,3）不会，如果在查询匹配（1,2）
推荐答案
假设连接表如下好的做法和定义了一个独特的复合键，即制约prevent重复的行，那么类似如下的简单查询应该做的。
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
重要的是要注意，数字2在末端是user_ids的列表的长度。这显然需要如果USER_ID列表更改长度改变。如果你不能假设你的连接表不包含重复，变COUNT（*），以伯爵（不同的user_id）在性能上的一些可能的成本。
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.
如果您希望只与对话的完全用户指定的集合，一种方法是使用嵌套子查询，如下where子句。注意，第一和最后行中的相同的原始查询，只有中间的两行是新的。
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 conversation_users where user_id not in (1,2))
group by conversation_id having count(*) = 2
等价地，你可以使用一组不同的运营商，如果你的数据库支持。这是Oracle语法的一个例子。 （对于Postgres而言，或DB2，更改关键字减的除外。）
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 conversation_users where user_id not in (1,2)
有一个良好的查询优化器的应的治疗在过去两个版本完全相同，但请与您的特定数据库以确保万无一失。例如，在Oracle 11gR2的查询计划排序两组对话ID的应用减号来过，但跳过最后一个查询的排序步骤。所以无论是查询计划可能会更快取决于多种因素，如行，内核，缓存，索引等的数量。
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的，所有的数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
