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)
Note: The ...
is there because 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.