Create an inbox message type of query

Question

I've just started learning SQL, so I apologies if this question is a bit of a dummy question.

I have two tables:

1. direct_messages
2. users

A direct_messages table looks like this:

receiverId and senderId are foreign keys to my users table.

What I'm trying to achieve is a typical inbox message query. Meaning get one message of each message sent that has the same receiverId as the logged in user and also get the latest one.

I've tried to get this working almost the whole day and I've come this "far":

SELECT *
FROM "direct_messages" tm
LEFT JOIN "users" tu ON "tm.senderId" = "tu.id"
WHERE "tm.createdAt"
ORDER BY "created_at" DESC
LIMIT 1;

But I'm just getting ERROR: column "tm.senderId" does not exist.

All the help I can get is super appreciated. Thank you!

EDIT: As requested, I added some example data: https://gist.github.com/Martinnord/f07bc62389f2ecd0df8e6716dd797a15

What I want is to get a list of messages that has a particular receiverId, but only one from each unique senderId and get the latest one. Like a typical message inbox.

Answer 1

Double quoting everything in Postgresql may be considered harmful because of how double quotes work. They should only be used when there's likely to be confusion with a keyword.

"tm.senderId" does not mean senderId in the tm alias. Because the . is quoted it means exactly the identifier tm.senderId. Postgres is looking for a column tm.senderId in the table direct_messages and not finding it.

Instead you'd write "tm"."senderId" careful to quote the table/alias and column separately.

This leads to the next pitfall, casing. Unless they're quoted, Postgres will lower case columns and table names for you.

test=# create table direct_messages ( senderId integer );
CREATE TABLE
test=# \d direct_messages 
           Table "public.direct_messages"
  Column  |  Type   | Collation | Nullable | Default 
----------+---------+-----------+----------+---------
 senderid | integer |           |          | 

(Note even the table description "public.direct_messages" is misleading.)

And it will do the same to unquoted tables and columns in queries.

test=# select * from direct_messages tm where tm.sEnDerID is not null;
 senderid 
----------
(0 rows)

But if you quote them, it will look for an exact match.

test=# select * from direct_messages tm where tm."sEnDerID" is not null;
ERROR:  column tm.sEnDerID does not exist
LINE 1: select * from direct_messages tm where tm."sEnDerID" is not ...

test=# select * from direct_messages tm where tm."senderId" is not null;
ERROR:  column tm.senderId does not exist
LINE 1: select * from direct_messages tm where tm."senderId" is not ...
                                               ^
HINT:  Perhaps you meant to reference the column "tm.senderid".

test=# select * from direct_messages tm where tm."senderid" is not null;
 senderid 
----------
(0 rows)

There's no ambiguous column names in your query, so remove the double quotes.

SELECT *
FROM direct_messages tm
LEFT JOIN users tu ON tm.senderId = tu.id
WHERE tm.createdAt
ORDER BY created_at DESC
LIMIT 1;

(Side note: it's confusing to mix camelCase createdAt and snake_case created_at. Pick one style for the project and stick with it.)

Answer 2

As some people mentioned in the comments you have a problem with your double quotes and they explained how you should handle that.
I will show you how to solve the sql problem that you are having. I will create a sample messages table.

CREATE TABLE messages (
  id BIGSERIAL PRIMARY KEY ,
  sender_id BIGINT ,
  reciever_id BIGINT,
  message TEXT,
  sent_at TIMESTAMP
);

Now lets fill that table with some data:

INSERT INTO messages (sender_id, reciever_id, message, sent_at) VALUES
  (1,3,'User 1 to user 3 1 hour ago', '2017-07-10 15:00:00'),
  (1,3,'User 1 to user 3 last','2017-07-10 16:00:00'),
  (2,3,'User 2 to user 3 2 hours ago','2017-07-10 14:00:00'),
  (2,3,'User 2 to user 3 last','2017-07-10 16:00:00');

Now, lets say that we want to find all of the last mesasges (by sender_id) that were sent to the user with id 3.

So based on the input data, the result should return 2 messages:

1. From user 1 to user 3 with value "User 1 to user 3 last"
2. From user 2 to user 3 with value "User 2 to user 3 last"

That can be achieved with the following query:

WITH sender_last_message_time AS (
SELECT sender_id, MAX(sent_at) sent_at FROM messages
WHERE reciever_id = 3
GROUP BY sender_id )
SELECT * FROM messages m
JOIN sender_last_message_time r ON m.sender_id = r.sender_id AND r.sent_at = m.sent_at;

Explanation:

I create the sender_last_message_time CTE that holds the last message time (sent by sender_id) for each sender. Once I have sender_id and the time of the last message for that sender_id, I just join the data with the messages table in order to fetch the messages.