0

I have two objects. Object2 has lookup relationship to Object1. I want to write a SOQL query to fetch some data of Object1 where the territory field of Object2 is equal to UK.

    Set<Id> preDefinedSet = new Set<Id>();
    List<Object1> obj1 = [
        SELECT field1, field2, field3 
        FROM Object1 
        WHERE field1 IN :preDefinedSet 
        AND
       ( SELECT territory FROM Object2 WHERE territory == 'UK' )
    ];

1 Answer 1

1

I see 3 options here. The one you are trying to achieve.

    Set<Id> preDefinedSet = new Set<Id>();
    List<Object1> obj1 = [
        SELECT field1, field2, field3 
        FROM Object1 
        WHERE field1 IN :preDefinedSet 
        AND Id IN
       ( SELECT lookupObject1 FROM Object2 WHERE territory == 'UK' )
    ];

Second query for the Object2 and return values from Object1.

    Set<Id> preDefinedSet = new Set<Id>();
    List<Object1> obj1 = [
        SELECT lookupObject1.field1, lookupObject1.field2, lookupObject1.field3 
        FROM Object2 
        WHERE lookupObject1.field1 IN :preDefinedSet 
        AND territory == 'UK'
    ];

It is possible that this will return multiple same Object2 thou. So maybe better to group the Object2 by the lookup field, but be aware that this is limited to 2000 lines returned.

    Set<Id> preDefinedSet = new Set<Id>();
    List<Object1> obj1 = [
        SELECT lookupObject1, max(lookupObject1.field1), max(lookupObject1.field2), max(lookupObject1.field3) 
        FROM Object2 
        WHERE lookupObject1.field1 IN :preDefinedSet 
        AND territory == 'UK'
        Group By lookupObject1
    ];
4
  • Thanks for the answers. In query3, why did you use the max() function? does it help in returning more than 2000 records?? Mar 9, 2022 at 11:04
  • no that is a limit from salesforce, grouped result are limited to 2000
    – Damecek
    Mar 9, 2022 at 11:52
  • the max function is required because you can display only the grouped fields or functions. developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/…
    – Damecek
    Mar 9, 2022 at 11:57
  • 1
    Thanks. If I go with the first approach (the one I'm trying to use), will that be ok? Mar 9, 2022 at 12:27

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .