On 06/12/2012 07:41, Thiago wrote:
> On 05/12/2012 17:16, Matheus de Oliveira wrote:
>>
>>
>> 2012/12/5 Thiago <zan...@farmaponte.com.br
>> <mailto:zan...@farmaponte.com.br>>
>>
>> Boa tarde.
>>
>> Tenho as seguintes tabelas:
>>
>> tb_mensagem
>> id
>> id_mensagem_pai
>> dth_insert
>>
>> tb_mensagem_leitura
>> id_mensagem
>> dth_insert
>>
>> A tabela de mensagens, sempre que existe uma mensagem com
>> id_mensagem_pai, significa que é uma resposta, as mensagens originais
>> (primeiras) contém 0 (zero) no campo id_mensagem_pai.
>>
>> Na tabela tb_mensagem_leitura eu tenho um histórico de todas as vezes
>> que o usuário leu a mensagem, sendo gravado no campo id_mensagem dessa
>> tabela sempre o id da mensagem original.
>>
>> Preciso identificar quantas mensagens não lidas existe na para o
>> usuário, sendo que uma mensagem não lida é:
>> 1) sem registro na tabela tb_mensagem_leitura;
>> 2) o registro da tabela tb_mensagem_leitura é com data menor que a
>> última resposta da mensagem original.
>>
>> Alguém poderia me ajudar com este select? Ou até mesmo com a forma em
>> que estruturei as tabelas, pois para chegar neste resultado está um
>> pouco complicado.
>>
>>
>> Cara, se eu não boiei na maionese, a consulta abaixo resolve o seu
>> problema (não testado):
>>
>> WITH RECURSIVE mensagens AS (
>> SELECT id AS id_original, id, dth_insert
>> FROM tb_mensagem
>> WHERE id_mensagem_pai = 0
>> UNION ALL
>> SELECT pai.id_original, filha.id <http://filha.id>, filha.dth_insert
>> FROM tb_mensagem AS filha
>> INNER JOIN mensagens AS pai ON pai.id <http://pai.id> =
>> filha.id_mensagem_pai
>> ), ultimas AS (
>> SELECT m.id_original, MAX(m.dth_insert) AS dth_insert
>> FROM mensagens AS m
>> GROUP BY m.id_original
>> )
>> SELECT COUNT(*)
>> FROM ultimas AS u
>> LEFT JOIN tb_mensagem_leitura AS l
>> ON l.id_mensagem = u.id_original
>> WHERE l.id_mensagem IS NULL OR l.dth_insert < u.dth_insert;
>>
>>
>> Não me parece muito performático, uma forma de melhorar seria SEMPRE
>> guardar o id da mensagem original em tb_mensagem (não só a da pai, mas a
>> da primeira na hierarquia), o que evitaria toda essa sobrecarga da query
>> recursiva e da outra sub-query, que na prática só estão pegando a data
>> da última mensagem.
>>
>> Atenciosamente,
>
> Mateus, muito obrigado pela resposta.
>
> Acho que não expliquei direito. Na tabela tb_mensagem, o campo
> id_mensagem_pai é sempre o id da mensagem original. Todas as respostas
> estão amarradas a uma única mensagem e não em um formato de árvore.
>
> Dessa forma você acha que existe uma solução melhor com uma melhor
> performance? Essa será uma leitura muitas vezes executada, pois cada
> página do portal que o usuário acessa essa consulta é refeita para
> atualizar as mensagens não lidas.
>
>
> _______________________________________________
> pgbr-geral mailing list
> pgbr-geral@listas.postgresql.org.br
> https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
>
Na verdade eu tenho um outro problema, que deixou as coisas um pouco
mais difíceis.
Na tabela de mensagem, existem os campos id_usuario_des (destinatário) e
id_usuario_rem (remetente). Uma mensagem deve aparecer na caixa de
entrada do usuário quando a mensagem original é id_usuario_des on existe
pelo menos uma resposta da mensagem original como id_usuario_des igual
ao usuário logado.
Eu consegui chegar ao resultado desejado da seguinte forma:
select count(*)
from (
select
tm.id_mensagem,
max(tm.dth_insert)
from (
select
case
when cr.id_mensagem_pai = 0 then cr.id
else cr.id_mensagem_pai
end as id_mensagem,
cr.dth_insert
from portal.tb_correio cr
where cr.id_usuario_des = 2
) as tm
where
(
select hl.id
from portal.tb_correio_historico_leitura hl
where
hl.id_mensagem = tm.id_mensagem and
hl.dth_insert > tm.dth_insert and
hl.dth_nao_lido is null and
hl.id_usuario = 2
limit 1
) is null
group by tm.id_mensagem
) as tp
Porém eu fico com medo quanto a questão de performance. No momento estou
com poucas mensagens na base de dados, por isso a query está sendo
executada rapidamente, mas daqui há alguns meses ela vai começar a ficar
lenta.
Alguma sugestão?
Obrigado!
_______________________________________________
pgbr-geral mailing list
pgbr-geral@listas.postgresql.org.br
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
