Início / coding / Perguntas / 77707092
Accepted
PixelPaul
Asked: 2023-12-23 16:45:16 +0800 CST

Consulta SQL para aplicativo de chat

  • 772

Estou realmente lutando para entender como escrever uma consulta. Existem duas tabelas principais para um recurso de chat. A UserConversationtabela contém os IDs do remetente e do destinatário para um bate-papo bidirecional. A UserMessagetabela contém as mensagens correspondentes. As tabelas Usere UserProfilesão necessárias apenas para extrair alguns detalhes extras. Aqui está a estrutura da tabela:

Conversa de usuário

Eu ia ID do remetente SenderDeleteDate ID do destinatário RecipientDeleteDate Criar Data
1 1002 NULO 1001 NULO 23/12/2023 14:14:13.1152723 +07:00
2 1001 NULO 1003 NULO 23/12/2023 14:15:20.1264302 +07:00
3 1001 NULO 1004 NULO 23/12/2023 14:16:57.4302621 +07:00

Do utilizador

Eu ia Nome de usuário
1001 JohnDoe
1002 Curvar
1003 JakeSmith
1004 KrisKringle

Perfil de usuário

ID do usuário avatar
1001 avatar/imagem/1001.jpg
1002 avatar/imagem/1002.jpg
1003 avatar/imagem/1003.jpg
1004 avatar/imagem/1004.jpg

Mensagem do usuário

ID de conversa ID do remetente ID do destinatário Mensagem lida Mensagem Criar Data
1 1002 1001 0 Olá 22/12/2023 13:00:00
1 1001 1002 0 Olá 22/12/2023 13:30:00
2 1001 1003 1 Feliz Natal 22/12/2023 14:00:00
2 1003 1001 1 O mesmo para você 22/12/2023 14:30:00
3 1001 1004 1 Pegue algumas cervejas? 22/12/2023 15:00:00
3 1004 1001 0 Claro 22/12/2023 15:30:00

Estou tentando obter o seguinte resultado:

ID de conversa ID do usuário Nome de usuário UserAvatar Última data Contagem não lida
3 1004 KrisKringle avatar/imagem/1004.jpg 22/12/2023 15:30:00 1
2 1003 JakeSmith avatar/imagem/1003.jpg 22/12/2023 14:30:00 0
1 1002 Curvar avatar/imagem/1002.jpg 22/12/2023 13:30:00 1

Algumas notas:

  1. Pesquise na UserConversationtabela as ocorrências onde JohnDoeestá o SenderIdou RecipientId, mas quero retornar os dados do usuário do outro usuário.
  2. Coluna de contagem MessageReadna UserMessagestabela para cada conversa, mas apenas onde o usuário pesquisado ( JohnDoe) é o destinatário da mensagem.
  3. Ordene os resultados pelos mais recentesCreateDate

Não cheguei muito longe, aqui está o que tenho até agora.

select 
    uc.Id as Id,
    us.Id as UserId,
    us.UserName as Username,
    ups.AvatarImage as UserAvatar
from UserConversation uc 
join UserMessage um on uc.Id = um.ConversationId
join [User] us on uc.SenderId = us.Id
join [User] ur on uc.RecipientId = ur.Id
join UserProfile ups on us.Id = ups.UserId
join UserProfile upr on ur.Id = upr.UserId
where uc.SenderId = 1001 or uc.RecipientId = 1001
sql

1 respostas

  1. Best Answer

    Como você deseja as linhas com JohnDoe como remetente ou destinatário junto com o outro participante, você deve unir as tabelas User e UserProfile duas vezes (uma para o remetente e outra para o destinatário). Feito isso, você pode fazer agregações e agrupamentos e algumas expressões case (ou função Decode() ou qualquer outra coisa semelhante) para obter os dados corretos para o resultado esperado. Não conheço t-sql, mas sua sintaxe é semelhante o suficiente ao sql da Oracle. Tenho certeza que você poderia adotar o código abaixo e transcrevê-lo para t-sql.

    WITH       --  S a m p l e    D a t a :
    UserConversation (Id,   SenderId,   SenderDeleteDate,   RecipientId,    RecipientDeleteDate,    CreateDate) AS
        ( Select 1, 1002,   NULL,   1001,   NULL,   TIMESTAMP '2023-12-23 14:14:13.1152723 +07:00' From Dual Union All      
          Select 2, 1001,   NULL,   1003,   NULL,   TIMESTAMP '2023-12-23 14:15:20.1264302 +07:00' From Dual Union All
          Select 3, 1001,   NULL,   1004,   NULL,   TIMESTAMP '2023-12-23 14:16:57.4302621 +07:00' From Dual
        ),
    Users (Id,  UserName) AS
        ( Select 1001,  'JohnDoe' From Dual Union All
          Select 1002,  'BenDover' From Dual Union All
          Select 1003,  'JakeSmith' From Dual Union All
          Select 1004,  'KrisKringle' From Dual
        ),
    UserProfile (UserId,    Avatar) AS
        ( Select 1001,  'avatar/image/1001.jpg' From Dual Union All
          Select 1002,  'avatar/image/1002.jpg' From Dual Union All
          Select 1003,  'avatar/image/1003.jpg' From Dual Union All
          Select 1004,  'avatar/image/1004.jpg' From Dual 
        ),
    UserMessage (ConversationId,    SenderId,   RecipientId,    MessageRead,    Message,    CreateDate) AS
        ( Select 1, 1002,   1001,   0,  'Hello',    To_Date('2023-12-22 13:00:00', 'yyyy-mm-dd hh24:mi:ss') From Dual Union All
          Select 1, 1001,   1002,   0,  'Hi there', To_Date('2023-12-22 13:30:00', 'yyyy-mm-dd hh24:mi:ss') From Dual Union All
          Select 2, 1001,   1003,   1,  'Merry Christmas', To_Date('2023-12-22 14:00:00', 'yyyy-mm-dd hh24:mi:ss') From Dual Union All
          Select 2, 1003,   1001,   1,  'Same to you',  To_Date('2023-12-22 14:30:00', 'yyyy-mm-dd hh24:mi:ss') From Dual Union All
          Select 3, 1001,   1004,   1,  'Grab some beers?', To_Date('2023-12-22 15:00:00', 'yyyy-mm-dd hh24:mi:ss') From Dual Union All
          Select 3, 1004,   1001,   0,  'Sure', To_Date('2023-12-22 15:30:00', 'yyyy-mm-dd hh24:mi:ss') From Dual
        )

    Oráculo:

    --  M a i n    S Q L :
Select      c.ID "CONVERSATION_ID", 
            Max(Case When usnd.USERNAME = 'JohnDoe' Then urcp.ID Else usnd.ID End) "USER_ID", 
            Max(Case When usnd.USERNAME = 'JohnDoe' Then urcp.USERNAME Else usnd.USERNAME End) "USER_NAME", 
            Max(Case When usnd.USERNAME = 'JohnDoe' Then uprcp.AVATAR Else upsnd.AVATAR End) "USER_AVATAR",
            To_Char(Max(um.CREATEDATE), 'yyyy-mm-dd hh24:mi:ss') "LATEST_DATE",
            Count(Case When urcp.USERNAME = 'JohnDoe' And um.MessageRead = 0 Then 1 End) "UNREAD_COUNT"
From        UserMessage um
Inner Join  UserConversation c ON(um.ConversationId = c.ID)
Inner Join  Users usnd ON(usnd.ID = um.SENDERID)
Inner Join  Users urcp ON(urcp.ID = um.RECIPIENTID)
Inner Join  UserProfile upsnd ON(upsnd.USERID = c.SENDERID)
Inner Join  UserProfile uprcp ON(uprcp.USERID = c.RECIPIENTID)
Where       'JohnDoe' IN(usnd.USERNAME, urcp.USERNAME)
Group By    c.ID
Order By    c.ID Desc

    /*      R e s u l t :
CONVERSATION_ID    USER_ID USER_NAME   USER_AVATAR           LATEST_DATE         UNREAD_COUNT
--------------- ---------- ----------- --------------------- ------------------- ------------
              3       1004 KrisKringle avatar/image/1004.jpg 2023-12-22 15:30:00            1
              2       1003 JakeSmith   avatar/image/1003.jpg 2023-12-22 14:30:00            0
              1       1002 BenDover    avatar/image/1002.jpg 2023-12-22 13:30:00            1   */
    • 1

relate perguntas