tags:

views:

130

answers:

0

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.