From be3b26e105a06bfdcf700445cd2311a874663bf4 Mon Sep 17 00:00:00 2001
From: Dimitris Routsis <droutsis@ebs.gr>
Date: Fri, 10 Dec 2010 13:55:00 +0200
Subject: [PATCH] Rewrite getUsersSharingFilesForUser() and
 getUsersSharingFoldersForUser() for speed.

---
 src/gr/ebs/gss/server/ejb/GSSDAOBean.java |   24 ++++++++++++++++++------
 1 file changed, 18 insertions(+), 6 deletions(-)

diff --git a/src/gr/ebs/gss/server/ejb/GSSDAOBean.java b/src/gr/ebs/gss/server/ejb/GSSDAOBean.java
index b0ec2d5..39a6ef4 100644
--- a/src/gr/ebs/gss/server/ejb/GSSDAOBean.java
+++ b/src/gr/ebs/gss/server/ejb/GSSDAOBean.java
@@ -31,6 +31,7 @@ import gr.ebs.gss.server.domain.Nonce;
 import gr.ebs.gss.server.domain.User;
 import gr.ebs.gss.server.domain.UserClass;
 
+import java.math.BigInteger;
 import java.util.ArrayList;
 import java.util.Calendar;
 import java.util.Date;
@@ -330,12 +331,23 @@ public class GSSDAOBean implements GSSDAO {
 					setParameter("userId", userId).setParameter("groupId", groupId).getResultList();
 	}
 
+	private List<Long> getGroupIdsForUserId(Long userId) {
+		List<BigInteger> groups = manager.createNativeQuery("select distinct groupsmember_id " +
+		"from GSS_Group_GSS_User where members_id=:userId")
+			.setParameter("userId", userId)
+			.getResultList();
+		List<Long> groupIds = new ArrayList<Long>();
+		for (BigInteger id : groups)
+			groupIds.add(id.longValue());
+		return groupIds;
+	}
+	
 	@Override
 	public List<User> getUsersSharingFoldersForUser(Long userId) {
 		return manager.createQuery("select distinct f.owner from Folder f " +
 					"LEFT JOIN f.permissions p where f.owner.id != :userId and f.deleted=false " +
-					"and (p.user.id=:userId or p.group.id in (select distinct gg.id " +
-					"from Group gg join gg.members memb where memb.id=:userId))) ").
+					"and (p.user.id=:userId or p.group.id in (:groupIds))").
+					setParameter("groupIds", getGroupIdsForUserId(userId)).
 					setParameter("userId", userId).getResultList();
 	}
 
@@ -343,11 +355,11 @@ public class GSSDAOBean implements GSSDAO {
 	public List<User> getUsersSharingFilesForUser(Long userId) {
 		List<User> users = manager.createQuery("select distinct f.owner from FileHeader f " +
 					"LEFT JOIN f.permissions p where f.owner.id != :userId and f.deleted=false " +
-					"and (p.user.id=:userId or p.group.id in (select distinct gg.id from Group gg " +
-					"join gg.members memb where memb.id=:userId)))").
-					setParameter("userId", userId).getResultList();
+					"and (p.user.id=:userId or p.group.id in (:groupIds))").
+					setParameter("userId", userId).
+					setParameter("groupIds", getGroupIdsForUserId(userId)).
+					getResultList();
 		return users;
-
 	}
 
 	@Override
-- 
1.7.10.4