CODEWITHSUNDEEP
phpmysqlchat
Nikita Patil
Nikita Patil

Reputation: 51

How to get desired details in making a chat?

//This is the conversation table where sender is the one who sends the message and the receiver is the one who receives the message and Context is the foreign key of another table

id  Context    Message          Sender  Receiver       Date
1      3    Hello                 1         3       2019-01-25
2      4    How Are you           3         2       2019-01-26
3      1    Are you there         1         6       2019-01-27
4      1    sample messages       5         1       2019-01-28
5      1    Where are you from    4         2       2019-01-29
6      5    Where are you now     1         3       2019-01-30
7      4    There you are         4         2       2019-01-31
8      5    What languages        5         4       2019-01-25
9      2    What language code    2         1       2019-01-26
10     6    Do you code?          2         5       2019-01-27
11     5    Do you have a car     2         5       2019-01-28
12     5    color of your car.    1         2       2019-01-29
13     5    I have a blue car     3         1       2019-01-30
14     2    Yes I have a car      1         5       2019-01-31
15     1    car maker is honda    5         3       2019-01-25
16     1    I have red color hat. 1         3       2019-01-26
17     6    What colored hat ?    3         2       2019-01-27
18     2    Do you use my sql     2         1       2019-01-28
19     4    Do u code in php      5         1       2019-01-29
20     1    Do you know java      1         2       2019-01-30
21     1    favourite key ?       2         1       2019-01-31
22     5    Mine is spacebar      4         1       2019-02-01


//This is the context table where the title is the title and posted by is the user who has posted this topic. We can connect id to context in the conversation table.


id           title               posted by
1   Lets talk about javascript      2
2   I have a blue car               1
3   I do not have a hat             5
4   How do you do                   2
5   Where do you stay               4
6   Fault in our stars              1


//This is the user table from where we can retrieve information about the user for Sender, Receiver in the conversation table and posted by in the context table.

User    Name    Last Name   Age         Email
1       John    Doe         24      [email protected]
2       Jane    Dow         23      [email protected]
3       Jack    Daniels     41      [email protected]
4       Peter   Parker      29      [email protected]
5       Tony    Stark       39      [email protected]
6       Terri   Benedict    49      [email protected]

Now I want to show a list to logged in user with whom he/she was chatting which will include the topic name, name of the person whom he/she was chatting ,date and the last message .

I have tried ***Assuming the logged in user is 2.

SELECT DISTINCT(context) FROM conversation WHERE (Sender = '2' OR Receiver = '2');

This works in getting me all the conversation and a proper right join also gives me name and all the information howver I want to get the last message in their conversation too I have added 'php' in the tags just in case it needs further processing after retrieval of the data.

Upvotes: 1

Views: 50

Answers (1)

Nick
Nick

Reputation: 147146

You can use a CTE to build up a list of the maximum dates a given user in each context, then JOIN that to the conversation, context and user tables to get the details of the last conversations in each context. For example, for user 2:

WITH usernum AS (
  SELECT '2' AS user
),
max_user_date AS (
  SELECT DISTINCT c.context, u.user, MAX(c.`Date`) OVER (PARTITION BY context, user) AS max_date
  FROM conversation c
  JOIN usernum u ON c.Sender = u.user OR c.Receiver = u.user
)
SELECT m.max_date as `Date`,
       cx.title AS title,
       cv.Message AS message,
       CONCAT(u1.Name, ' ', u1.`Last Name`) AS Sender,
       CONCAT(u2.Name, ' ', u2.`Last Name`) AS Receiver
FROM max_user_date m
JOIN conversation cv ON (cv.Sender = m.user OR cv.Receiver = m.user)
                    AND cv.context = m.context
                    AND cv.Date = m.max_date
JOIN context cx ON cx.id = cv.Context
JOIN user u1 ON u1.User = cv.Sender
JOIN user u2 ON u2.User = cv.Receiver
ORDER BY `Date`, cx.id

Output:

Date        title                       message             Sender          Receiver
2019-01-27  Fault in our stars          What colored hat ?  Jack Daniels    Jane Dow
2019-01-27  Fault in our stars          Do you code?        Jane Dow        Tony Stark
2019-01-28  I have a blue car           Do you use my sql   Jane Dow        John Doe
2019-01-29  Where do you stay           color of your car.  John Doe        Jane Dow
2019-01-31  Lets talk about javascript  favourite key ?     Jane Dow        John Doe
2019-01-31  How do you do               There you are       Peter Parker    Jane Dow

Demo on dbfiddle

If you have a DB version that doesn't support CTEs and window functions, you can write the CTE as a subquery:

SELECT m.max_date as `Date`,
       cx.title AS title,
       cv.Message AS message,
       CONCAT(u1.Name, ' ', u1.`Last Name`) AS Sender,
       CONCAT(u2.Name, ' ', u2.`Last Name`) AS Receiver
FROM (
  SELECT c.context, '2' AS user, MAX(c.`Date`) AS max_date
  FROM conversation c
  WHERE c.Sender = '2' OR c.Receiver = '2'
  GROUP BY c.context
) m
JOIN conversation cv ON (cv.Sender = m.user OR cv.Receiver = m.user)
                    AND cv.context = m.context
                    AND cv.Date = m.max_date
JOIN context cx ON cx.id = cv.Context
JOIN user u1 ON u1.User = cv.Sender
JOIN user u2 ON u2.User = cv.Receiver
ORDER BY `Date`, cx.id

Output is the same as the first query. Demo on dbfiddle

Upvotes: 1

Related Questions