Add methods to return group by field counts

7 files changed, 199 insertions, 4 deletions

diff --git a/CHANGELOG.md b/CHANGELOG.md
index 4c90f09..5bca337 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -2,11 +2,9 @@
 All notable changes to this project will be documented in this file.
 This project adheres to [Semantic Versioning](http://semver.org/).
 
-### [Unreleased][unreleased]
-#### Fixed
-#### Changed
+### [2.1.0][unreleased]
 #### Added
-#### Removed
+- Add methods to select counts of a group by field (*SELECT field, COUNT(*) FROM table [WHERE conditions] GROUP BY field*)
 
 ### [2.0.0] - 2016-04-04
 
diff --git a/src/main/java/com/iciql/Query.java b/src/main/java/com/iciql/Query.java
index d26e7b7..20d5520 100644
--- a/src/main/java/com/iciql/Query.java
+++ b/src/main/java/com/iciql/Query.java
@@ -31,6 +31,7 @@ import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.util.ArrayList;
 import java.util.Arrays;
+import java.util.Collections;
 import java.util.HashMap;
 import java.util.IdentityHashMap;
 import java.util.List;
@@ -100,6 +101,56 @@ public class Query<T> {
         }
     }
 
+    public <X> List<ValueCount<X>> selectCount(X x) {
+        return selectCount(x, false);
+    }
+
+    public <X> List<ValueCount<X>> selectCountDesc(X x) {
+        return selectCount(x, true);
+    }
+
+    <X> List<ValueCount<X>> selectCount(X x, boolean desc) {
+        List<ValueCount<X>> list = Utils.newArrayList();
+        SelectColumn<T> col = getColumnByReference(x);
+        X alias = x;
+        if (col == null) {
+            alias = getPrimitiveAliasByValue(x);
+            col = getColumnByReference(alias);
+        }
+        if (col == null) {
+            throw new IciqlException("Unmapped column reference!");
+        }
+        groupBy(alias);
+
+        SQLStatement stat = getSelectStatement(false);
+        col.appendSQL(stat);
+        stat.appendSQL(", COUNT(*)");
+        appendFromWhere(stat);
+
+        ResultSet rs = stat.executeQuery();
+        Class<? extends DataTypeAdapter<?>> typeAdapter = col.getFieldDefinition().typeAdapter;
+        Class<?> clazz = x.getClass();
+        try {
+            // SQLite returns pre-closed ResultSets for query results with 0 rows
+            if (!rs.isClosed()) {
+                while (rs.next()) {
+                    X value = (X) db.getDialect().deserialize(rs, 1, clazz, typeAdapter);
+                    long count = rs.getLong(2);
+                    list.add(new ValueCount<X>(value, count));
+                }
+            }
+            Collections.sort(list);
+            if (desc) {
+                Collections.reverse(list);
+            }
+        } catch (Exception e) {
+            throw IciqlException.fromSQL(stat.getSQL(), e);
+        } finally {
+            JdbcUtils.closeSilently(rs, true);
+        }
+        return list;
+    }
+
     public List<T> select() {
         return select(false);
     }
diff --git a/src/main/java/com/iciql/QueryWhere.java b/src/main/java/com/iciql/QueryWhere.java
index 16f1397..de6ecd4 100644
--- a/src/main/java/com/iciql/QueryWhere.java
+++ b/src/main/java/com/iciql/QueryWhere.java
@@ -605,4 +605,12 @@ public class QueryWhere<T> {
         return query.selectCount();

     }

 

+    public <X> List<ValueCount<X>> selectCount(X x) {

+        return query.selectCount(x, false);

+    }

+

+    public <X> List<ValueCount<X>> selectCountDesc(X x) {

+        return query.selectCount(x, true);

+    }

+

 }

diff --git a/src/main/java/com/iciql/ValueCount.java b/src/main/java/com/iciql/ValueCount.java
new file mode 100644
index 0000000..dfc8570
--- /dev/null
+++ b/src/main/java/com/iciql/ValueCount.java
@@ -0,0 +1,71 @@
+/*
+ * Copyright 2016 James Moger.
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package com.iciql;
+
+import java.io.Serializable;
+import java.util.Date;
+
+public class ValueCount<X> implements Serializable, Comparable<ValueCount<X>> {
+
+    private final static long serialVersionUID = 1L;
+
+    private final X value;
+    private final long count;
+
+    ValueCount(X x, long count) {
+        this.value = x;
+        this.count = count;
+    }
+
+    public X getValue() {
+        return value;
+    }
+
+    public long getCount() {
+        return count;
+    }
+
+    @Override
+    public String toString() {
+        return String.format("%s=%d", value, count);
+    }
+
+    @Override
+    public int compareTo(ValueCount<X> o) {
+        return (count < o.count) ? -1 : ((count == o.count) ? compareValue(o.value) : 1);
+    }
+
+    private int compareValue(X anotherValue) {
+        if (value == null && anotherValue == null) {
+            return 0;
+        } else if (value == null) {
+            return 1;
+        } else if (anotherValue == null) {
+            return -1;
+        }
+
+        if (Number.class.isAssignableFrom(value.getClass())) {
+            long n1 = ((Number) value).longValue();
+            long n2 = ((Number) anotherValue).longValue();
+            return (n1 < n2) ? -1 : ((n1 == n2) ? 0 : 1);
+        } else if (Date.class.isAssignableFrom(value.getClass())) {
+            return ((Date) value).compareTo((Date) anotherValue);
+        }
+
+        return value.toString().compareToIgnoreCase(anotherValue.toString());
+    }
+}
+
diff --git a/src/test/java/com/iciql/test/ModelsTest.java b/src/test/java/com/iciql/test/ModelsTest.java
index 742ed53..96a6e4a 100644
--- a/src/test/java/com/iciql/test/ModelsTest.java
+++ b/src/test/java/com/iciql/test/ModelsTest.java
@@ -19,6 +19,7 @@ package com.iciql.test;
 
 import com.iciql.Db;
 import com.iciql.DbInspector;
+import com.iciql.ValueCount;
 import com.iciql.ValidationRemark;
 import com.iciql.test.models.Product;
 import com.iciql.test.models.ProductAnnotationOnly;
@@ -174,4 +175,47 @@ public class ModelsTest {
         assertEquals(10, ids.size());
         assertEquals("[10, 9, 8, 7, 6, 5, 4, 3, 2, 1]", ids.toString());
     }
+
+    @Test
+    public void testGroupByCount() {
+        Product products = new Product();
+
+        List<ValueCount<String>> categories = db.from(products)
+                .selectCount(products.category);
+        assertEquals(5, categories.size());
+        assertEquals("[Meat/Poultry=1, Produce=1, Seafood=1, Beverages=2, Condiments=5]", categories.toString());
+    }
+
+    @Test
+    public void testGroupByCountDesc() {
+        Product products = new Product();
+
+        List<ValueCount<String>> categories = db.from(products)
+                .selectCountDesc(products.category);
+        assertEquals(5, categories.size());
+        assertEquals("[Condiments=5, Beverages=2, Seafood=1, Produce=1, Meat/Poultry=1]", categories.toString());
+    }
+
+    @Test
+    public void testFilteredGroupByCount() {
+        Product products = new Product();
+
+        List<ValueCount<String>> categories = db.from(products)
+                .where(products.category).isNot("Seafood")
+                .selectCount(products.category);
+        assertEquals(4, categories.size());
+        assertEquals("[Meat/Poultry=1, Produce=1, Beverages=2, Condiments=5]", categories.toString());
+    }
+
+    @Test
+    public void testFilteredGroupByCountDesc() {
+        Product products = new Product();
+
+        List<ValueCount<String>> categories = db.from(products)
+                .where(products.category).isNot("Seafood")
+                .selectCountDesc(products.category);
+        assertEquals(4, categories.size());
+        assertEquals("[Condiments=5, Beverages=2, Produce=1, Meat/Poultry=1]", categories.toString());
+    }
+
 }
diff --git a/src/test/java/com/iciql/test/PrimitivesTest.java b/src/test/java/com/iciql/test/PrimitivesTest.java
index 49595a2..db36be0 100644
--- a/src/test/java/com/iciql/test/PrimitivesTest.java
+++ b/src/test/java/com/iciql/test/PrimitivesTest.java
@@ -18,6 +18,7 @@ package com.iciql.test;
 

 import com.iciql.Db;

 import com.iciql.IciqlException;

+import com.iciql.ValueCount;

 import com.iciql.test.models.MultipleBoolsModel;

 import com.iciql.test.models.PrimitivesModel;

 import org.junit.Test;

@@ -114,4 +115,22 @@ public class PrimitivesTest {
         assertEquals(models.size(), list.size());

         assertEquals("[10, 9, 8, 7, 6, 5, 4, 3, 2, 1]", list.toString());

     }

+

+    @Test

+    public void testPrimitiveGroupByCount() {

+        Db db = IciqlSuite.openNewDb();

+

+        // insert random models in reverse order

+        List<PrimitivesModel> models = PrimitivesModel.getList();

+        Collections.reverse(models);

+        // insert them in reverse order

+        db.insertAll(models);

+

+        PrimitivesModel primitives = new PrimitivesModel();

+        List<ValueCount<Integer>> types = db.from(primitives)

+                .selectCount(primitives.typeCode);

+        assertEquals(2, types.size());

+        assertEquals("[0=5, 1=5]", types.toString());

+    }

+

 }

diff --git a/src/test/java/com/iciql/test/models/PrimitivesModel.java b/src/test/java/com/iciql/test/models/PrimitivesModel.java
index 078b565..aebf9f6 100644
--- a/src/test/java/com/iciql/test/models/PrimitivesModel.java
+++ b/src/test/java/com/iciql/test/models/PrimitivesModel.java
@@ -50,6 +50,9 @@ public class PrimitivesModel {
     @IQColumn

     public float myFloat;

 

+    @IQColumn

+    public int typeCode;

+

     public PrimitivesModel() {

         Random rand = new Random();

         myLong = rand.nextLong();

@@ -78,6 +81,7 @@ public class PrimitivesModel {
         for (int i = 1; i <= 10; i++) {

             PrimitivesModel p = new PrimitivesModel();

             p.myLong = i;

+            p.typeCode = i % 2;

             list.add(p);

         }

         return list;