我引用这篇文章是为了解决这个问题,尽管最终产品的计算成本很高。
我有以下实体(为了简单起见,截断(
@Entity
public Post {
@Id
private Long id;
@ManyToOne
private Person author;
private String post;
@Temporal(TemporalType.TIMESTAMP)
private Date postDate;
//getter setters
}
@Entity
public Person {
@Id
private Long id;
private String name;
//getter setters
}
@Entity
public Friendship {
@Id
private Long id;
@ManyToOne
private Person owner;
@ManyToOne
private Person friend;
//getter setters
}
我正在尝试获取某个用户和他的朋友的最新帖子。对用户的查询是琐碎的,如下所示:
Query query = em.createQuery("SELECT p FROM Post p WHERE p.author = :user ORDER BY p.postDate DESC);
query.setParameter("user", user);
query.setMaxResults(50)
return query.getResultList();
然而,通过一个查询似乎不可能获得朋友和用户的帖子列表。我尽最大努力按照上面的消息为每个朋友重复(即:从朋友表中获取朋友,这也是相当琐碎的(,然后再次按发布日期对合并列表进行排序,这是我想避免的,因为它真的很慢。
样本代码
Query q1 = em.createQuery("SELECT F from Friendship f WHERE p.owner = :user or p.friend = :user");
q1.setParameter("user", user);
List<Friendship> friendshipList = q1.getResultList();
List<Person> friends = new ArrayList<>();
friends.add(user);
for (Friendship f : friendshipList) {
if (f.owner.equals(user)) {
friends.add(f.getFriend());
} else {
friends.add(f.getOwner());
}
}
List<Post> result = new ArrayList<>();
for (Person p : friends) {
Query query = em.createQuery("SELECT p FROM Post p WHERE p.author = :user ORDER BY p.postDate DESC");
query.setParameter("user", p);
query.setMaxResults(50)
result.addAll(query.getResultList());
}
//sort by date and return
有没有办法改进这个解决方案?
您可以使用
SELECT DISTINCT p FROM Post p
WHERE p.author IN
(SELECT f.owner from Friendship f WHERE f.owner = :user or f.friend = :user)
OR
p.author IN
(SELECT f.friend from Friendship f WHERE f.owner = :user or f.friend = :user)
ORDER BY p.postDate DESC