Databases
StackOverflow2013
Postsdbo
POSelect users with I have talked and last message with them like whatsapp
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSelect users with I have talked and last message with them like whatsapp
    primarykey
    data
    text
    <p>I have this table on my database:</p> <p><img src="https://i.stack.imgur.com/8P5hh.png" alt="Message table"></p> <p><code>sentBy</code> and <code>sentTo</code> are FK to <code>User</code> table.</p> <p>On this table I have messages between users:</p> <pre><code>sentBy | sentTo | dateSent | body -------+----------+------------------+----------------- 1 | 2 | 11/21/2010 10:00 | Hey! -------+----------+------------------+----------------- 2 | 1 | 11/21/2010 10:50 | Hi! -------+----------+------------------+----------------- 1 | 2 | 11/21/2010 10:51 | msg body 1 -------+----------+------------------+----------------- 2 | 1 | 11/21/2010 11:05 | msg body 2 -------+----------+------------------+----------------- 1 | 3 | 11/21/2010 11:51 | msg body 3 -------+----------+------------------+----------------- 3 | 1 | 11/21/2010 12:05 | msg body 4 -------+----------+------------------+----------------- 1 | 3 | 11/21/2010 12:16 | msg body 5 -------+----------+------------------+----------------- 4 | 1 | 11/21/2010 12:25 | msg body 6 -------+----------+------------------+----------------- </code></pre> <p>I need to know the users with whom user 1 has talked <strong>and users that have talked with user 1</strong>. In this case, with users 2, 3 and 4 (note that user 4 has sent a message to user 1, but user 1 hasn't sent any message yet).</p> <p>And the second question is: <strong>how can I get the last message with each user?</strong> I'm asking about to get the latest message sent to a user.</p> <p>For example, if I'm asking about user 1, the latest message with user 2 is: msg body 2. And the latest message with user 3 is msg body 5.</p> <p><strong>How can I get that info in one SQL SELECT statement?</strong> Or maybe I will need two selects.</p> <p>I'm trying to do something like <strong>WhatsApp</strong>. Where you have a chats screen with a list of users with whom I have talked (my first question), and the last message with them (my second question).</p> <p>Maybe I can create another table named <code>Conversation</code>, move <code>sentBy</code> and <code>sentTo</code> to that table, and also last message with the date sent to it, but I think this can't be a good design.</p> <p>The result for my two question is this:</p> <pre><code>sentBy | sentTo | dateSent | body -------+----------+------------------+----------------- 2 | 1 | 11/21/2010 11:05 | msg body 2 -------+----------+------------------+----------------- 1 | 3 | 11/21/2010 12:16 | msg body 5 -------+----------+------------------+----------------- 4 | 1 | 11/21/2010 12:25 | msg body 6 -------+----------+------------------+----------------- </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USVansFannel
    1. USVansFannel
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSelect users with I have talked and last message with them like whatsapp
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSelect users with I have talked and last message with them like whatsapp
      1. USKay Nelson
      1. VTFavorite
    1. CORight you are about the latter -- That wouldn't be good design at all. However, I think it would help if you provided a sample output of your example data. I'm a little confused about the way to determine who has talked to whom and what message would be considered the last one.
      singulars
      1. POSelect users with I have talked and last message with them like whatsapp
      1. USgeomagas
    2. COThe last message is the last message received (you can know that using dateSent). I think it is well explained with examples showing what I need.
      singulars
      1. POSelect users with I have talked and last message with them like whatsapp
      1. USVansFannel
    3. COSo for example, if you're asking about user 1, would you like the result to include the message user 4 sent him? Because, technically, user 1 has never "talked" to user 4 -- only the other way around. In other words, is the last line of your example considered _a valid conversation, in which user 1 has participated?_
      singulars
      1. POSelect users with I have talked and last message with them like whatsapp
      1. USgeomagas
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload