将GROUP BY和ORDER BY与对象上的JPA存储库一起使用

Question

我有两个实体：

项目：

@Entity
@Table(name = "item", schema = "public")
public class ItemEntity {
@ManyToOne
@JoinColumn(name = "owner", nullable = false)
private UserEntity user;
....
}

项目视图：

@Entity
@Table(name = "item_view", schema = "public")
public class ItemViewEntity {
@ManyToOne
@JoinColumn(name = "user_id", nullable = false)
private UserEntity user;
@ManyToOne
@JoinColumn(name = "item_id", nullable = false)
private ItemEntity item;
}

我想按用户在上查看的时间顺序返回项目

public interface ItemViewRepository extends JpaRepository<ItemViewEntity, Long> {
@Query("Select u.item from ItemViewEntity u WHERE u.item.owner= ?! group by u.item order by count(u.item") desc")
List<ItemEntity> getItemsByViews(UserEntity user);
}

使用这个查询，我得到错误"；

ERROR: column "itementi1_.id" must appear in the GROUP BY clause or be used in an aggregate function

我在每个实体上定义了所有equals方法

我试过在基元类型上使用groupby等等，一切都很好。看来我的关系有问题。

如何修复此问题并使查询正常工作？

Answer 1

您可以通过在查询中显式指定联接来实现这一点

@Query("SELECT i, COUNT(v) AS viewcount " +
"FROM ItemViewEntity v " +
"JOIN v.item i " +
"WHERE i.user = ?1 " +
"GROUP BY i " +
"ORDER BY viewcount DESC")
List<ItemEntity> getItemsByViews(UserEntity user);