I'm developing the chat feature in my app. The problem occurs when I try to retrieve a Chat object from my database, all the messages are retrieved duplicated twice.
Project structure
This is the simplified structure followed:
And this are the entities (simplified):
@Entity
public class Chat {
@Id
@GeneratedValue
private Long id;
@ManyToMany
@JoinTable(
name = "user_chat",
joinColumns = @JoinColumn(name = "chat"),
inverseJoinColumns = @JoinColumn(name = "user")
)
private Set<User> users = new HashSet<>();
@OneToMany(mappedBy = "chat", cascade = CascadeType.ALL)
private List<Message> messages = new ArrayList<>();
public void addMessage(Message message){
this.messages.add(message);
}
}
@Entity
public class Message {
private @Id @GeneratedValue Long id;
@ManyToOne
@JoinColumn(name="senderId", nullable=false)
private User sender;
@Column(name="value")
private String value;
@ManyToOne()
@JoinColumn(name = "chat")
private Chat chat;
}
@Entity
public class User implements UserDetails {
private @Id
@GeneratedValue
Long id;
@OneToMany(mappedBy = "sender", cascade = CascadeType.ALL)
private List<Message> messagesSent;
@ManyToMany(mappedBy = "users")
private Set<Chat> chats = new HashSet<>();
}
And this is the repository which is failing:
public interface ChatRepository extends JpaRepository<Chat,Long> {
@Query("SELECT c FROM Chat c JOIN c.users u WHERE u.id IN :usersIDs GROUP BY c HAVING COUNT(DISTINCT u) = :numberOfUsers")
Optional<Chat> getChat(@Param("usersIDs") List<Long> usersIDs, int numberOfUsers);
}
The problem
Here you can see an example of how the object is retrieved:
As you can see, every Message object is duplicated
What I suppose the problem is
Researching and executing the raw SQL query to the DB, I come across with the assumption that the problem can be this: Hibernate is making the query this way: Join between Chat and Users and then Join between the result and Messages. This gives messages duplicated because it joins each message with Chat-User1 and Chat-User2 rows. Just this way:
SELECT message.id as messageID, chat.id as chatID, user.id as userID FROM chat JOIN user JOIN message;
What I have tried until here
In response of the assumption I explained before, I tried changing the repository query this way:
public interface ChatRepository extends JpaRepository<Chat,Long> {
@Query("SELECT c FROM Chat c JOIN c.users u LEFT JOIN c.messages m ON m.sender = u AND m.chat = c WHERE u.id IN :usersIDs GROUP BY c HAVING COUNT(DISTINCT u) = :numberOfUsers")
Optional<Chat> getChat(@Param("usersIDs") List<Long> usersIDs, int numberOfUsers);
}
Query breakdown:
SELECT c FROM Chat c JOIN c.users u Chat and Users joined
LEFT JOIN c.messages m ON m.sender = u AND m.chat = c Join between the result and messages
WHERE u.id IN :usersIDs Filtering just requested users
GROUP BY c HAVING COUNT(DISTINCT u) = :numberOfUsers") Retrieving the Chat which has all the users requested
As you can see, the difference lies in the ON m.sender = u AND m.chat = c. This way, the Messages must also join the User, like in this query:
SELECT message.id as messageID, chat.id as chatID, user.id as userID FROM chat JOIN user JOIN message ON message.sender_id = user.id and message.chat = chat.id;
and the LEFT before the JOIN is for getting the users who hasn't send message to the chat but are members.
However, this query is not solving the problem. Returns the Chat with the messages duplicated as well.
This is a list of other things I have tried:
- Adding
@Fetch (FetchMode.SELECT)beforeprivate List<Message> messages = new ArrayList<>(); - Using
@Transactionalbefore the query - Overriding
equals(Object o)inMessage
This is how the message.equals() method looks like:
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass()) return false;
Message message = (Message) o;
return Objects.equals(id, message.id) && Objects.equals(sender, message.sender) && Objects.equals(value, message.value) && Objects.equals(chat, message.chat);
}
@Override
public int hashCode() {
return Objects.hash(id, sender, value, chat);
}
Other SO posts which I have read
JPA - EntityManager find method returns duplicates
Multiple fetches with EAGER type in Hibernate with JPA
Updates
In response on what @EdmarSM have told me in the comments, I have modified my query like this:
@Query("SELECT DISTINCT c FROM Chat c INNER JOIN FETCH c.users u LEFT JOIN FETCH c.messages m WHERE u.id IN :usersIDs GROUP BY c HAVING COUNT(DISTINCT u) = :numberOfUsers")
However, I've received the next error:
Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'close.m1_0.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
I can see that this error is produced because of the INNER JOIN FETCH part. Without it, there is no error.
I also tried using the query without that part, just adding the DISTINCT to the query I've proposed above.
@Query("SELECT DISTINCT c FROM Chat c JOIN c.users u LEFT JOIN c.messages m ON m.sender = u AND m.chat = c WHERE u.id IN :usersIDs GROUP BY c HAVING COUNT(DISTINCT u) = :numberOfUsers")
But the response was exactly the same, the Chat with duplicated messages.
Second update
Again, according with @EdmarSM has told me, I tried with this:
public interface ChatRepository extends JpaRepository<Chat,Long> {
@Query("SELECT DISTINCT c FROM Chat c INNER JOIN FETCH c.users u LEFT JOIN FETCH c.messages m WHERE c.id IN (SELECT c2.id FROM Chat c2 INNER JOIN c2.users u2 WHERE u2.id IN :usersIDs GROUP BY c2 HAVING COUNT(DISTINCT u2) = :numberOfUsers)")
Optional<Chat> getChat(@Param("usersIDs") List<Long> usersIDs, int numberOfUsers);
}
I thought it has solved my problem but it hasn't. It just changed the way the messages were duplicated. Before, it used to be this way:
AABBCCDDEE...
And with this implementation, the pattern is ABCDEABCDE
In other words, it use to be:
- Message 1
- Message 1
- Message 2
- Message 2
- Message 3
- Message 3
- Message 4
- Message 4
And now is:
- Message 1
- Message 2
- Message 3
- Message 4
- Message 1
- Message 2
- Message 3
- Message 4
