Solved

issue returning all information from chat sytem that has no replies, just its some sort of JOIN problem?

Posted on 2014-11-06
25
101 Views
Last Modified: 2014-11-10
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)

Currently displaying latest message where conversation has 1 or more replies
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.

The 3rd line "Leigh" has no replies as yet and doesnt show which I need it to?
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);

Open in new window


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

Open in new window

0
Comment
Question by:NeilT
  • 14
  • 10
25 Comments
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40428161
Which RDBMS are you working with?
0
 
LVL 3

Author Comment

by:NeilT
ID: 40428174
This is on MySQL 5.6
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40428177
Why are you using a GROUP BY clause?
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

Open in new window

0
 
LVL 3

Author Comment

by:NeilT
ID: 40428187
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
0
 
LVL 3

Author Comment

by:NeilT
ID: 40428188
To be honest I've fudged my way to get it this far :)
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40428189
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.
--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

Open in new window

0
 
LVL 3

Author Comment

by:NeilT
ID: 40428198
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
0
 
LVL 3

Author Comment

by:NeilT
ID: 40428199
It should only be getting the last message on each one (whether its a reply, or the original message)
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40428203
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

Open in new window

0
 
LVL 3

Author Comment

by:NeilT
ID: 40428207
That's getting there but its not returning the users profile picture of screen name now?
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40428217
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

Open in new window

0
 
LVL 3

Author Comment

by:NeilT
ID: 40428222
nearly :) but its only returning my name and profile image, not the name / profile image associated with the conversations

Getting this:
getting this
But the names associated with each message should be as the 2nd image on this page
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40428225
Just realized that also. So need joining with Conversation table and then with Users table.
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

Open in new window

0
 
LVL 46

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40428231
Forgot that's MySQL. Here's the correct version:
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
ORDER BY c1.crID, c1.crconfk

Open in new window

0
 
LVL 3

Author Closing Comment

by:NeilT
ID: 40428240
Vitor you are a true star. Thank you so much for this, its given me headaches for weeks :)
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40428250
Thanks for the challenge ;)
Cheers.
0
 
LVL 3

Author Comment

by:NeilT
ID: 40428255
Thanks sdstuber ill remember that in future
0
 
LVL 3

Author Comment

by:NeilT
ID: 40428288
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.
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40428296
Just add a WHERE clause with the corresponding user ID.
0
 
LVL 3

Author Comment

by:NeilT
ID: 40428300
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)

[/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

Open in new window


It has to narrow down the results to just the conversations user x (1 for example) is involved in
0
 
LVL 3

Author Comment

by:NeilT
ID: 40428305
I as user 1 would have 3 conversations on the go, whereas users 2,3 and 7 would only have 1 each
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40428316
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

Open in new window

0
 
LVL 3

Author Comment

by:NeilT
ID: 40428318
Many thanks again Vitor, I was adding it into the INNER JOINS (scary when you don't know what you're doing ) :)
0
 
LVL 3

Author Comment

by:NeilT
ID: 40432564
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:

http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_28554211.html
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
A short film showing how OnPage and Connectwise integration works.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now