在多对多表中统计ID的出现次数

Question

我正在创建一个函数，我的应用程序将显示最喜欢的照片。

我有这个类

public class User{
    @OneToMany(fetch = FetchType.LAZY, mappedBy = "pk.user", cascade = CascadeType.ALL)
    private Set<UserLikedPhoto> likedPhotos = new HashSet<UserLikedPhoto>();
   //Other properties and accessor methods.
}

照片类

public class Photo{
    @OneToMany(fetch = FetchType .LAZY ,mappedBy = "pk.photo")
    private Set<UserLikedTrack> likedByUsers = new HashSet<UserLikedTrack>();
        //Other properties and accessor methods
}

CompoundId/CompoundObject

@Embeddable
public class UserLikedPhotoId  implements Serializable {
    @ManyToOne
    private UserProfile user;
    @ManyToOne
    private Photo photo;
    //Other Properties and accessor methods.
}

和包含CompoundObject和日期的类

  @Entity
    @AssociationOverrides({
            @AssociationOverride(name = "pk.userId", joinColumns = @JoinColumn(name = "userId")),
            @AssociationOverride(name = "pk.photoid", joinColumns = @JoinColumn(name = "photoId")) })
    public class UserLikedPhoto{
        @EmbeddedId
        private UserLikedPhotoId pk = new UserLikedPhotoId();
        @Column
        @Temporal(TemporalType.DATE)
        private Date date;
           //Other Entities and accssor methods
    }

用这个类。生成的表类型为

------------------------------
| date |    UserId   photoId |
-----------------------------
| 2010-12-23  | 1   | 23 |
| 2010-12-21  | 2   | 23 |
| 2010-12-23  | 1   | 24 |
| 2010-12-21  | 5   | 23 |

现在我要做的是获得投票最多的照片(可能是给定日期的前5或前10)，在示例中，投票最多的照片是照片号23。第二名是第24名。

在Hibernate中，我将如何对这种任务进行查询?

Answer 1

还没试过…但是试一试……如果不工作，我将删除这个答案

select photoId, count(photoId) from UserLikedPhoto group by photoId order by count(photoid) desc

在这个查询中，我们按照片id分组，因此只有1行对应1张照片id。

这个照片id在UserLikedPhoto中出现的次数将告诉我们该照片的点赞数…

我们用desc来订购，所以最像的会排在最前面。你问你想先要5个还是10个…所以你可以在select语句

中使用top 10或top 5 SQL子句

所以最终的SQL将是这样的…

select top 10 photoId, count(photoId) from UserLikedPhoto group by photoId order by count(photoid) desc

Hibernate也支持本地sql

Answer 2

如果没有测试，从一开始就很难做到正确，但这里是我的尝试:

Query q = session.createQuery("select p from 
    Photo as p, 
    (select ulp.pk.photo.photoId as photoId from UserLikedPhoto ulp 
           where ulp.date = :someDate group by ulp.pk.photo.photoId 
                 order by count(ulp.pk.photo.photoId) desc) as top 
    where p.photoId = top.photoId");
q.setParameter("someDate", new Date())
q.setMaxResults(10);
List<Photo> topPhotos = q.list();