@Override public Map<String, Long> countByBookType(Workspace workspace) { QBook qBook = QBook.book; Map<String, Long> result = from(qBook).where(qBook.workspace.eq(workspace)) .groupBy(qBook.type) .transform(GroupBy.groupBy(qBook.type).as(qBook.count())); return result;
@Test public void booleanBuilder_with_null_in_having() { assertEquals("select cat\nfrom Cat cat\ngroup by cat.name", selectFrom(cat).groupBy(cat.name).having(new BooleanBuilder()).toString());
@Override public List<AuditStatsDto> countByUser(Predicate predicate) { NumberPath<Long> aliasCount = Expressions.numberPath(Long.class, "userCount"); QAudit qAudit = QAudit.audit; return from(qAudit) .select(Projections.constructor(AuditStatsDto.class, qAudit.user, qAudit.user.count().as(aliasCount))) .where(predicate) .groupBy(qAudit.user) .orderBy(aliasCount.desc()) .fetch();
@Test public void factoryExpression_in_groupBy() { Expression<Cat> catBean = Projections.bean(Cat.class, cat.id, cat.name); assertFalse(query().from(cat).groupBy(catBean).select(catBean).fetch().isEmpty());
@Override public List<AuditStatsDto> countStatusByDate(Predicate predicate) { NumberPath<Long> aliasDate = Expressions.numberPath(Long.class, "date"); QAudit qAudit = QAudit.audit; Expression<String> groupDateExpr = Expressions.stringTemplate("DATE_FORMAT({0}, {1})", qAudit.startTime, "%Y-%m-%d"); Expression<String> selectDateExpr = Expressions.stringTemplate("DATE_FORMAT({0}, {1})", qAudit.startTime, "%Y-%m-%d").as("date"); return from(qAudit) .select(Projections.constructor(AuditStatsDto.class, selectDateExpr, qAudit.status, qAudit.count())) .where(predicate) .groupBy(groupDateExpr, qAudit.status) .orderBy(aliasDate.asc()) .fetch();
@Test public void count_distinct() { QCat cat = QCat.cat; query().from(cat) .groupBy(cat.id) .select(cat.id, cat.breed.countDistinct()).fetch();
@Test public void groupBy_results() { QueryResults<Integer> results = query().from(cat).groupBy(cat.id).select(cat.id).fetchResults(); assertEquals(6, results.getTotal()); assertEquals(6, results.getResults().size());
@Test @Ignore // FIXME public void groupBy_count() { List<Integer> ids = query().from(cat).groupBy(cat.id).select(cat.id).fetch(); long count = query().from(cat).groupBy(cat.id).fetchCount(); QueryResults<Integer> results = query().from(cat).groupBy(cat.id) .limit(1).select(cat.id).fetchResults(); long catCount = query().from(cat).fetchCount(); assertEquals(catCount, ids.size()); assertEquals(catCount, count); assertEquals(catCount, results.getResults().size()); assertEquals(catCount, results.getTotal());
@Test @Ignore // FIXME public void groupBy_distinct_count() { List<Integer> ids = query().from(cat).groupBy(cat.id).distinct().select(Expressions.ONE).fetch(); QueryResults<Integer> results = query().from(cat).groupBy(cat.id) .limit(1).distinct().select(Expressions.ONE).fetchResults(); assertEquals(1, ids.size()); assertEquals(1, results.getResults().size()); assertEquals(1, results.getTotal());
@Test public void groupBy_results2() { QueryResults<Integer> results = query().from(cat).groupBy(cat.birthdate).select(cat.id.max()).fetchResults(); assertEquals(1, results.getTotal()); assertEquals(1, results.getResults().size());
@Test public void groupBy2() { // select cat0_.name as col_0_0_, cat0_.breed as col_1_0_, sum(cat0_.bodyWeight) as col_2_0_ // from animal_ cat0_ where cat0_.DTYPE in ('C', 'DC') and cat0_.bodyWeight>? // group by cat0_.name , cat0_.breed query().from(cat) .where(cat.bodyWeight.gt(0)) .groupBy(cat.name, cat.breed) .select(cat.name, cat.breed, cat.bodyWeight.sum()).fetch();
@Test @NoHibernate // https://hibernate.atlassian.net/browse/HHH-1902 public void groupBy_select() { // select length(my_column) as column_size from my_table group by column_size NumberPath<Integer> length = Expressions.numberPath(Integer.class, "len"); assertEquals(ImmutableList.of(4, 6, 7, 8), query().select(cat.name.length().as(length)).from(cat).orderBy(length.asc()).groupBy(length).fetch());
@Test public void map_groupBy() { QShow show = QShow.show; assertEquals(1, query().from(show).select(show.acts.get("X")).groupBy(show.acts.get("a")).fetchCount());
@Override public ApplicationEntity findByInstanceIds(final String accountId, final String region, final Collection<String> instanceIds) { final QApplicationEntity qApp = new QApplicationEntity("a"); final QLifecycleEntity qLifecycle = new QLifecycleEntity("l"); return from(qLifecycle) .join(qLifecycle.applicationEntity, qApp) .where(qLifecycle.accountId.eq(accountId), qLifecycle.region.eq(region), qLifecycle.instanceId.in(instanceIds)) .groupBy(qApp.id) .orderBy(qLifecycle.lastModified.max().desc()) .limit(1) .select(qApp) .fetchOne();
@Test @NoBatooJPA @NoHibernate public void count_distinct2() { QCat cat = QCat.cat; query().from(cat) .groupBy(cat.id) .select(cat.id, cat.birthdate.dayOfMonth().countDistinct()).fetch();
@Test public void order_by_count() { NumberPath<Long> count = Expressions.numberPath(Long.class, "c"); query().from(cat) .groupBy(cat.id) .orderBy(count.asc()) .select(cat.id, cat.id.count().as(count)).fetch();
@Test @NoEclipseLink public void groupBy_yearMonth() { query().from(cat) .groupBy(cat.birthdate.yearMonth()) .orderBy(cat.birthdate.yearMonth().asc()) .select(cat.id.count()).fetch();
@Test @Ignore // FIXME public void test() { QCat cat = QCat.cat; SubQueryExpression<?> subQuery = select(cat.birthdate, cat.name, cat.mate).from(cat) .where(select(cat.mate, cat.birthdate.max()) .from(cat) .groupBy(cat.mate) .contains(Projections.tuple(cat.mate, cat.birthdate))); assertToString( "(select cat.birthdate, cat.name, cat.mate from Cat cat " + "where (cat.mate, cat.birthdate) in " + "(select cat.mate, max(cat.birthdate) from Cat cat group by cat.mate))", subQuery);
@Test public void subQuery2() { QCat cat = QCat.cat; QCat other = new QCat("other"); assertEquals(savedCats, query().from(cat) .where(cat.name.in(select(other.name).from(other) .groupBy(other.name))) .orderBy(cat.id.asc()) .select(cat).fetch());