Complex SQL Query

[TechBoyJK]

Hi Guys,

I'm writing a basic messaging app. It's working pretty well, but there's something I want to change.

WHen a user views their inbox, the following sql code is used to fetch their essages. What it does it grab the NEWEST message from a specific user and it uses that message, date, and time to display that message group.

For instance say the messaging went like this.

ME - HELLO (7:51pm)
USER - How are you (7:53pm)
ME - Just fine, you? (7:55pm)
USER - I am ok. Did you get the package (7:57pm)
ME - I did (7:58pm)
ME - But it looked damaged (7:59pm)

The inbox is going to show a row that has

(pic) USER - I am ok. Did you get the package (7:57pm)

Because that was the newest message from that user. However, what I want to display is the pic/username of the other user, but the most current message in the chain, whether it's from me or the other user.

So instead, based on the conversation above, the inbox should display

(pic) USER - <reply arrow> But it looked damaged (7:59pm)

This would show a row that linked to the message chain between me and the user, but since I was the last one to say something, my message would be the one displayed. The 'reply arrow' would be a small graphic that would only be displayed IF the message was from me. If they click on the message, it takes them to the entire chain.

So I need to mod the SQL below to grab the newest message between the two users, along with the information on the other user, and determine in the newest message is from ME or the other user. Thoughts?

SELECT 

tmm.message_id, 
tmm.content_id, 
tmm.sender_id, 
tmm.new, 
tmm.replied, 
tmm.flagged, 
tmm.sent_date, 
tmm.viewed_date, 
tmmc.category, 
tmmc.body, 
tmcc.first_name, 
tmcc.middle_name, 
tmcc.last_name,
tmcc.name_display,
tmpt.thumbnail_url,
tmpt.thumbnail_file
			
FROM tbl_member_messages tmm

INNER JOIN tbl_member_messages_content tmmc on tmm.content_id = tmmc.content_id
INNER JOIN members.dbo.tbl_member_core_contact tmcc on tmm.sender_id = tmcc.member_id
INNER JOIN members.dbo.tbl_member_profile_thumbnail tmpt on tmm.sender_id = tmpt.member_id

WHERE

tmm.recipient_id = <cfqueryparam value="#session.myid#">
    and recipient_archived <> 1
    and recipient_deleted <> 1
    and recipient_spammed <> 1
    AND tmm.message_id IN 
	(
		SELECT message_id FROM
        		(
		        	SELECT MAX(tmm2.message_id) as message_id

				FROM tbl_member_messages tmm2

				INNER JOIN tbl_member_messages_content tmmc2 on tmm2.content_id = tmmc2.content_id
				INNER JOIN members.dbo.tbl_member_core_contact tmcc on tmm2.sender_id = tmcc.member_id

            			WHERE

				tmm2.recipient_id = <cfqueryparam value="#session.myid#">
					and recipient_archived <> 1
					and recipient_deleted <> 1
					and recipient_spammed <> 1
				
				GROUP BY tmm2.sender_id
			) 
		as whoreallycares
	)

ORDER BY tmm.sent_date DESC

 

[brandonb]

I believe you are on the right track with your statement.

This is how I'd do it though (this sql would not really work but you should get where I'm going)

Select case when recipient_id = <session> then sender_id else recipient_id end as other_id
,Max(message_id) as message_id from table where recipient_id = <session> or sender_id = <session>
group by sender_id, recipient_id

That statement would indicate the last message id between the two no matter who sent the message (see where statement.) The case would indicate the id of the person on the other side of the conversation, no matter if they sent or received the message.

Once you have that information just join the rest of the conversation details like you have. Make this portion above as an embedded sql and join on it.

SELECT 

tmm.message_id, 
tmm.content_id, 
tmm.sender_id, 
tmm.new, 
tmm.replied, 
tmm.flagged, 
tmm.sent_date, 
tmm.viewed_date, 
tmmc.category, 
tmmc.body, 
tmcc.first_name, 
tmcc.middle_name, 
tmcc.last_name,
tmcc.name_display,
tmpt.thumbnail_url,
tmpt.thumbnail_file
	[b]		
FROM  (

Select case when recipient_id = <session> then sender_id else recipient_id end as other_id
,Max(message_id) as message_id from table where recipient_id = <session> or sender_id = <session>
group by sender_id, recipient_id


) AS whatever
[/b]
INNER JOIN tbl_member_messages tmm ON [b]whatever.message_id[/b] = tmm.message_id

INNER JOIN tbl_member_messages_content tmmc on tmm.content_id = tmmc.content_id
INNER JOIN members.dbo.tbl_member_core_contact tmcc on [b]whatever.other_id[/b] = tmcc.member_id
INNER JOIN members.dbo.tbl_member_profile_thumbnail tmpt on [b]whatever.other_id[/b] = tmpt.member_id

*like I said, I did not completely answer your question so dont expect to copy/paste and have it work. I'm just helping steer you in the direction I would take without giving you an answer.

 

[KentState]

I would honestly not put the logic in the sql, but rather in your code. Let SQL and data access layer just worry about returning specific data elements. The reason I say this, when you get to scalability, why have the database calculate this for 100s, 1000s, or 10,000s users when it can be offloaded to the client.

 

[bobross419]

Kent, I'm no SQL guru, just curious.

Why would it be better for 10,000+ data points to be sent to the client then calculated... wouldn't that be a lot of unnecessary network traffic?

 

[KentState]

Kent, I'm no SQL guru, just curious.

Why would it be better for 10,000+ data points to be sent to the client then calculated... wouldn't that be a lot of unnecessary network traffic?

Well, the data that is being returned will be sent regardless in this example. I was stating to purely pass the chat records with the data/timestamp and then let the front end do the business logic for you. I typically like to keep the database doing basic CRUD and enforcing data integrity. Maybe I'm missing something with the OP's explanation that would require this server side.

 

[bobross419]

Sounds reasonable. Not really debating as I've never done any client-server based programming, more just curious about your thought process. Thanks.

 

[Markbnj]

Sounds reasonable. Not really debating as I've never done any client-server based programming, more just curious about your thought process. Thanks.

I'm also a fan of keeping the data storage/retrieval layer distinct from the layers that process and present data. It's easy to adhere to the ideal when implementing simple crud operations for runtime objects. As the data presentation requirements get more variable and disconnected from the application class model it gets harder to hold the line, and when I have to manipulate data for a reporting requirement I typically end up abandoning my position completely.

 

[Ka0t1x]

I would probably also only grab the data and let the app run its course to determine. Even if you are fetching 10k rows, that's not exactly ' a lot '.

Also ..

You might also be able to slim down your query a bit if you do not include the thumbnail image with every row.

You could craft a slimmed version of that query and group by the member_id so you only get a list of the thumbnails for the conversation.

Just my 2c.

 

[KentState]

I would probably also only grab the data and let the app run its course to determine. Even if you are fetching 10k rows, that's not exactly ' a lot '.

Also ..

You might also be able to slim down your query a bit if you do not include the thumbnail image with every row.

You could craft a slimmed version of that query and group by the member_id so you only get a list of the thumbnails for the conversation.

Just my 2c.

You said it much better than I did. I also agree that when it comes to reporting, I typically leave that do the database. In that situation, usually the architecture of the system is setup for reporting anyways. The one exception I have to the above is any batch processing. For example, we had a process that would update our website with product information from our ERP system. The process was written in C# and used simple CRUD to get the data. Well, the process started taking 8 hours to run as it was continually fetching and updating a lot of data. I re-wrote it using T-SQL and reduced the run time to under a minute.