Postgresql inbox query

I would suggest taking couling's answer and slightly modifying it so that it does provide effectively two queries using a common table expression:

WITH threads (sender_id, receiver_id, latest) as (
        select sender, 
               receiver,
               max(sent) 
          from sof_messages
         where receiver = <user>
            or sender = <user>
         group by sender,
               receiver
         order by 3
         limit 10
 ), 
 messages ([messages fields listed here], rank) as (
         select m.*, 
                rank() over (partition by (sender, receiver), order by sent desc)
           from sof_messages
          WHERE (sender, receiver) in (select (sender, receiver) from threads))
 SELECT * from messages where rank <= 10;

This has the advantage of allowing the planner to have a pretty good idea of when to use indexes here. In essence each of the three pieces to the query is independently planned.

I'm posting this to show what can be done.

I do not really recommend using it.

It would be much better to do two separate queries: 1 to retrieve the 10 most recent threads and 1 repeated to pull back the 10 most recent messages for each thread.

However you may achieve your goal with the rank() window function as shown below.

select * from (
      select message.*,
             rank() over (partition by message.sender, message.receiver 
                              order by sent desc )  
      from sof_messages message,
           (
            select sender, 
                   receiver,
                   max(sent) 
              from sof_messages
             where receiver = <user>
                or sender = <user>
             group by sender,
                   receiver
             order by 3
             limit 10
           ) thread
      where message.sender = thread.sender
        and message.receiver = thread.receiver
      ) message_list

where rank <= 10

There are a couple of different queries which will achieve your goal with window functions, none of them particularly clean.

Creating a Thread table looks wrong because of data duplication, but a view may help:

CREATE VIEW threads AS 
  SELECT sender_id, receiver_id, min(created_at) AS t_date
  FROM messages
  GROUP BY sender_id,receiver_id;

Change min(created_at) to max(created_at) if the thread's date is to be the date of its most recent message, rather than the oldest one.

Then it can be joined back to messages simply with:

SELECT ... FROM messages JOIN threads USING (sender_id,receiver_id)

The tidiest query I could imagine to resolve your problem within one query is the following one:

select * from (
  select row_number() 
    over (partition by sender_id, receiver_id order by created_at desc) as rn, m.*
  from Messages m
  where (m.sender_id, m.receiver_id) in (
    select sender_id, receiver_id
    from Messages
    where sender_id = <id> or receiver_id = <id>
    group by sender_id, receiver_id
    order by max(created_at) desc
    limit 10 offset 0
  )
) res where res.rn <= 10

The row_number() over (partition by sender_id, receiver_id order by created_at desc) column will contain the row number of each message within each thread (it will be like the record number if you run a separate query to query just for one thread). Apart from this row number you query the message itself if it is contained in the 10 topmost threads (that is made by that (m.sender_id, m.receiver_id) in ...query.... And finally as you want just 10 topmost messages you limit the row number to be lower or equal to 10.

I haven't tested this, but it looks like you forgot the LIMIT 10 on your subquery which gives you the 10 most-recent threads:

SELECT
  *
FROM
  (SELECT DISTINCT ON
     (sender_id, receiver_id) messages.* 
   FROM
     messages 
   WHERE
     (receiver_id = 5 OR sender_id = 5)
   ORDER BY
     sender_id, receiver_id, created_at DESC
   LIMIT
     10)   
  q
ORDER BY
  created_at DESC 
LIMIT
  10
OFFSET
  0;

(I've pretty-printed the SQL so it is easier to tell what is going on.)