PostgreSQL where all in array

user_231150 0
Viewed 24783

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)

relational-division postgresql activerecord ruby-on-rails sql
9 Answers

Based on Alex Blakemore answer

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

I have found an alternative query with the same goal, finding the conversation_id of a conversation that contains user_1 and user_2 (ignoring aditional users)

select *
from conversations_users cu1
where 2 = (
    select count(distinct user_id)
    from conversations_users cu2
    where user_id in (1, 2) and cu1.conversation_id = cu2.conversation_id
)

It is slower according the analysis that postgres perform via explain query statement, and i guess that is true because there is more conditions beign evaluated, at least, for each row of the conversations_users the subquery will get executed as it is correlated subquery. The possitive point with this query is that you aren't grouping, thus you can select aditional fields of the conversations_users table. In some situations (like mine) it could be handy.

user_9039980
Related