Get All Participants and their unread message
-
I have the message system DB diagram below.
- A person can create a Thread (conversation) with anyone.
- A Thread has one or more Participants
- A Thread has one or more Message
- A Person can join one or more Thread
- MessageReadState table is to store the record if a participant retried (seen/read) the message. If message is not yet seen/read by the partiipant, there will be no record of the MessageId and PeopleId there.
So, when a person create a Thread with 3 people (Participant), only those participants can join the threads and chat.
Every Participant have their own MessageReadState. If a Participant saw or read the message, his PeopleId , MessageId of the Thread that he joined, and a TimeStamp are written to table MessageReadStates.
What I’m doing is to get the all participants who have not yet read the message (Name, Email, Thread.Title, maybe one of the unread messages per Thread). So that I can send them the email notification that they have new messages.
So, here is the query I have written so far. I tested with only one participant first that is why I put PeopleId manually there.
DECLARE @fromDate datetime = '2017-01-11';
DECLARE @toDate datetime = '2017-01-14';
DECLARE @testPeopleId int = 57;SELECT t0.Id AS LastMessageId,
t0.MessageContent AS LastMessage,
t0.CreatedOn AS LastMessageDate,
t0.SenderPeopleId,
t1.Id AS ThreadId,
t1.OwnerPeopleId AS ThreadOwnerId,
t1.CreatedOn AS ThreadDate
FROM dbo.Messages AS t0
INNER JOIN
dbo.MessageThreads AS t1
ON t1.Id=t0.ThreadId
WHERE t0.CreatedOn>=@fromDate
AND t0.CreatedOn<=@toDate
AND t0.IsSent=1
AND t0.IsDeleted=0
AND t1.IsDeleted=0
AND t1.PriorityCode<>0
AND EXISTS(
SELECT NULL AS EMPTY
FROM dbo.MessageThreadParticipants AS t2
WHERE t2.PeopleId IN(@testPeopleId)
AND t2.IsDeleted=0
AND t2.ThreadId=t1.Id)
AND NOT EXISTS(
SELECT NULL AS EMPTY
FROM dbo.MessageStates AS t3
WHERE t3.PeopleId IN(@testPeopleId)
AND t3.MessageId=t0.Id);
It gave this result:Now, when I replace the test PeopleId with a list of PeopleId from a SelectStatment , the query does not produce any result. I replaced @testPeopleId with SELECT * FROM #PeopleIds and it does not work. I got no result back.
Here the complete query:
DECLARE @fromDate datetime = '2017-01-11';
DECLARE @toDate datetime = '2017-01-14';
DECLARE @testPeopleId int = 57;--query 1
--Select list of ThreadId first
SELECT DISTINCT [t1].[Id] AS [ThreadId] INTO #ThreadIds
FROM [dbo].[Messages] AS [t0]
INNER JOIN [dbo].[MessageThreads] AS [t1] ON [t1].[Id] = [t0].[ThreadId]
WHERE
[t0].[CreatedOn] >= @fromDate AND [t0].[CreatedOn] <= @toDate AND ( [t0].[IsSent] = 1 )
AND ( [t0].[IsDeleted] = 0 )--query 2
--Select all ThreadParticipants by list of ThreadId from query1
SELECT t1.PeopleId INTO #PeopleIds FROM dbo.MessageThreadParticipants t1 WHERE t1.ThreadId IN (SELECT * FROM #ThreadIds)--query 3
SELECT t0.Id AS LastMessageId,
t0.MessageContent AS LastMessage,
t0.CreatedOn AS LastMessageDate,
t0.SenderPeopleId,
t1.Id AS ThreadId,
t1.OwnerPeopleId AS ThreadOwnerId,
t1.CreatedOn AS ThreadDate
FROM dbo.Messages AS t0
INNER JOIN
dbo.MessageThreads AS t1
ON t1.Id=t0.ThreadId
WHERE t0.CreatedOn>=@fromDate
AND t0.CreatedOn<=@toDate
AND t0.IsSent=1
AND t0.IsDeleted=0
AND t1.IsDeleted=0
AND t1.PriorityCode<>0
AND EXISTS(
SELECT NULL AS EMPTY
FROM dbo.MessageThreadParticipants AS t2
WHERE t2.PeopleId IN(SELECT * FROM #PeopleIds)
AND t2.IsDeleted=0
AND t2.ThreadId=t1.Id)
AND NOT EXISTS(
SELECT NULL AS EMPTY
FROM dbo.MessageStates AS t3
WHERE t3.PeopleId IN(SELECT * FROM #PeopleIds)
AND t3.MessageId=t0.Id);--drop the temp tables
DROP TABLE #ThreadIds;
DROP TABLE #PeopleIds;I have not include People, Employees, Students in the query to get the name and email yet though because I am now stuck with query above.
However, I want to produce the result like this:
Please suggest me a query to get the result like above. Answer to this question will be my new year gift of 2017. Thank you.
-
Any chance you can change the data model? It doesn't make any sense to have a separate table for students and employees, as you could easily incorporate a PersonType field that can identify which is which, and remove the JOIN complexity associated with that. However, that said, I would think that something along the lines of the following might work:
SELECT COALESCE(S.email, E.email) AS email
FROM Messages AS M
INNER JOIN MessageThreads AS MT
ON M.ThreadId = MT.ThreadId
INNER JOIN MessageThreadParticipants AS MTP
ON MT.ThreadId = MTP.ThreadId
INNER JOIN People AS P
ON MTP.PeopleId = P.PeopleId
INNER JOIN MessageReadStatus AS MRS
ON P.PeopleId = MRS.PeopleId
AND M.MessageId = MRS.MessageId
LEFT OUTER JOIN Students AS S
ON P.StudentId = S.StudentId
LEFT OUTER JOIN Employees AS E
ON P.EmployeeId = E.EmployeeId
WHERE MRS.ReadData = <value indicating the message is unread>Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - sgmunson - Tuesday, January 17, 2017 3:09 PM
Actually it can make sense to have a separate table for employees and students, because the same person can be both an employee and a student and because there are attributes that apply to students that do not apply to employees and vice versa.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - drew.allen - Tuesday, January 17, 2017 3:24 PM
I don't believe those attributes should actually be stored in the People table nor that there should be two separate tables for this. Every time I've seen someone do such a thing, something bad has gone wrong or become much more difficult because of the separation.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden - Tuesday, January 17, 2017 4:40 PM
The problem with relying on anecdotal evidence is that it may not be representative of the full range of possible outcomes, particularly since there is an inherent observational bias. Also, we all know that it's much easier to come up with a bad design than a good design. The fact that you have only encountered bad designs does not mean that the approach is flawed only that those particular design were flawed.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - drew.allen - Wednesday, January 18, 2017 8:52 AM
Drew,
I've seen a rather large number of "flawed designs" over the last 30 years, and the number I see keeps growing by leaps and bounds every time I start a new contract. Jeff is perfectly justified in relying on fact as opposed to mere anecdotal evidence. The amount of real "crap" out there is astounding. Much of it has so much organizational inertia that fixing it is impractically expensive, and in many cases, even migrating away from it is in a similar category of impractical. It's not that either Jeff or I have a bad attitude... it's just that we repeatedly see the bad stuff because we're the ones who get the call to try and fix them. Most of the time, the best we can do is apply a band-aid of sorts. However, I'm quite sure Jeff would be preppin' the beer popsicles for the ones he can actually do something really useful with. It's not uncommon for folks with a background that spans a relatively small number of longer-term employers to just not see that much of the crap, so I can't blame you for your point of view. You just need to realize that there's an extraordinarily large universe of stuff you can do best by hoping to never see...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - sgmunson - Wednesday, January 18, 2017 4:43 PM
The problem is that that's not what Jeff was doing. He was relying on anecdotal evidence ("every time I've seen someone do such a thing") instead of facts, not the other way around. Yes, there are lots of bad designs out there, but Jeff was arguing that because he's seen a lot of bad designs, the approach is wrong. I'm reminded of a quote attributed to Thomas Edison, "I have not failed. I've just found 10000 ways that won't work." Jeff has found lots of ways that don't work, but that doesn't mean that none of them will work.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - drew.allen - Thursday, January 19, 2017 9:11 AM
I think you're missing the point. Just because you can "make it work", doesn't mean it's a good idea. The future consequences of such decisions are predominantly bad, and not just because Jeff has seen it a lot, but because I and most of the other SQL development contractors I know, that know much about databases have consistently been brought in to try and "put lipstick on a pig", where multiple truly ugly pigs are lined up awaiting their "facial transformation". On those occasions when we manage to get pork or bacon out of the pig instead of a bloody mess with no meat, we celebrate. It just doesn't happen often enough to conclude that "making it work without changing it" is all that likely to be a good idea, and I'm pretty sure that's what he was getting at. And no, it's not "anecdotal" when it's so darn common.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) - sgmunson - Thursday, January 19, 2017 12:33 PM
The problem is that I didn't look at the small image of the data structure that was posted at the top of the thread. I didn't realize how truly awful this particular design is. There may still be a reason to keep separate student and employee tables, but not based on the data provided.
For example, both the student and employee tables have name attributes. These are not facts about them as students or employees, these are facts about them as people, so the name belongs on the people table.
Also, the foreign keys are backwards. The people table contains foreign keys to the student and employee tables, when it is the students and employees tables that should have foreign keys to the people table.
As it is currently set up, a person who is both a student and an employee can have one name on their student record and another on their employee record. And there is no way to name a person who is neither a student nor an employee. You can also have a student or employee who is not a person.
In short, this specific design has very serious issues.
Drew
PS: I really hate that ALL of the primary keys are labelled ID. It makes it very difficult to remember which particular ID field you are talking about.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply