0

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.

2

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}];
0

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];
  • This is good but I don't think it would work for 1ks of records. I am sure I would have more than one where that is true. All that has to happen is the values in on list be in another and its going to match. If i could map it to the exact ID then yes. Explain to me if I'm missing something. I don't see how this will be certain that I only get the object c record that matches both criteria. – Andrew Mayes May 20 '17 at 23:49
0

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);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.