Help with JPQL query
- by Robert
I have to query a Message that is in a provided list of Groups and has not been Deactivated by the current user. Here is some pseudo code to illustrate the properties and entities:
class Message {
private int messageId;
private String messageText;
}
class Group {
private String groupId;
private int messageId;
}
class Deactivated {
private String userId;
private int messageId;
}
Here is an idea of what I need to query for, it's the last AND clause that I don't know how to do (I made up the compound NOT IN expression). Filtering the deactivated messages by userId can result in multiple messageIds, how can I check if that subset of rows does not contain the messageId?
SELECT msg FROM Message msg, Group group, Deactivated unactive
WHERE
group.messageId = msg.messageId
AND (group.groupId = 'groupA' OR group.groupId = 'groupB' OR ...)
AND ('someUserId', msg.messageId) NOT IN (unactive.userId, unactive.messageId)
I don't know the number of groupIds ahead of time -- I receive them as a Collection<String> so I'll need to traverse them and add them to the JPQL dynamically.
