Databases
StackOverflow2013
Postsdbo
POMessaging system query to get last messages, number of unread messages and array of users in conversation
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMessaging system query to get last messages, number of unread messages and array of users in conversation
    primarykey
    data
    text
    <p>I'm working on a messaging system with two tables and another table with the users information.<br> A conversation can be between 2 or more users. Every conversation has a UID and every messages exchanged between users are labeled with that conversation UID. </p> <p>Here are the tables :</p> <p><code>conversation_list</code> : every row in this table links the <code>user_id</code> and the <code>conversation_id</code>, it also contains the last time the user viewed the conversation.</p> <pre><code>`id` -&gt; unique ID, autoincremented `user_id` -&gt; This contains the user associated with the conversation. `conversation_id` -&gt; This contains the UID of the conversation `date_lastView` -&gt; This field has the time that the user viewed the conversation last </code></pre> <p><code>conversation_messages</code> : every row in this table contains a message </p> <pre><code>`id` -&gt; unique ID, autoincremented `user_id` -&gt; This contains the user that sent the message. `conversation_id` -&gt; This contains the UID of the conversation `date_created` -&gt; This contains the time when the message was posted `message` -&gt; This contains the message </code></pre> <p><code>users</code> : every row in this table contains a user </p> <pre><code>`User_ID` -&gt; UID of the user `FirstName` -&gt; This contains the first name of the user `LastName` -&gt; This contains the last name of the user </code></pre> <p>I already have a SQL query to get the last message of every conversation. Here it is : </p> <pre><code>SELECT * FROM conversation_messages AS m JOIN (SELECT mx.conversation_id, MAX(mx.date_created) AS MaxTime FROM conversation_messages AS mx GROUP BY mx.conversation_id) AS mx ON m.conversation_id = mx.conversation_id AND m.date_created = mx.MaxTime JOIN (SELECT mu.conversation_id FROM conversation_list AS mu WHERE mu.user_id = :USER_ID_CONNECTED GROUP BY mu.conversation_id) AS mux ON m.conversation_id = mux.conversation_id JOIN conversation_list AS mu ON m.conversation_id = mu.conversation_id GROUP BY mu.conversation_id ORDER BY m.date_created DESC </code></pre> <p>I now would like to add to this perfectly working query the ability to return:</p> <ul> <li>The number of unread messages for each conversation (count of all messages with the <code>date_creaded</code> bigger then <code>date_lastView</code> of the logged in user)</li> <li>An array containing the <code>User_ID</code> of every user in each conversation and sorted by when they last posted a message in the conversation.</li> <li>With the same idea of the last array but with the <code>FirstName</code> and <code>LastName</code> of the user.</li> </ul> <p>I tried a few things but I was really unsuccessful, so I'm now asking the SO community for its precious help.</p> <p>All this can only display conversations where the logged in user takes part in.</p> <p>It it helps, I created a <a href="http://sqlfiddle.com/#!2/c7306/2" rel="noreferrer">SQLFiddle</a></p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USBen
    1. USJoris Blanc
    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
    1. VO
      singulars
      1. POMessaging system query to get last messages, number of unread messages and array of users in conversation
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMessaging system query to get last messages, number of unread messages and array of users in conversation
      1. USace33022
      1. VTFavorite
    3. VO
      singulars
      1. POMessaging system query to get last messages, number of unread messages and array of users in conversation
      1. This table or related slice is empty.
      1. VTUpMod
    1. COA well written question +1.
      singulars
      1. POMessaging system query to get last messages, number of unread messages and array of users in conversation
      1. USBen
    2. COAnother +1 for the fiddle.
      singulars
      1. POMessaging system query to get last messages, number of unread messages and array of users in conversation
      1. USJStead
 

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