Изучаю JPA (в качестве реализации используется Hibernate) и столкнулся с проблемой. Допустим, у нас есть пользователи, а у пользователей есть посты. Я хочу сделать запрос, который бы строил рейтинг пользователей по количеству постов за последние 30 дней (причём в рейтинге должны быть в том числе пользователи, которые не написали ни одного поста).
В обычном SQL я могу сделать так (в данном случае это MySQL, но для переноса на любой другой диалект потребуется лишь подредактировать сравнение дат):
SELECT users.id as user_id, COUNT(posts.id) as post_count FROM users LEFT JOIN posts ON users.id = posts.submitter
WHERE (posts.id is NULL) OR (posts.submit_timestamp >= (CURRENT_DATE - INTERVAL 30 DAY))
GROUP BY posts.submitter ORDER BY post_count DESC;
А вот как это сделать с помощью CriteriaQuery в упор понять не могу. Пробовал разные комбинации, но то у меня не считаются юзеры без постов, то все результаты умножаются на общее количество пользователей.
Если что модели юзеров и постов типа таких:
@Entity
@Table(name = "users")
public class User {
@Column(name = "id", updatable = false, nullable = false)
@Id
private Integer id;
@OneToMany(mappedBy = "submitter", cascade = CascadeType.REMOVE)
private List<Post> submittedPosts;
...
}
@Entity
@Table(name = "posts", indexes = {
@Index(name = "submitter", columnList = "submitter"),
@Index(name = "submit_timestamp", columnList = "submit_timestamp"),
})
public class Post {
@Column(name = "id", updatable = false, nullable = false)
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE)
private Integer id;
@ManyToOne
@JoinColumn(name = "submitter", updatable = false, nullable = false)
private User submitter;
...
}
Пробовал делать так:
Calendar c = Calendar.getInstance();
c.setTime(new Date());
c.add(Calendar.DATE, -30);
Date startDate = c.getTime();
CriteriaBuilder cb = manager.getCriteriaBuilder();
CriteriaQuery<Tuple> q = cb.createTupleQuery();
Root<User> userRoot = q.from(User.class);
Join<User, Post> join = userRoot.join("submittedPosts", JoinType.LEFT);
q.where(cb.or(
cb.isNull(join.get("id")),
cb.greaterThanOrEqualTo(join.get("submitDate"), startDate)
));
q.select(cb.tuple(userRoot, cb.count(join.get("id"))));
q.groupBy(join.get("submitter"));
q.orderBy(cb.desc(cb.count(join.get("id"))));
Но в результат не попадают пользователи без постов.
UPD: Нашёл решение. Группировать надо результаты по users.id, а не по posts.submitter. SQL иначе всех пользователей без постов сливает в одного и я это не заметил. А JPA почему-то вообще не выдал такие записи в результатах.
В общем, надо сделать q.groupBy(userRoot.get(«id»)) и всё становится хорошо.
