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
98 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 45

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 45

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 45

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 45

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 45

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 45

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 45

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 45

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 45

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 45

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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.​
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

747 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

12 Experts available now in Live!

Get 1:1 Help Now