r7263 - dumbhippo/trunk/server/src/com/dumbhippo/server/impl

[commits mugshot org]

Author: otaylor
Date: 2008-01-25 17:07:42 -0600 (Fri, 25 Jan 2008)
New Revision: 7263

Modified:
   dumbhippo/trunk/server/src/com/dumbhippo/server/impl/StackerBean.java
Log:
Switch to a native query for active users, the last attempt didn't work,
  since arbitrary "LEFT JOIN" is not supported in EJBQL.


Modified: dumbhippo/trunk/server/src/com/dumbhippo/server/impl/StackerBean.java
===================================================================
--- dumbhippo/trunk/server/src/com/dumbhippo/server/impl/StackerBean.java	2008-01-25 21:49:33 UTC (rev 7262)
+++ dumbhippo/trunk/server/src/com/dumbhippo/server/impl/StackerBean.java	2008-01-25 23:07:42 UTC (rev 7263)
@@ -1265,35 +1265,26 @@
 	}
 	
 	private List<User> getRecentActivityUsers(int start, int count, boolean includeGroupUpdates) {
-		// The convulated way we do this is to convince MySQL to not get smart on us and
-		// reorder the joins. If we could pass the MySQL STRAIGHT_JOIN hint through MySQL
-		// the simpler:
-		//
-		// SELECT STRAIGHT_JOIN ubd.u FROM UserBlockData ubd JOIN Block b on ubd.block = b 
-		//    WHERE b.publicBlock = 1 [AND group update check on blockType]
-		//
-		// Would work fine, but I don't know any way of doing that short of a native query,
-		// which is it's own pain
+		// Use a native query so we can pass the MySQL STRAIGHT_JOIN hint; if we don't MySQL
+		// creatively reorders things and creates a 5 minute query.
 		
-		String blockFilter;
-		if (includeGroupUpdates) {
-			blockFilter = " AND NOT EXISTS (SELECT b from Block b " + 
-			" WHERE b = ubd.block AND b.publicBlock = 0) "; 
-		} else {
-			blockFilter = " AND NOT EXISTS (SELECT b from Block b " + 
-			" WHERE b = ubd.block AND " + 
-			"  (b.publicBlock = 0 " + 
-			"   OR b.blockType = " + BlockType.GROUP_MEMBER.ordinal() +
-			"   OR b.blockType = " + BlockType.GROUP_CHAT.ordinal() + ")) ";
-		}
-		
-		// we expect the anonymous viewpoint here, so we only get public blocks
-		Query q = em.createQuery("SELECT u FROM UserBlockData ubd " + 
-                " LEFT JOIN User u ON u.id = ubd.user.id " +
-                " WHERE ubd.deleted = 0 " +
-                " AND ubd.participatedTimestamp IS NOT NULL " +
-                " AND " + blockFilter +
-                " ORDER BY ubd.participatedTimestamp DESC");
+		String blockTypeClause;
+		if (!includeGroupUpdates)
+			blockTypeClause = 
+				"      AND b.blockType <> " + BlockType.GROUP_MEMBER.ordinal() +
+				"      AND b.blockType <> " + BlockType.GROUP_CHAT.ordinal();
+		else
+			blockTypeClause = "";
+			
+		Query q = em.createNativeQuery(
+				"SELECT STRAIGHT_JOIN u.* " +
+				"     FROM UserBlockData ubd " +
+				"     JOIN HippoUser u on ubd.user_id = u.id " +
+				"     JOIN Block b on ubd.block_id = b.id " +
+				"    WHERE ubd.deleted = 0 AND ubd.participatedTimestamp IS NOT NULL" +
+				"      AND b.publicBlock = 1 " +
+				blockTypeClause +
+				" ORDER BY ubd.participatedTimestamp DESC", User.class);
 		q.setFirstResult(start);
 		q.setMaxResults(count);
 		
@@ -1303,8 +1294,8 @@
 	private List<PersonMugshotView> getUserMugshotViews(Viewpoint viewpoint, List<User> users, int blockPerUser, boolean includeGroupUpdates) {
 		final String groupUpdatesFilter;
 		if (!includeGroupUpdates) {
-	        groupUpdatesFilter = " AND block.blockType != " + BlockType.GROUP_MEMBER.ordinal() + 
-	                             " AND block.blockType != " + BlockType.GROUP_CHAT.ordinal(); 
+	        groupUpdatesFilter = " AND block.blockType <> " + BlockType.GROUP_MEMBER.ordinal() + 
+	                             " AND block.blockType <> " + BlockType.GROUP_CHAT.ordinal(); 
 		} else {
 			groupUpdatesFilter = "";
 		}