有效使用GORM检索联接数据-Grails 2.3 [英] Using GORM efficiently to retrieve join data - Grails 2.3

问题描述

我有User和Follow Domain类，它们构造了Twitter的Follower，Following关系.当我的用户访问另一个用户页面时，单击其关注者列表.我弹出访问者的关注者列表的列表，然后在其前面显示一个按钮，该按钮标记为关注"或取消关注"，具体取决于您是否已关注该人.因此，我通过以下方式进行操作，但是我不确定这是否有效，或者是否有更好的方法.为了提高效率，我一次只检索20个关注者并允许分页.

I have User, and Follow Domain class that construct the Follower, Following relationship like Twitter. When my User visits another User's page, and click on their Follower list. I pop-up the list of the visited person's follower list, and then show a button in front of it that is labeled "Follow" or "Unfollow" depending on if you already following that person. So I do it the following way, but I'm not sure if this is efficient or maybe there is a better way of doing it. In order to make it efficient, I only retrieve 20 follower at a time and allow pagination.

> example of pop-up:
> ----------------------------------------------
> alex [follow]
> dave [unfollow]
> sarah[follow]
> paul [follow]
> ----------------------------------------------

关注域类具有:

// The person to follow
User follow
// The person who is following the follow
User follower
// The status of the relationship if removed or still following
boolean status

用户域类是典型的Spring Security用户类，其中包含一堆额外的字段，例如位置等.

User domain class is the typical Spring Security User class with a bunch of extra fields like locations and etc.

在我的控制器中，当我收到关注者列表的请求时，请执行以下操作.

In my controller when I receive a request for follower list I do the following.

def visitedUser = User.get(visitedUserId)
// get the list of top 20 followers of this user
def followerList = Follow.findAllByFollow(visitedUser, [max:20])

// get the list of all people current user follow who is in the 20 follower list
def mutualFollow = Follow.findAllByFollowerAndFollowInList(currentUser, followerList)

现在，我可以在要访问的人的个人资料中找到所有关注者的列表，以及彼此共同的人的列表.然后，我将两者传递给我的GSP，并通过GSP中的followerList进行while循环，比较该关注者的ID，如果在我的commonFollow列表中存在该关注者，则将按钮调整为取消关注"，否则将按钮保持如下.

Now I have the list of all the followers in profile of the person I'm visiting, and also the list of those who are mutual. Then I pass both to my GSP and while loop through the followerList in GSP, I compare the id of that follower, if the follower exist in my mutualFollow list I tweak the button to "unfollow" otherwise I keep the button as follow.

进一步优化这一步的方法是，对用户进行投影，使其仅检索commonFollow的ID而不是整个USER域对象，但是由于代理了返回的USER，并且它们周围有包装器，因此我不确定有很大的不同.因为我检索的全部是ID.

One step further to optimize this is to user projection to only retrieve the id of the mutualFollow instead of the whole USER domain object, but since USERs coming back are proxied and they have wrapper around them, I'm not sure that makes a big difference. Because All I retrieve is the id.

我很高兴提出任何改进此方法或替代方法的建议.谢谢

I appreciate any suggestion to improve this approach or an alternative. Thanks

推荐答案

我决定使用HQL，并且只在关注列表中检索那些USER的必要信息，然后我加入了一个聚会，以使他们都关注他们.并在GSP页面中使用了它.这是SQL中的简化解决方案:

I decided to use HQL and only retrieve the necessary info of those USERs within the follow list, and then I did a join to get the mutual people they both follow. And used that in the GSP page. Here is the simplified solution in SQL:

SELECT Ur.follow_id, Urs.follow_id as mutual FROM (SELECT * FROM FOLLOW WHERE FOLLOWER_ID=1)as Urs
RIGHT JOIN (SELECT * FROM FOLLOW WHERE FOLLOWER_ID=3) as Ur
ON Urs.follow_id=Ur.follow_id order by Ur.follow_id;

这篇关于有效使用GORM检索联接数据-Grails 2.3的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！