title: Postgres SQL array aggregate values from a single table based on conditions
body: <p>I'm a SQL newbie and I'm trying to write the following query:</p>
  <p>I have the following table:</p>
  <pre><code> user_id | chat_id 
  ---------+---------
  </code></pre>
  <p>Which represents a many to many mapping of users to chat rooms;</p>
  <p>I'm looking to create a query that finds all the <code>chat_id</code>s that are associated with the input <code>user_id</code>, and then array aggregates all the <code>user_id</code>s associated with those chats excluding the input <code>user_id</code>.</p>
  <p>So the result should look like this for example:</p>
  <pre><code> chat_id | user_id 
  ---------+---------
      1     {1,3,5,6}
  </code></pre>
  <p>I've kind of jumbled together to following query; but I'm pretty sure I got something wrong:</p>
  <pre class="lang-sql prettyprint-override"><code>WITH chatIDs AS (SELECT user_chats.chat_id FROM user_chats WHERE user_chats.user_id=$1)
  WITH userIDs AS (SELECT user_chats.user_id FROM user_chats WHERE user_chats.chat_id=chatIDs AND user_chats.user_id != $1)
  SELECT chatIDs, array_agg(userIDs) FROM user_chats;
  </code></pre>
  <p>EDIT:</p>
  <p>Edited for clarity</p>