查询中的SQL CTE范围 [英] SQL CTE scope in query

问题描述

我有表 ChatMessages ， ChatGroups 和 ChatGroupMemberships .用户可以在0..N组中，并且组中可以是1..N个聊天消息.组启动后即创建第一个消息，这是一种有效"的ping操作.

I have tables ChatMessages, ChatGroups and ChatGroupMemberships. A user can be in 0..N groups and in group can be 1..N chat messages. That first message is created once group is initied and it is sort of "alive" ping.

我正在优化重构用户对话列表的方式.该列表非常标准，您可能从任何社交网站上都知道它:

I'm optimizing the way I'm reconstructing the list of a user's conversations. That list is pretty standard and you may know it from any social site:

| Chat with User X -> [last message in that chat group]
| Group chat named ABC -> [same]

到目前为止，我只是查询了 ChatGroupMemberships 的列表，其中 userId = X (x已登录用户)，然后查询此集合中的每个条目我选择了该组中的最新消息，然后在服务器上订购了整个列表(安装在DB上).

What I did so far was that I simply queried a list of ChatGroupMemberships where userId = X (x being logged in user) and then for each entry in this collection I've selected latest message in that group and then ordered entire list on server (insted of doing that on DB).

我正在本项目中尽可能使用NHibernate创建查询，因此上述功能如下:

I'm creating queries with NHibernate where possible in this project so the function for the above follows:

public ChatMessage GetLastMessageInGroup(int groupId)
{
    return session.CreateCriteria<ChatMessage>()
    .AddOrder(Order.Desc("Date"))
    .Add(Restrictions.Eq("RecipientId", groupId))
    .SetMaxResults(1)
    .List<ChatMessage>().FirstOrDefault();
 }

现在，我非常确定这是我在那做的一个非常丑陋的解决方案，并且随着用户创建越来越多的聊天组，该对话列表的重建时间开始花费越来越多的时间(我通过AJAX进行，但是仍然).现在，一个用户通常是大约50个组的成员.

Now I'm pretty sure that this is a pretty ugly solution I did there and as users created more and more chat groups reconstruction time of that conversations list started to took more and more time (I'm doing that via AJAX but still). Now a user is commonly a member of about 50 groups.

因此需要对此进行优化，而现在我要做的是将该列表的加载分成较小的批处理.当用户滚动列表时，将即时加载条目.

Thus a need to optimize this arose and what am I doing now is that I'd like to split loading of that list into smaller batches. As users scroll the list loads entries on fly.

我正在使用的功能如下:

The function I'm working on looks like:

public List<ChatGroupMembershipTimestamp> GetMembershipsWhereUserId(int userId, int take, int skip)
{

} 

我花了好几个小时学习有关CTE的知识，并提出以下建议:

I've spent a good few hours learning about CTE's and come up with the following:

WITH ChatGroupMemberships AS
( 
    SELECT p.Date, p.RecipientId, p.RecipientType, p.Id, p.userId
    ROW_NUMBER() OVER(PARTITION BY p.RecipientId, p.userId ORDER BY p.Id DESC)
    AS rk FROM ChatMessages p
)

SELECT s.date, s.recipientId as groupId, s.recipientType as groupType, s.userId
FROM ChatGroupMemberships s

WHERE s.rk = 1 
Order by s.date desc;

这将返回每个组中每个用户的最新消息.问题是我的用户不是所有这些组的成员，因此我需要以某种方式在 ChatGroupMemberships 表上加入吗?，并检查是否存在该行用户ID为 userId

This returns last (newest) message by every user in every group. The catch is that my user in not a member of every of these groups so I need to somehow join? this on the ChatGroupMemberships table and check whether there is a row with that user's ID as userId

上述查询的示例如下:

我可能会对此过于复杂，我最初的计划是执行:

I might be as well overcomplicating this, my original plan was to execute:

select m.id as messageId, groupId, date
from ChatGroupMemberships g 
join ChatMessages m on g.groupId = m.recipientId
where g.userId = XXX <-- user's id
order by m.date desc

收益:

但是在这里，我只需要对上面的查询尝试执行的每个groupId的最上面一行.抱歉，这可能会造成混淆(由于我缺乏适当的术语/知识)，而且这是一个相当长的问题.

But here I'd need only the top-most row for each groupId which I tried to do with the query above. Sorry this might be confusing (due to my lack of proper terms / knowledge) and it is a fairly long question.

如果需要任何澄清，我非常乐于合作.

Should any clarification be needed I'd be more than happy to collaborate.

最后，我要附上表格的设计方案

Lastly, I'm attaching design schemes of the tables

聊天消息:

聊天组成员身份:

chat group memberships:

聊天组:

推荐答案

WITH messages_ranked AS
( 
    SELECT p.Date, p.RecipientId, p.RecipientType, p.Id, p.userId
    ROW_NUMBER() OVER(PARTITION BY p.RecipientId, p.userId ORDER BY p.Id DESC) AS rk 
    FROM ChatMessages p
    JOIN ChatGroupMemberships g 
    on p.recipientId = g.groupId
    where g.user_id = XXX
)

SELECT s.date, s.recipientId as groupId, s.recipientType as groupType, s.userId
FROM messages_ranked s

WHERE s.rk = 1 
Order by s.date desc;

这篇关于查询中的SQL CTE范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！