function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Bahtiyar IshkabulovBahtiyar Ishkabulov 

Filter SOQL query by multiple fields

Hi everyone,
How to filter a SOQL query by two or more fields (key fields)? For example let's say I have an object with three fields: Person (String), Year (String) and Income (Number). Person and Year are key fields. And there are four records in the object:

Person                    Year                       Income
'Jhon'                      '2016'                      1000
'Jhon'                      '2017'                      950
'Mike'                      '2016'                       1100
'Mike'                      '2017'                       900

Then I want to retrieve data about Jhon's income in 2016 and Mike's income in 2017
In some SQL implementations I can do it in such way: 
Select Person, Year, Income From MyObject Where (Person, Year) In (('Jhon', '2016'), ('Mike', '2017'))
What about SOQL? I was told that it won't accept such a query. Is there any means? Or I have to use "And" filter separately for every field and then use a loop?
Best Answer chosen by Bahtiyar Ishkabulov
Glyn Anderson (Slalom)Glyn Anderson (Slalom)
Create a formula field on MyObject that concatenates Person and Year in some way, perhaps with a pipe, '|'.  Then you can do something like this: 
Set<String> personYears = new Set<String>{ 'John|2017', 'Mike|2016' };

List<MyObject> records =
[   SELECT  Person, Year, Income
    FROM    MyObject
    WHERE   PersonYear IN :personYears
];

All Answers

Steven NsubugaSteven Nsubuga
Select Person, Year, Income From MyObject Where Person In ('Jhon', 'Mike') AND Year IN ('2016', '2017'))

 
Raj VakatiRaj Vakati
Select Person__c, Year__c, Income__c From MyObject__c Where Person__c In ('Jhon', 'Mike') AND Year__c IN ('2016', '2017'))

 
Bahtiyar IshkabulovBahtiyar Ishkabulov
Steven Nsubuga, Raj V,
It won't work. Your query will return all four records, while I need only 2 of them (the first and the last).
Bahtiyar IshkabulovBahtiyar Ishkabulov
When I wrote "... Or I have to use "And" filter separately for every field..." in the end of my question, I meant exactly such a query. But I need some other one.
Raj VakatiRaj Vakati
Select Person__c, Year__c, Income__c From MyObject__c Where (Person__c='Jhon' AND Year__c='2017') OR ( Person__c='Mike' AND Year__c='2016')

 
Bahtiyar IshkabulovBahtiyar Ishkabulov
Raj V,
Yes, I'm asking exactly how to put these conditional statements to lists (using "in" operator). Because it's just in my example there are only two filter pairs and it's not difficult to write them both directly in the query. But if there are 2000 filter pairs this method won't be possible at all.
I mean, if there were only one field, it would be as simple as: 
Select Person, Income from MyObject Where Person in :pList
But I have two fields to match: Person and Year. So I need something like: 
Select Person, Year, Income From MyObject Where (Person, Income) in :myList
And myList shoud contain other lists (massives) of two values: {{'Mike', '2017'}, {'Jhon', '2016'}...} 
Glyn Anderson (Slalom)Glyn Anderson (Slalom)
Create a formula field on MyObject that concatenates Person and Year in some way, perhaps with a pipe, '|'.  Then you can do something like this: 
Set<String> personYears = new Set<String>{ 'John|2017', 'Mike|2016' };

List<MyObject> records =
[   SELECT  Person, Year, Income
    FROM    MyObject
    WHERE   PersonYear IN :personYears
];
This was selected as the best answer
Bahtiyar IshkabulovBahtiyar Ishkabulov
Glyn Anderson,
Yep, I did it just the same way now (but instead "|" used "_"). So this is the only possible way I guess?
P.S. Instead of formula field I made it just as a regular text field and fill it in with Field Update Workflow Action. This let me to make the field unique, so now I have a key of two fields.
Glyn Anderson (Slalom)Glyn Anderson (Slalom)
This is the only way I have come up with so far in my 6 years experience with Salesforce.  This also means you have to create a different key formula for every compound key that you might need -- PersonYear, PersonIncome, IncomeYear, etc.  I like the Field Update idea to get uniqueness.  Consider doing the Field Updates in a Process Builder Flow -- Salesforce is discouraging Workflow Rules these days...
Shane KenyonShane Kenyon
Another good option to do this is to populate a custom string field for your dual key via a trigger and make it an External ID so it is indexed.  It seems that with SF you cannot have more than one LIKE statement in your SELECT query, is this confirmed?
Kunal Alandkar 9Kunal Alandkar 9
You can do in following way :
  
Map<String, String> personYearMap =  new Map<String, String>() ;
personYearMap .put( 'Jhon', '2016' );
personYearMap .put( 'Mike', '2017');

List<MyObject > myObjectList  = Database.query (   'Select Person, Year, Income From MyObject  Where '  
                                                  + getRecordByTwoFields( 'Person', 'Year' ,  personYearMap ));

/* where getRecordByTwoFields() given below which return String  " (Person = 'Jhon' AND Year ='2017') OR ( Person='Mike' AND Year='2016') " dynamically. */

public static String getRecordByTwoFields( String keyField,  String valueField, Map<String, String> mapKeyValue ) {
    String whereBody = '';
    for ( String key : mapKeyValue.keySet() ) {
       whereBody = whereBody + ' ( ' + keyField + ' = \'' + key + '\' AND ' + valueField + ' = \''+          mapKeyValue.get( key ) + '\' ) OR' ;
    }
    whereBody += whereBody.removeEnd('OR');
    return whereBody;      
}

// in case of jhon  with different year ex : Jhon => 2018 , john => 2019 then use  Map<String, List<String>>

public static String getRecordByTwoFields( String keyField,  String valueField, Map<String, List<String>>  mapKeyValue ) {
    String whereBody = '';
    for ( String key : mapKeyValue.keySet() ) {
       for ( String val :  mapKeyValue.get( key ) )  {
            whereBody = whereBody + ' ( ' + keyField + ' = \'' + key + '\' AND ' + valueField + ' = \''+ val + '\'  ) OR' ;
        }
    }
    whereBody += whereBody.removeEnd('OR');
    return whereBody;      
}