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
103 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:Neil Thompson
  • 14
  • 10
25 Comments
 
LVL 47

Expert Comment

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

Author Comment

by:Neil Thompson
ID: 40428174
This is on MySQL 5.6
0
 
LVL 47

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
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 3

Author Comment

by:Neil Thompson
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:Neil Thompson
ID: 40428188
To be honest I've fudged my way to get it this far :)
0
 
LVL 47

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:Neil Thompson
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:Neil Thompson
ID: 40428199
It should only be getting the last message on each one (whether its a reply, or the original message)
0
 
LVL 47

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:Neil Thompson
ID: 40428207
That's getting there but its not returning the users profile picture of screen name now?
0
 
LVL 47

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:Neil Thompson
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
 
LVL 47

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 47

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:Neil Thompson
ID: 40428240
Vitor you are a true star. Thank you so much for this, its given me headaches for weeks :)
0
 
LVL 47

Expert Comment

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

Author Comment

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

Author Comment

by:Neil Thompson
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 47

Expert Comment

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

Author Comment

by:Neil Thompson
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:Neil Thompson
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 47

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:Neil Thompson
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:Neil Thompson
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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 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