Use text field with multiple values with , in SOQL where clause

Question

I have customobject with groups__c text field which hold id of different public groups Id with ","

groups__c = 0011U00000oyv2AQAQ,0011U00000oyv2BQAQ

I'm using this field in where clause

List<String> groupIds = new List<String>();
for(GroupMember gm :[SELECT Id,GroupId,UserOrGroupId FROM GroupMember WHERE UserOrGroupId = :userId]){
    groupIds.add(gm.GroupId);
}

for(CustomObj__c obj : [SELECT Id, Name FROM CustomObj__c where groups__c IN :groupIds])

here it returns records of CustomObj__c for only 0011U00000oyv2AQAQ UserOrGroupId

Is it possible to query so that it will return for all mention groupIds in groups__c field?

Answer 1

This is because where groups__c IN :groupIds will only return against hard matches of each single value, instead of checking against a LIKE operator. Simply change your groupIds.add(gm.GroupId); line to groupIds.add('%' + gm.GroupId + '%');, and your IN clause to a LIKE, and it should function as you expect.

List<String> groupIds = new List<String>();
for(GroupMember gm :[SELECT Id,GroupId,UserOrGroupId FROM GroupMember WHERE UserOrGroupId = :userId]){
    groupIds.add('%' + gm.GroupId + '%');
}

for(CustomObj__c obj : [SELECT Id, Name FROM CustomObj__c where groups__c LIKE :groupIds])