Obtaining a list of objects from Hibernate given a friend relationship in another table

Question

I reference this post as part of the attempt to solve the problem, although the end product is highly computationally expensive.

I have the following entities (truncated for simplicity)

@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
}

I am trying to obtain the most recent post by a certain user and his friends. The query for a user is trivial as shown below:

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();

However, obtaining the list of Posts by the friends and users seems to be impossible with a single query. My best effort attempt is repeating for each friend as per the message above (ie: get friends from the friend table which is also fairly trivial) and then sorting the merged list by the post date once again, which is something I would want to avoid since it is really slow.

Sample code

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

Is there a way to improve on this solution?

score 0 · Answer 1 · answered Mar 26 '21 at 10:22

You could use

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

Obtaining a list of objects from Hibernate given a friend relationship in another table

1 Answers1