r9897 - helma/helma/trunk/src/helma/objectmodel/db

Author: hannes
Date: 2009-09-15 13:00:30 +0200 (Tue, 15 Sep 2009)
New Revision: 9897

Modified:
   helma/helma/trunk/src/helma/objectmodel/db/Relation.java
Log:
Rewrite group-by select statements using select distinct

Details at http://dev.helma.org/trac/helma/changeset/9897

Modified: helma/helma/trunk/src/helma/objectmodel/db/Relation.java
===================================================================
--- helma/helma/trunk/src/helma/objectmodel/db/Relation.java	2009-09-14 22:27:46 UTC (rev 9896)
+++ helma/helma/trunk/src/helma/objectmodel/db/Relation.java	2009-09-15 11:00:30 UTC (rev 9897)
@@ -880,7 +880,13 @@
             }
 
         String table = otherType.getTableName();
-        String idfield = (groupby == null) ? otherType.getIDField() : groupby;
+        String idfield;
+        if (groupby == null) {
+            idfield = otherType.getIDField();
+        } else {
+            idfield = groupby;
+            buf.append("DISTINCT ");
+        }
 
         if (idfield.indexOf('(') == -1 && idfield.indexOf('.') == -1) {
             buf.append(table).append('.');
@@ -892,11 +898,15 @@
     }
 
     public StringBuffer getCountSelect() {
-        StringBuffer buf = new StringBuffer();
+        StringBuffer buf = new StringBuffer("SELECT ");
         if (otherType.isOracle() && maxSize > 0) {
-            buf.append("SELECT * FROM ");
+            buf.append("* FROM ");
         } else {
-            buf.append("SELECT count(*) FROM ");
+            if (groupby == null) {
+                buf.append("count(*) FROM ");
+            } else {
+                buf.append("count(DISTINCT ").append(groupby).append(") FROM ");
+            }
         }
 
         buf.append(otherType.getTableName());
@@ -954,8 +964,6 @@
 
         // add group and order clauses
         if (groupby != null) {
-            q.append(" GROUP BY ").append(groupby);
-
             if (useOrder && (groupbyOrder != null)) {
                 q.append(" ORDER BY ").append(groupbyOrder);
             }
@@ -982,10 +990,6 @@
             }
         }
 
-        if (isCount && groupby != null) {
-            q.insert(0, "SELECT count(*) FROM (");
-            q.append(") as NESTED_COUNT_QUERY");
-        }
     }
 
     protected void appendAdditionalTables(StringBuffer q) {