SOQL Query on key value pair?

Question

How do I query on object 'c' when I have an Id from 'a' and and Id from 'b' as lookups in 'c'? I need to return the exact record in c that has both fields in 'a' and 'b'.

Query on both lists does not work because it would miss values. Do I need to use a wrapper to get both Ids as a key value pair? OR some kind of AR query?

Tried:

List<objectC> listC = [SELECT Id FROM objectC WHERE Id IN listB AND Id IN ListA];

Maybe there is another way to build a map using key value pairs or some other type that I could prepare a new list on to query exactly what I want. Not sure.

Answer 1

Using your example query as a guide to what you are trying to do. Looks like you want to query for ObjectC records that has its Id in both ListA and ListB?

What if you used the retainAll method of sets to combine the lists into a one set where each id was in both lists then use that in the query

You could do something like this - example only, narrow down to your code (using strings for example but you could use Ids):

string[] a = New String[]{'A', 'B', 'C', 'D'};
String[] b = New String[]{'A', 'D'};

Set<String> c = New set<String>(a);
c.retainAll(b);
System.debug(c);

19:42:20.16 (17938935)|USER_DEBUG|[6]|DEBUG|{A, D}

Then use the final set in your query.

List<objectC> listC = [SELECT Id FROM objectC WHERE Id IN :{FINALSET}];

Answer 2

Query will look like this where ObjectAId__c and ObjectBId__c are the lookup fields points for ObjectA and ObjectB

And ObjectAId__c and ObjectBId__c must exist both in the returned result.

List<objectC> listC = [SELECT Id, ObjectAId__c, ObjectBId__c FROM objectC__c 
                        WHERE ObjectAId__c IN:ListA 
                        AND ObjectBId__c IN:ListB
                        AND ObjectAId__c <> NULL 
                        AND ObjectBId__c <> NULL];

Answer 3

Just to show the code @eric and if this helps anyone. There is likely a better and more efficient solution so if anyone has it let me know but this is what I did. Checked the debug log and this only return the exact records that fit both Ids:

pseudocode:

List<DetailFromParent__c> oldList = [SELECT Id, IsDeleted, Name, Parent__c, User__c FROM DetailFromParent__c WHERE Parent__c = 'a010n000000QzBZ' LIMIT 1];
System.debug('oldList: ' + oldList);
List<Id> parents = New List<Id>();
List<Id> users = New List<Id>();

for (DetailFromParent__c m : oldList) {
    parents.add(m.Parent__c);
    users.add(m.User__c);
}
Set<Id> recordsToAdd = New Set<Id>();
Set<Id> recordsToKeep = New Set<Id>();

List<Parent__Share> parentShared = [SELECT Id, UserOrGroupId FROM Parent__Share WHERE ParentId IN :parents];
List<Parent__Share> usersShared = [SELECT Id, UserOrGroupId FROM Parent__Share WHERE UserOrGroupId IN :users];

for (Parent__Share ps : parentShared) {
    recordsToKeep.add(ps.Id);
}
for (Parent__Share ps : usersShared) {
    recordsToAdd.add(ps.Id);
}
recordsToKeep.retainAll(recordsToAdd);
System.debug('RecordsToKeep: ' + recordsToKeep);