LINUX.ORG.RU
решено Форум — Development  

JPA: Сложный запрос

 , ,


0

1

Изучаю 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»)) и всё становится хорошо.

★★★★★

Последнее исправление: KivApple (всего исправлений: 5)

Есть вот такая нашлёпка на JPA: https://github.com/Blazebit/blaze-persistence
Избавляет от ручного написания запросов :)

Автор оперативно помогает с возникающими трудностями.
Он, кстати, активный контрибьютер в хибернейт.

aol ★★★★★
()

Rastafarra, вот видишь, как много сущностей нужно наплодить, чтобы составит сложный запрос? И посмотри мой коммент жизнь без jpa (комментарий) - у меня не создается ни одной сущности явно в этом методе. Какой вариант понятней для программиста и другого программиста, читающего код?

bvn13 ★★★★★
()

Включи максимальный уровень логирования и посмотри во что превращается твой запрос. Дальше подстраивай под то что нужно.

Я лично жпашный критеиаапи так и ниасилил полностью.

ya-betmen ★★★★★
()
Последнее исправление: ya-betmen (всего исправлений: 1)

Зачем делать критерии ради критериев, написал бы на jpql свой запрос и всё, ну не читабельно же это всё

anonymous
()

Пора выкинуть эту жаву и учить го:

type Rating struct {
    UserID, PostCount int64
}
 
type UserRepository interface {
    GetRatings(userID int64, from time.Time) (Ratings, error)
}
 
type usersRepository struct {
    db *gorm.DB
}
 
func (r *usersRepository) GetRatings(userID int64, from time.Time) (Ratings, error) {
    var ratings Rating[]
   
    err := db.
        Table("users").
        Select(`
            users.id AS user_id,
            COUNT(posts.id) AS post_count
        `).
        Joins("LEFT JOIN posts ON users.id = posts.submitter").
        Where("posts.id is NULL").
        Where("posts.submit_timestamp >= ?", from).
        Group("posts.submitter").
        Order("post_count DESC").
        Find(&ratings ).
        Error
 
    return ratings, err 
}
nikolnik ★★★
()
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.
Development