StatisticDaoImpl.java

package fr.openwide.core.showcase.core.business.statistic.dao;

import java.util.Date;
import java.util.Map;

import org.springframework.stereotype.Repository;

import com.google.common.collect.Range;
import com.google.common.collect.Table;
import com.querydsl.core.Tuple;
import com.querydsl.core.group.GroupBy;
import com.querydsl.core.types.MappingProjection;
import com.querydsl.jpa.impl.JPAQuery;

import fr.openwide.core.commons.util.collections.DateDiscreteDomain;
import fr.openwide.core.jpa.business.generic.dao.JpaDaoSupport;
import fr.openwide.core.jpa.querydsl.Expressions2;
import fr.openwide.core.jpa.querydsl.group.GroupBy2;
import fr.openwide.core.showcase.core.business.user.model.QUser;
import fr.openwide.core.showcase.core.business.user.model.UserGender;

@Repository
public class StatisticDaoImpl extends JpaDaoSupport implements IStatisticDao {
	
	@Override
	public Map<UserGender, Integer> getUserGenderStatistics() {
		return new JPAQuery<>(getEntityManager())
				.from(QUser.user)
				.groupBy(QUser.user.gender)
				.orderBy(QUser.user.gender.asc())
				.transform(GroupBy2.transformer(GroupBy.sortedMap(QUser.user.gender, QUser.user.count().intValue())));
	}

	@Override
	public Table<UserGender, Date, Integer> getUserCreationCountByGenderByWeekStatistics(Range<Date> dateRange) {
		final DateDiscreteDomain domain = DateDiscreteDomain.weeks();
		dateRange = domain.alignOut(dateRange);
		return new JPAQuery<>(getEntityManager())
				.from(QUser.user)
				.groupBy(QUser.user.gender, QUser.user.creationDate)
				.where(Expressions2.inRange(QUser.user.creationDate, dateRange))
				.orderBy(QUser.user.gender.asc(), QUser.user.creationDate.asc())
				.transform(GroupBy2.transformer(GroupBy2.table(
						QUser.user.gender,
						new MappingProjection<Date>(Date.class, QUser.user.creationDate) {
							private static final long serialVersionUID = 1L;
							@Override
							protected Date map(Tuple row) {
								return domain.alignPrevious(row.get(0, Date.class));
							}
						},
						/**
						  * We sum twice: once in the SQL query (for each date) and once in Java (for each week).
						  * We could have summed only in Java, but it would be less optimized if
						  * many user are created each day.
						  * The even better solution would have been to group by week in the SQL query,
						  * but unfortunately it's not easy to do with JPQL.
						  */
						GroupBy.sum(QUser.user.count().intValue())
				)));
	}

}