6

I have this table messages;

sender_id    recipient_id
1            2
1            3
1            3
2            1
3            1
2            3

I wish to select rows such that:

  1. Either sender_id or receiver_id = current_user.id.
  2. The other field should be unique.

I.e. I want to select unique from table where sender_id = 2 or recipient_id = 2 and I need this result:

sender_id    recipient_id
2            1
2            3

How to do it?
Why? Because I wish to build a facebook-like inbox in which sent and received messages are aggregated, and this query is the bottleneck so far.

I am using rails 3.2 and Postgres 9.3.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Zuhaib Ali
  • 3,344
  • 3
  • 20
  • 32

2 Answers2

7
SELECT sender_id AS user_id, recipient_id AS other_user_id
FROM   messages
WHERE  sender_id = $current_user_id

UNION
SELECT recipient_id, sender_id
FROM   messages
WHERE  recipient_id = $current_user_id
-- ORDER BY 1, 2  -- optional

UNION (not UNION ALL) removes duplicates from the result makingDISTINCT unnecessary. You might want to add ORDER BY at the end for sorted output.

Assuming a big table with relatively few qualifying rows, two btree indexes typically deliver best performance. One with leading or only sender_id, another one with leading or only recipient_id.

A single multicolumn index on (sender_id, receiver_id) or vice versa also works, but typically slower. See:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • @Erwin how do I order these results to get the most recent first? The table also has an id column, and a created_at column both of which can be used for sorting... but how? – Zuhaib Ali Mar 30 '14 at 17:22
  • @ZuhaibAli: First you would have to define *which* value to pick for each group of duplicates. The smallest `id` / `created_at` per group? I suggest you write a new question with the detailed requirements. You can always refer to this one for context. – Erwin Brandstetter Mar 30 '14 at 18:33
  • Note that I replaced my original answer with a better one. – Erwin Brandstetter Feb 28 '19 at 16:35
3

With ANSI SQL:

SELECT DISTINCT sender_id, reciepient_id
FROM messages
WHERE (sender_id = current_user.id or reciepient_id = current_user.id)
Piroinno
  • 115
  • 1
  • 9