Neil Thompson
asked on
issue returning all information from chat sytem that has no replies, just its some sort of JOIN problem?
Hi
As part of a system I'm creating I have a messaging feature almost identical to FaceBook (as lots of people know the look and feel of that)
All works fine if a user has had a reply to their message but I'm unable to pull back the latest message in the conversations where a conversation has been started.
I'm guessing it’s something simple for a guru but it’s got me well and truly stuck.
There's a live example on SQL Fiddle at http://sqlfiddle.com/#!2/854e3/1/0 which shows both Jayne and Clare and the last message they sent or received, there should also be on for user 3 - Leigh "Hi Leigh, hows it looking on Android?" but that's not showing
I'm after these results, as well as seeing the single message to Leigh please
As part of a system I'm creating I have a messaging feature almost identical to FaceBook (as lots of people know the look and feel of that)
All works fine if a user has had a reply to their message but I'm unable to pull back the latest message in the conversations where a conversation has been started.
I'm guessing it’s something simple for a guru but it’s got me well and truly stuck.
There's a live example on SQL Fiddle at http://sqlfiddle.com/#!2/854e3/1/0 which shows both Jayne and Clare and the last message they sent or received, there should also be on for user 3 - Leigh "Hi Leigh, hows it looking on Android?" but that's not showing
I'm after these results, as well as seeing the single message to Leigh please
CREATE TABLE conversation (
coID int auto_increment primary key,
coUserOne int(11),
coUserTwo int(11),
coIP varchar(30),
coTime int(11)
);
CREATE TABLE users (
uID int auto_increment primary key,
uScreenName varchar(100),
uProfileImage varchar(100),
uSex varchar(10)
);
CREATE TABLE conversation_reply (
crID int auto_increment primary key,
crReply text,
crUserFK int(11),
crIP varchar(30),
crTime int(11),
crConFK int(11)
);
INSERT INTO users VALUES (1,'Neil','20140302__1__b00c1338a7feeae110af30868d871549.png','Male');
INSERT INTO users VALUES (2,'Clare','20140303__2__150fb4811b2794f6b8a39d49ead2edc5.jpg','Female');
INSERT INTO users VALUES (3,'Leigh','20140403__3__fda33411ff84b3f69484d862838dd417.jpg','Male');
INSERT INTO users VALUES (7,'Jayne','20140403__7__87bbf84f78bd08b71a55a2002e35230a.jpg','Female');
INSERT INTO conversation VALUES (1,1,2,'127.0.0.1',1400945511);
INSERT INTO conversation VALUES (2,1,3,'127.0.0.1',1400945613);
INSERT INTO conversation VALUES (3,1,7,'127.0.0.1',1400946146);
INSERT INTO conversation_reply VALUES (1,'This is my first conversation to Clare',1,'127.0.0.1',1400945511,1);
INSERT INTO conversation_reply VALUES (2,'this is a reply from Clare',2,'127.0.0.1',1400945514,1);
INSERT INTO conversation_reply VALUES (3,'Hi Leigh, hows it looking on Android?',1,'127.0.0.1',1400945613,2);
INSERT INTO conversation_reply VALUES (5,'Have you seen the amount of people who have registered on the site in the last day, it\'s gone mad.',1,'127.0.0.1',1400946146,3);
INSERT INTO conversation_reply VALUES (6,'test',7,'127.0.0.1',1400946149,3);
INSERT INTO conversation_reply VALUES (7,'good',1,'92.40.104.96',1401642550,3);
INSERT INTO conversation_reply VALUES (9,'Very good',1,'188.31.214.125',1401703530,3);
INSERT INTO conversation_reply VALUES (10,'latest test from upload to live server :)',1,'82.47.69.204',1401991923,1);
INSERT INTO conversation_reply VALUES (11,'Cooooooooooooooo eeeeeeeeee',2,'77.96.207.54',1401993721,1);
INSERT INTO conversation_reply VALUES (12,'Ipad test',1,'188.29.102.172',1402780028,1);
SELECT a.crConFK, (
SELECT crTime
FROM conversation_reply
WHERE crID = (
SELECT max(crID)
FROM conversation_reply
WHERE crConFK = a.crConFK))
AS crTime, (
SELECT crReply
FROM conversation_reply
WHERE crID = (
SELECT max(crID)
FROM conversation_reply
WHERE crConFK = a.crConFK))
AS crReply, uScreenName, uProfileImage, uID, uSex
FROM users,conversation_reply a
WHERE a.crUserFK = uID
AND uID != 1
AND a.crConFK IN (SELECT coID FROM conversation WHERE (coUserOne = 1 OR coUserTwo = 1) )
GROUP BY uID
ORDER BY a.crTime DESC
Which RDBMS are you working with?
ASKER
This is on MySQL 5.6
Why are you using a GROUP BY clause?
Did you try without it?
Did you try without it?
SELECT a.crConFK, (
SELECT crTime
FROM conversation_reply
WHERE crID = (
SELECT max(crID)
FROM conversation_reply
WHERE crConFK = a.crConFK))
AS crTime, (
SELECT crReply
FROM conversation_reply
WHERE crID = (
SELECT max(crID)
FROM conversation_reply
WHERE crConFK = a.crConFK))
AS crReply, uScreenName, uProfileImage, uID, uSex
FROM users,conversation_reply a
WHERE a.crUserFK = uID
AND uID != 1
AND a.crConFK IN (SELECT coID FROM conversation WHERE (coUserOne = 1 OR coUserTwo = 1) )
ORDER BY a.crTime DESC
ASKER
I've just removed it and it shows message 1 twice if I copy your code above into the sqlfiddle page http://sqlfiddle.com/#!2/854e3/2/0
ASKER
To be honest I've fudged my way to get it this far :)
Even I didn't get your data model.
By your example there are 3 conversations and the one you posted here doesn't looks to me part of the same conversation.
By your example there are 3 conversations and the one you posted here doesn't looks to me part of the same conversation.
--CONVERSATION 1
crID crReply crUserFK crIP crTime crConFK
1 This is my first conversation to Clare 1 127.0.0.1 1400945511 1
2 this is a reply from Clare 2 127.0.0.1 1400945514 1
10 latest test from upload to live server :) 1 82.47.69.204 1401991923 1
11 Cooooooooooooooo eeeeeeeeee 2 77.96.207.54 1401993721 1
12 Ipad test 1 188.29.102.172 1402780028 1
--CONVERSATION 2
crID crReply crUserFK crIP crTime crConFK
3 Hi Leigh, hows it looking on Android? 1 127.0.0.1 1400945613 2
--CONVERSATION 3
crID crReply crUserFK crIP crTime crConFK
5 Have you seen the amount of people who have registered on the site in the last day, it's gone mad. 1 127.0.0.1 1400946146 3
6 test 7 127.0.0.1 1400946149 3
7 good 1 92.40.104.96 1401642550 3
9 Very good 1 188.31.214.125 1401703530 3
ASKER
ITs conversation 2 line 11 above that isn't showing for me?
The last message for conversation 1 and 3 are fine (line 7 Ipad test) and (line 18 (Very good)
Yes, there are 3 conversations
The last message for conversation 1 and 3 are fine (line 7 Ipad test) and (line 18 (Very good)
Yes, there are 3 conversations
ASKER
It should only be getting the last message on each one (whether its a reply, or the original message)
Ok. If you want the last message for each conversation then try this code:
SELECT c1.*
FROM conversation_reply AS c1
INNER JOIN (SELECT crConFK, MAX(crID) MAXcrID
FROM conversation_reply
GROUP BY crConFK) AS c2
ON c1.crID=c2.MAXcrID
ORDER BY c1.crID, c1.crconfk
ASKER
That's getting there but its not returning the users profile picture of screen name now?
I didn't join the users table. Here it's the complete solution:
SELECT c1.crConFK, c1.crTime, c1.crReply, u.uScreenName, u.uProfileImage, u.uID, u.uSex
FROM conversation_reply AS c1
INNER JOIN (SELECT crConFK, MAX(crID) MAXcrID
FROM conversation_reply
GROUP BY crConFK) AS c2
ON c1.crID=c2.MAXcrID
INNER JOIN users u
ON c1.crUserFK=u.uID
ORDER BY c1.crID, c1.crconfk
ASKER
Just realized that also. So need joining with Conversation table and then with Users table.
Check now:
Check now:
SELECT c1.crConFK, c1.crTime, c1.crReply, u.uScreenName, u.uProfileImage, u.uID, u.uSex
FROM conversation_reply AS c1
INNER JOIN (SELECT crConFK, MAX(crID) MAXcrID
FROM conversation_reply
GROUP BY crConFK) AS c2
INNER JOIN conversation c
INNER JOIN users u ON c.coUserTwo=u.uID
ON c.coID=c2.crConFK
ON c1.crID=c2.MAXcrID
ORDER BY c1.crID, c1.crconfk
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Vitor you are a true star. Thank you so much for this, its given me headaches for weeks :)
Thanks for the challenge ;)
Cheers.
Cheers.
ASKER
Thanks sdstuber ill remember that in future
ASKER
I've just realised there is a 2nd part to this question which I forgot to add in the original request and that is the conversations returned should only relate to the currently logged in user ID
e.g. if user 3 is logged in, only messages from him to others, or from others to him should show (not all of them)
I'll open another question now.
e.g. if user 3 is logged in, only messages from him to others, or from others to him should show (not all of them)
I'll open another question now.
Just add a WHERE clause with the corresponding user ID.
ASKER
I've tried adding it in a few places but it errors in fiddle? guess im just misplacing it. (LINE 6 is the code I had)
Where for example would I add this (this is what I used before)
It has to narrow down the results to just the conversations user x (1 for example) is involved in
Where for example would I add this (this is what I used before)
[/SELECT a.crConFK, (SELECT crTime FROM conversation_reply WHERE crID = (SELECT max(crID) FROM conversation_reply WHERE crConFK = a.crConFK)) AS crTime, (SELECT crReply FROM conversation_reply WHERE crID = (SELECT max(crID) FROM conversation_reply WHERE crConFK = a.crConFK)) AS crReply, uScreenName, uProfileImage, uID, uSex
FROM users,conversation_reply a
LEFT JOIN conversation_reply AS b on b.crID = a.crID
WHERE a.crUserFK = uID
AND uID != ?
[b]AND a.crConFK IN (SELECT coID FROM conversation WHERE (coUserOne = ? OR coUserTwo = ?) )[/b]
GROUP BY uID
ORDER BY a.crTime DESC
It has to narrow down the results to just the conversations user x (1 for example) is involved in
ASKER
I as user 1 would have 3 conversations on the go, whereas users 2,3 and 7 would only have 1 each
Based on your original post here's the solution with the WHERE clause (just replace the 1 for the userID that you want):
SELECT c1.crConFK, c1.crTime, c1.crReply, u.uScreenName, u.uProfileImage, u.uID, u.uSex
FROM conversation_reply AS c1
INNER JOIN (SELECT crConFK, MAX(crID) MAXcrID
FROM conversation_reply
GROUP BY crConFK) AS c2
ON c1.crID=c2.MAXcrID
INNER JOIN conversation c ON c.coID=c2.crConFK
INNER JOIN users u ON c.coUserTwo=u.uID
WHERE (c.coUserOne = 1 OR c.coUserTwo = 1)
ORDER BY c1.crID, c1.crconfk
ASKER
Many thanks again Vitor, I was adding it into the INNER JOINS (scary when you don't know what you're doing ) :)
ASKER
For info, there is a continuation to this question as I've noticed something else needed which is the user displaying should always be the other person in the chat, not the logged in user:
https://www.experts-exchange.com/questions/28554211/Messaging-system-needs-to-pull-back-other-persons-details-not-message-initiator.html
https://www.experts-exchange.com/questions/28554211/Messaging-system-needs-to-pull-back-other-persons-details-not-message-initiator.html