How to get all elements not present in a @ManyToMany Relationship-CodePudding

I am trying to fetch all channels where UserAccount is not present in as admin or follower (-> is not in subscribedUsers & admins collection).

Those are my entities:

public class UserAccount {
    @Id ... 
    private long userId;
    
    @JsonIgnore
    @JoinTable(
      name="user_channel_sub",
      joinColumns = @JoinColumn(name                 
            ="user_channel_id",referencedColumnName="userId"),
        inverseJoinColumns = @JoinColumn(name =              
            "channel_user_id",referencedColumnName = "channelId"))
    @ManyToMany
    private Collection<Channel> subChannel = new ArrayList<Channel>();

    @JoinTable(
            name="user_channel_admin",
            joinColumns = @JoinColumn(name = 
                "user_channel_id",referencedColumnName= "userId"),
            inverseJoinColumns = @JoinColumn(name = 
                "channel_user_id",referencedColumnName = "channelId"))
    @ManyToMany
    private Collection<Channel> adminChannel = new ArrayList<Channel>();

    ....
}

public class Channel {
    @Id
    private channelId;

    @ManyToMany(mappedBy="adminChannel")
    private Collection<UserAccount> admins  = new ArrayList<UserAccount>();
    
    @ManyToMany(mappedBy = "subChannel")
    private Collection<UserAccount> subscribedUsers  = new ArrayList<UserAccount>();

    ....
}

Repository is a PagingAndSortingRespository.

I get all channels a user is either follower or admin in by this method (I dont really need it, just wanted to make sure at least something works):

Collection<Channel> findBySubscribedUsers_UserName_OrAdmins_UserName(String uName1,String uName2);

Now when I try the opposite, I get duplicates and yeah basically wrong data:

Collection <Channel> findBySubscribedUsersUserNameNotAndAdminsUserNameNot(String uName1,String uName2);

What am I doing wrong? Is it with this method possible or do I need to use more advanced spring jpa querying methods?

CodePudding user response：

Possible with JPA see documentation https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.query-methods.query-creation

Using the findDistinct method and notContaining.

Collection<Channel> findDistinctByAdminsNotContainingAndSubscribedUsersNotContaining(UserAccount admin, UserAccount subscribedUser);