design de esquema de database do sistema de mensagens de encadeamento

Eu estou tentando alcançar exatamente o que está explicado aqui: Criando um sistema de mensagens privadas encadeadas como o facebook e o gmail , no entanto, eu não entendo completamente a resposta de Joel Brown. Qualquer um pode explicar por favor.

É assim que minhas tabelas de database se parecem com os dados de amostra (suponho que eu os preenchi corretamente para fins de demonstração): insira a descrição da imagem aqui

  1. Eu preciso exibir uma lista de threads com base no LoginId (mais novo no topo) como seria a consulta no LINQ? (o que eu estou perguntando é em um grupo de tópicos de mensagens, me dê a 1 mensagem mais nova em cada thread) – assim como isso é feito no facebook.

  2. Eu preciso exibir todas as mensagens em um segmento de mensagem (LINQ) -> como é feito no facebook onde você clica na mensagem e você veria toda a “conversa” em um piso.

Por favor ajude! obrigado

EDIT -> continuação Joel, isso está correto?

insira a descrição da imagem aqui

Joel, estou um pouco confuso, você pode por favor explicar (comentários / perguntas em negrito):

A ideia aqui é que toda vez que um usuário inicia um novo thread / mensagem, ele começa com um novo registro na tabela THREAD. Em seguida, o usuário é adicionado como um THREAD_PARTICIPANT e o conteúdo da mensagem é adicionado a MESSAGE, que aponta para o contendo o THREAD. O FK de MESSAGE para USER indica o autor da mensagem.

LoginId 1 envia uma mensagem para LoginId2 => novo registro é inserido na tabela MessageThread. Além disso, um registro é inserido no registro MessageThreadParticipant com MessageThreadId = 1, LoginId = 1 (o remetente). E um novo registro é inserido na tabela Message com MessageId = 1, MessageThreadid = 1, SenderLoginId = 1 (correto ??)

é isso que eu tenho depois da iteração: insira a descrição da imagem aqui

Acho que estou confuso porque não há como o Loginid 2 saber que existe uma mensagem para ele. ?? OU talvez eu precise inserir 2 registros em MessageThreadParticipant? (o remetente eo destinatário) -> desta forma, ambos podem ver toda a “conversa”?

EDIT2: Joe, acho que poderia fazer isso:

SELECT Message.MessageId, Message.CreateDate, Message.Body, Login.Username, Message.SenderLoginId , (SELECT MessageReadState.ReadDate FROM MessageReadState WHERE MessageReadState.MessageId = Message.MessageId ) as ReadDate FROM Message INNER JOIN Login ON Message.SenderLoginId = Login.LoginId INNER JOIN MessageThreadParticipant mtp on mtp.MessageThreadId = Message.MessageThreadId AND ( Message.MessageId in ( SELECT Max(Message.MessageId) FROM MessageThreadParticipant INNER JOIN Message ON MessageThreadParticipant.MessageThreadId = Message.MessageThreadId GROUP BY MessageThreadParticipant.MessageThreadId ) ) Where mtp.LoginId = 2 ORDER BY Message.CreateDate DESC; 

Por favor me corrija se eu estiver errado 🙂

Bem, por que você não pergunta? 🙂

Deixe-me tentar entender minha necessidade. Parece-me que você está olhando para um segmento sendo uma lista linear (não uma tree) de mensagens entre duas pessoas. Eu acho que você pode querer permitir mais pessoas do que apenas duas. Isso seria como o Facebook, na medida em que alguém publica uma mensagem e, em seguida, qualquer número de pessoas pode lê-lo e começar a adicionar comentários. Quando você adiciona um comentário, ele coloca você no encadeamento e começa a receber atualizações de status e e-mails informando sobre a atividade no encadeamento e assim por diante. Supondo que é isso que você quer, então o esquema que sugeri para o Big Mike não é exatamente o que você está procurando.

Considere, em vez disso, o seguinte:

Esquema

A ideia aqui é que toda vez que um usuário inicia um novo thread / mensagem, ele começa com um novo registro na tabela THREAD. Em seguida, o usuário é adicionado como um THREAD_PARTICIPANT e o conteúdo da mensagem é adicionado a MESSAGE, que aponta para o contendo o THREAD. O FK de MESSAGE para USER indica o autor da mensagem.

Quando um usuário lê uma mensagem, ele obtém uma input na tabela MESSAGE_READ_STATE para indicar que marcou a mensagem como uma leitura explícita ou implícita, dependendo de como seus requisitos vão.

Quando alguém comenta a mensagem inicial no encadeamento, uma segunda MENSAGEM é adicionada com um FK de volta ao THREAD original e o autor da resposta (usuário) é adicionado à tabela THREAD_PARTICIPANT. E assim acontece quando as mensagens são adicionadas ao tópico por um, dois ou até mais participantes.

Para obter a mensagem mais recente em qualquer encadeamento, apenas pegue o primeiro 1 de MESSAGE classificado descendente na data de criação (ou uma chave de identidade) onde a mensagem FK é para o encadeamento de interesse.

Para obter o encadeamento atualizado mais recentemente para um usuário, obtenha o THREAD relacionado ao início 1 da mensagem classificada como decrescente na data de criação, em que a mensagem está em um encadeamento no qual o usuário é um THREAD_PARTICIPANT.

Eu tenho medo de nunca poder declarar essas coisas no LINQ sem sair do LinqPad. Se você está tendo problemas para entender o que foi dito acima, eu consegui detalhar a resposta com definições de tabela e um pouco de SQL. Basta perguntar nos comentários.

EDIT: esclarecimento de requisitos e implementação

Esclarecendo os requisitos: Inicialmente eu estava pensando em mensagens postadas publicamente com a oportunidade de comentar, enquanto que Shane está atrás de mais do recurso de mensagem direta. Nesse caso, o destinatário inicial precisa ser incluído na tabela THREAD_PARTICIPANT no início.

Para alguma clareza, vamos colocar algumas linhas nas tabelas. Aqui está o cenário (em homenagem ao Dia do Canadá): Usuário 1 Mestres do Usuário 2 para perguntar sobre o encontro de uma cerveja. O usuário 2 responde com uma pergunta sobre onde encontrar e as respostas do Usuário 1. As tabelas seriam algo como isto: (provavelmente supersimplificado)

Amostra de Dados Parte 1Amostra de Dados Parte 2

EDIT # 2: Acesso SQL para lista de todas as mensagens em um segmento, com o estado de leitura …

Usando o esquema do @ OP, este SQL irá obter uma lista de mensagens em um determinado segmento com uma indicação se um dado usuário leu cada mensagem ou não. As mensagens estão no mais recente primeiro pedido.

 SELECT Message.MessageId , Message.CreateDate , Message.Body , Login.Username , (SELECT MessageReadState.ReadDate FROM MessageReadState WHERE MessageReadState.MessageId = Message.MessageId and MessageReadState.LoginId = 2) as ReadState FROM (Message INNER JOIN Login ON Message.SenderLoginId = Login.LoginId) WHERE (((Message.MessageThreadId)=10)) ORDER BY Message.CreateDate DESC; 

Note que o truque, se é justo chamá-lo assim, é que o estado de leitura é captado com uma sub-seleção. Isso é necessário porque parte dos critérios para obter o estado de leitura requer uma cláusula where que não possa ser satisfeita com uma junit externa. Portanto, você usa a subseleção para fixar o valor (possivelmente ausente) desejado da tabela filho MessageReadState.

EDIT 3: SQL para obter todos os threads com a última mensagem em cada um para um determinado usuário …

Para obter uma lista de todos os segmentos nos quais um determinado usuário participou, classificados pela mensagem mais recente primeiro, com apenas a mensagem mais recente sendo exibida (1 mensagem por thread), você usaria uma consulta semelhante à acima, exceto que, em vez de filtrar mensagens por seu FK para o encadeamento de interesse, você filtra as mensagens por uma subconsulta que localiza a mensagem mais recente em cada encadeamento do qual o usuário de interesse participou. Ela ficaria assim:

 SELECT Message.MessageId , Message.CreateDate , Message.Body , Login.Username , (SELECT MessageReadState.ReadDate FROM MessageReadState WHERE MessageReadState.MessageId = Message.MessageId and MessageReadState.LoginId = 2) AS ReadState FROM Message INNER JOIN Login ON Message.SenderLoginId = Login.LoginId WHERE ( Message.MessageId in ( SELECT Max(Message.MessageId) FROM MessageThreadParticipant INNER JOIN Message ON MessageThreadParticipant.MessageThreadId = Message.MessageThreadId WHERE MessageThreadParticipant.LoginId=2 GROUP BY MessageThreadParticipant.MessageThreadId ) ) ORDER BY Message.CreateDate DESC; 

De acordo com Joel Brown’answer, você pode adicionar a coluna LAST_MESSAGE_ID na tabela THREAD, em seguida, obter todos os threads com as últimas mensagens SQL torna-se muito simples. Você deve atualizar essa coluna quando todas as mensagens forem enviadas.

Obtendo todos os tópicos com a última mensagem em cada um para um determinado usuário

 SELECT * FROM THREAD T INNER JOIN MESSAGE M ON T.LAST_MESSAGE_ID=M.MESSAGE_ID INNER JOIN USER SENDER ON M.USER_ID=SENDER.USER_ID LEFT JOIN MessageReadState MRS ON M.MESSAGE_ID=MRS.MESSAGE_ID AND MRS.USER_ID=2