/** * Add a GROUP BY clause (or an additional grouping term) to this query * * @param fields one or more Fields to group on * @return this Query object, to allow chaining method calls */ public Query groupBy(Field<?>... fields) { if (immutable) { return fork().groupBy(fields); } if (this.groupByFields == null) { this.groupByFields = new ArrayList<>(); } SquidUtilities.addAll(this.groupByFields, fields); invalidateCompileCache(); return this; }
public void testBoundArgumentsWorkInHavingClause() { Query query = Query.select(Employee.PROPERTIES) .groupBy(Employee.MANAGER_ID) .having(Function.count(Employee.MANAGER_ID).gt(2)); SquidCursor<Employee> cursor = database.query(Employee.class, query); try { assertEquals(1, cursor.getCount()); cursor.moveToFirst(); assertEquals(bigBird.getRowId(), cursor.get(Employee.MANAGER_ID).longValue()); } finally { cursor.close(); } }
public void testAggregateCount() { TestModel model1 = insertBasicTestModel(); TestModel model2 = new TestModel().setFirstName(model1.getFirstName()).setLastName("Smith"); database.persist(model2); IntegerProperty groupCount = IntegerProperty.countProperty(TestModel.FIRST_NAME, false); Query query = Query.select(TestModel.PROPERTIES).selectMore(groupCount).groupBy(TestModel.FIRST_NAME); SquidCursor<TestModel> groupedCursor = database.query(TestModel.class, query); try { groupedCursor.moveToFirst(); assertEquals(1, groupedCursor.getCount()); assertEquals(2, groupedCursor.get(groupCount).intValue()); } finally { groupedCursor.close(); } }
"subordinates"); Query subquery = Query.select(aliasedManagerId, subordinates).from(employeesAlias) .groupBy(aliasedManagerId);
/** * Add a GROUP BY clause (or an additional grouping term) to this query * * @param fields one or more Fields to group on * @return this Query object, to allow chaining method calls */ public Query groupBy(Field<?>... fields) { if (immutable) { return fork().groupBy(fields); } if (this.groupByFields == null) { this.groupByFields = new ArrayList<>(); } SquidUtilities.addAll(this.groupByFields, fields); invalidateCompileCache(); return this; }
public void testSubqueryJoin() { StringProperty managerName = Employee.NAME.as("managerName"); Query query = Query .fromSubquery(Query.select(Employee.MANAGER_ID).from(Employee.TABLE).groupBy(Employee.MANAGER_ID), "subquery"); query.selectMore(managerName); query.join(Join.inner(Employee.TABLE, query.getTable().qualifyField(Employee.MANAGER_ID).eq(Employee.ID))) .orderBy(Employee.MANAGER_ID.asc()); SquidCursor<Employee> cursor = database.query(Employee.class, query); try { assertEquals(3, cursor.getCount()); cursor.moveToFirst(); assertEquals("bigBird", cursor.get(managerName)); cursor.moveToNext(); assertEquals("cookieMonster", cursor.get(managerName)); cursor.moveToNext(); assertEquals("bert", cursor.get(managerName)); } finally { cursor.close(); } }
public void testGroupConcat() { setUpAggregateTest(); StringProperty firstNameConcat = StringProperty.fromFunction( Function.groupConcat(TestModel.FIRST_NAME), "fname_concat"); StringProperty firstNameConcatSeparator = StringProperty.fromFunction( Function.groupConcat(TestModel.FIRST_NAME, "|"), "fname_concat_separator"); StringProperty firstNameDistinct = StringProperty.fromFunction( Function.groupConcatDistinct(TestModel.FIRST_NAME), "fname_distinct"); SquidCursor<TestModel> cursor = database.query(TestModel.class, Query.select(firstNameConcat, firstNameConcatSeparator, firstNameDistinct) .groupBy(TestModel.FIRST_NAME)); try { assertEquals(2, cursor.getCount()); cursor.moveToFirst(); assertEquals("A,A,A", cursor.get(firstNameConcat)); assertEquals("A|A|A", cursor.get(firstNameConcatSeparator)); assertEquals("A", cursor.get(firstNameDistinct)); cursor.moveToNext(); assertEquals("B,B,B", cursor.get(firstNameConcat)); assertEquals("B|B|B", cursor.get(firstNameConcatSeparator)); assertEquals("B", cursor.get(firstNameDistinct)); } finally { cursor.close(); } }