如何从所有的查询按照用户数新回复

问题描述

我有一个CRM应用程序。我的工作邮件主题发现了一个麻烦。当我有显示没有新的回复从由每个用户提出的多种查询。作为短期内如Facebook，显示评论计数器时ü登录到帐户乌尔那么它显示没有新的回复。我只是只是想怎么过算不显示总结报告。

在这里我有包括给予基本的思路我的表层次结构。

我算了算没有通过工作人员给用户由最终用户提出了多种新的调查回复。我想怎么过计算AnswerId的总量没有（回答说，工作人员给出的用户）在自己的帐户，而用户登录。

请给我的想法如何实现这一点。

解决方案

 宣布@user_id INT  - 我认为这是一个整数设置@ USER_ID = 123  -  user_ID的您有兴趣SELECT COUNT（*）
从tbl_answer一
加入tbl_question●在q.id = a.question_id
加入tbl_inquiry_history我对i.inquiry_id = q.inquiry_id
其中，i.user_id = @user_id
 

要通知的新的答案，因为他们上次登录，你有两个主要选择用户： -

1. 存储用户知道在用户记录答案的数目 - 下一次，使他们登录，你可以告诉他们有多少新的答案已经建立。这就是所谓的高水印。用户记录已10存储为高水位。他们登录的系统计算用户现在有多少答案已经和发现15的答案 - 所以5必须是新的 - 并增加了一个消息计数用户界面说5个新的答案（和更新高水位15）


2. 在每个答案存储一个标志，如果用户有读特定的答案记录。每当添加一个答案是 - 不设置标志。每当用户读取回答该标志被设置。定期将系统数量多少的答案存在不具有标志设置的用户 - 并显示一条消息，如果发现任何

选项1是快速和肮脏的。这很容易实现。这也是最无用的。用户要问接下来的事情就是好的。所以它的答案是新的？。如果可以的答案到他们已经创建顺序进行排序，你可以（从上面的例子），他们展示最新的5答案 - 但是这大约相当于可以做。他们没有粒度可以去阅读的答案之一，并返回到列表，并纷纷上榜缩短（取出他们只是看了答案） - 没有你（作为开发人员）通过一些复杂的铁圈跳操纵名单

选项2是更复杂的解决方案，但提供该用户可能期望（如Facebook - 或计算器）接口的类型。聪明的事情是不使用布尔/位作为你的标志 - 只允许你跟踪用户是否阅读了答案。如果使用日期时间，你也可以跟踪，当他们阅读的答案 - 它允许对正在使用的系统的方式更加分析

您（或者说，你的用户）将需要决定哪种方法你用 - 每回答一个简单的高水位或颗粒状标志。如果你开始选1，你几乎肯定在一些点来实现选择2 - 所以你不妨字节（笑）子弹，并做正确的第一次

i have one crm application. i worked on message threading and found one trouble. when i have to show no of new replies from multiple inquiries that was raised by per user. as shortly like facebook that show 'comment counter' when u login to ur account then it's shows no of new replies. how ever i just only want to count not showing summary report.

here i have include my table hierarchy that give basic idea.

i just count no of new replies that given by staff user to multiple inquiry raised by end user. how ever i want to count total no of AnswerId's(Replies that given by staff users) while user sign in to his account.

please give me idea how i implement this.

解决方案

declare @user_id int  -- I assume this is an integer

set @user_id=123  -- the user_id you are interested in

select count(*)
from tbl_answer a
join tbl_question q on q.id=a.question_id
join tbl_inquiry_history i on i.inquiry_id=q.inquiry_id
where i.user_id = @user_id

To notify the user of "new" answers since they last logged in, you have two major options:-

1. Store the number of answers the user knows about on the user record - so that next time they log in, you can tell them how many "new" answers have been created. This is called a "high watermark". The user record has 10 stored as the high water mark. They log in. The system counts how many answers the user now has and finds 15 answers - so 5 must be new - and adds a message count to the user interface to say "5 new answers" (and updates the high watermark to 15).
2. Store a flag on each answer to record if the user has read that particular answer. Whenever an answer is added - the flag is not set. Whenever the user reads the answer the flag is set. Periodically the system counts how many answers exist for the user that don't have the flag set - and displays a message if it finds any.

Option 1 is "quick and dirty". It's easy to implement. It's also the least useful. The next thing the user is going to ask is "ok. so which answers are new?". If you can sort the answers into the order they have been created, you can (from the example above) show them the latest 5 answers - but that's about as much as you can do. They have no granularity to be able to go read one of the answers and return to the list and have the list shorten (taking out the answer they just read) - without you (as the developer) jumping through some complex hoops to manipulate the list.

Option 2 is the more complex solution but provides the type of interface that the user is likely to expect (like Facebook - or StackOverflow). The clever thing is not to use a bool/bit as your flag - which only allows you to keep track of whether the user has read the answer. If you use a datetime, you can also keep track of when they read the answer - which allows for much more analysis of the way the system is being used.

You (or rather, your users) will need to decide which approach you use - a simple high watermark or a granular flag on each answer. If you start with option 1, you will almost certainly have to implement option 2 at some point - so you may as well byte (lol) the bullet and do it "right" first time.

这篇关于如何从所有的查询按照用户数新回复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！