Complex SQL query for messaging app

Question

I'm building a messaging feature in a Django 1.6.2 application using PostgreSQL 9.3.4. On a user's "Messages" home page, I'll show a list of conversations that a user has with other users. Each conversation "tile" or block will show the picture and name of the other user in that conversation, the date the last message in that conversation was sent, and the first 25 characters in that last message. I'll also show a small "reply" icon if the last message was sent by user who is viewing these conversations. I've got my query to the point where I can identify all of the conversations between the viewer and all the other users but I'm having trouble pulling in the fields I need from the User and Message tables.

My tables (shown at the bottom) are User, Message, and Conversation. Although I've implemented my table schemas so that there is a many-to-many relationship between users and conversations, in the beginning I'm going to create my interface so that a user can only send a message to one other user instead of multiple users.

When I run my query on the data shown below, what I'm trying to get back is the conversation and user IDs for users 3, 4, 5 along with their associated usernames, the last message in that conversation, who sent it, and the date it was sent. Instead, I'm getting the error:

ERROR: syntax error at or near "WHERE"

Can anyone help me fix this query? I'm more interested in speed than elegance.

Test case

Data in conversation_user linking table:

 id | conversation_id | user_id 
----+-----------------+---------
  1 |               1 |      32
  2 |               1 |       3   <- want this
  3 |               2 |      32
  4 |               2 |       4   <- want this
  6 |               3 |       3
  7 |               3 |       1
  8 |               4 |      32
  9 |               4 |       5   <- want this
 10 |               5 |       7
 11 |               5 |       9

Rows I want to return. Each message is last message in that conversation.

conversation_id | user_id | username  | from_user | message | send_date
----------------+---------+-----------+-----------+---------+----------
 1              | 3       | user3     | u3 or u32 | <msg3>  | <date>
 2              | 4       | user4     | u4 or u32 | <msg4>  | <date>
 4              | 5       | user5     | u5 or u32 | <msg5>  | <date>

Query that isn't working:

SELECT cu.conversation_id,
       cu.user_id,
       au.username,
       m.from_user,
       m.message,
       m.send_date
FROM conversation_user cu
INNER JOIN auth_user au ON cu.user_id = au.id
INNER JOIN message m ON cu.conversation_id = m.conversation_id
ORDER BY m.send_date DESC LIMIT 1
WHERE conversation_id IN
    (SELECT conversation_id
     FROM conversation_user
     WHERE user_id = 32)
  AND user_id != 32;

Table definitions

# auth_user
--------------+--------------------------+------------------------------
 id           | integer                  | not null default nextval(...
 username     | character varying(30)    | not null
Referenced by:
    TABLE "conversation_user" CONSTRAINT "conversation_user_user_id_fkey" FOREIGN KEY (user_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "message" CONSTRAINT "message_from_user_id_fkey" FOREIGN KEY (from_user_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED

# conversation
------------+--------------------------+--------------------------------
 id         | integer                  | not null default nextval(...
 start_date | timestamp with time zone | not null
Referenced by:
    TABLE "conversation_user" CONSTRAINT "conversation_id_refs_id_4344ca71" FOREIGN KEY (conversation_id) REFERENCES conversation(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "message" CONSTRAINT "message_conversation_id_fkey" FOREIGN KEY (conversation_id) REFERENCES conversation(id) DEFERRABLE INITIALLY DEFERRED

# conversation_user
-----------------+---------+--------------------------------------------
 id              | integer | not null default nextval(...
 conversation_id | integer | not null
 user_id         | integer | not null
Foreign-key constraints:
    "conversation_id_refs_id_4344ca71" FOREIGN KEY (conversation_id) REFERENCES conversation(id) DEFERRABLE INITIALLY DEFERRED
    "conversation_user_user_id_fkey" FOREIGN KEY (user_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED

# message
     Column      |           Type           |
-----------------+--------------------------+---------------------------
 id              | integer                  | not null default nextval(...
 conversation_id | integer                  | not null
 from_user_id    | integer                  | not null
 to_user_uid     | integer                  | not null
 message         | text                     | not null
 send_date       | timestamp with time zone | not null
Foreign-key constraints:
    "message_conversation_id_fkey" FOREIGN KEY (conversation_id) REFERENCES conversation(id) DEFERRABLE INITIALLY DEFERRED
    "message_from_user_id_fkey" FOREIGN KEY (from_user_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED

Answer 1

Fix syntax

Basically, you just need to move the WHERE condition to its proper place, like @Lamak commented:

SELECT  ...
FROM conversation_user cu
INNER JOIN ...
WHERE conversation_id IN
    (SELECT conversation_id
     FROM conversation_user
     WHERE user_id = 32)
AND user_id != 32
ORDER BY m.send_date DESC
LIMIT 1;

Make it fast

According to comment:

I'm trying to select the last message in each of the [...] conversations user 32 is having.

SELECT cu.conversation_id
     , ufrom.username AS from_user
     , uto.username   AS to_user
     , m.message
     , m.send_date
FROM   conversation_user cu
LEFT   JOIN LATERAL (
   SELECT from_user_id, to_user_id, message, send_date
   FROM   message   m
   WHERE  m.conversation_id = cu.conversation_id
   ORDER  BY send_date DESC
   LIMIT  1
   ) m ON TRUE
LEFT   JOIN auth_user ufrom ON ufrom.id = m.from_user_id
LEFT   JOIN auth_user uto   ON uto.id = m.to_user_id
WHERE  cu.user_id = 32;

Notes

• A join is typically faster than an IN construct on a subquery, especially with big sets. But you don't need either. You have been overcomplicating things.

• You could have a simpler query with DISTINCT ON, but I expect this one to be faster.
  Details:

  • Select first row in each GROUP BY group?
  • Optimize GROUP BY query to retrieve latest record per user

DB design

• The query assumes that (user_id, conversation_id) is UNIQUE - which you confirmed in the comment. Be sure to add an actual UNIQUE constraint, which provides the much needed index automatically.

• An index on message on (conversation_id, send_date DESC) would help, too. Details:

  • Is a composite index also good for queries on the first field?

• Assuming auth_user.id is the PK, so it would be indexed.

• message.to_user_uid is probably supposed to be to_user_id - like from_user_id.

• You probably want to add another FK to stay consistent:

  "message_to_user_id_fkey" FOREIGN KEY (to_user_id) REFERENCES auth_user(id)
  

  Not sure why you think you need DEFERRABLE INITIALLY DEFERRED. If you don't know you need this, remove it. It's for special purposes and makes regular operations more expensive.

• If only two users can take part in the same conversation, it would be more efficient to remove conversation_user altogether and add user1 and user2 or similar to conversation - unless there are more attributes for each combination of user/conversation. Potentially simplify message, too. You only need a boolean information instead of from_user and to_user.
  According to relational theory, conversation can be seen as a the implementation of many-to-many relationship between table auth_user and itself.