sql - Retrieving a user's conversations together with last sent and received timestamps -

I need to get a list of all users that a particular user has with the message of the last message timestamp Exchanges have been sent and the timestamp of the last message has been received. I want to do this in a single query.

The result will be something like this:

  [[0] {: uname => "Fred" ,: Last_Fed => 2013-04-09 22:47:20 UTC, final_centrate => 2013-04-09 22:47:28 UTC}, [1] {: uname => "Barney" ,: Last_Food => Zero,: last_sent = & gt; 2013-06-16 16:25:56 UTC}, [2] {: uname => "Dino" ,: Last_Food => 2013-06-09 17:52:54 UTC, final_centrate => 2013-06-10 15:56:52 UTC}]   
 is a simplified schema:  
  create tab user (not id serial, not the only text) NULL, without time zone default time zone ('UTC' :: text, now ()), create table messages (id serial NULL SENDER_ID not integer NULL recipient_id integer NULL message_text_id integer, created_at timestamp without time zone Created_at timestamp default timezone ('UTC' :: text, now ()))   
 I have a query that does this, but since it connects with users, I worry that it gets slower as the number of users increases - I do not know whether PostgreSQL To avoid being optimized,  
 with  as SE (SENDER_ID, recipient_id, max (created_at) LATEST_DATE as the selection. SENDER_ID = SENDER_ID = # {ID} or recipient_id = # {ID} by group SENDER_ID, recipient_id) by uname selection, t1.latest_date last_r Eceived, t2.latest_date last_sent users left t1.sender_id = users.id and t1 to join t t1 .endender_id! = # {I d} left t2.recipient_id = joining t2 on users.id and t2.recipient_id! = # {ID} where t1.latest_date is not empty or t2.latest_date is not empty   
 I would be interested in knowing whether postgresql would optimize it, and better to do the same query See also the ways.  
 Thanks Mark   
 
 
 You can try something with the following lines - on Try to use  explain  to look which looks the best.  
  select u.uname, maximum (x.last_received) last_received, maximum (x.last_sent) from (SENDER_ID select user_id last_sent, maximum (created_at) last_received, last messages from null messages last_sent Where recipient_id = # {ID} selects recipient_id unions group by all recipient_id user_id, last_received null, last_sent with maximum (created_at) messages where SENDER_ID = # {id} SENDER_ID group) x users join u.user_id = u .user_id