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
Sushant RaoSushant Rao 

Dynamic SOQL with an IN Clause and map.KeySet()

I have a dynamically generated soql statement. At the end of the statement I add 'AND Contract_Member__c ... 'and I get this error... System.QueryException: unexpected token: '{'  

 

String corpMemberQuery = 'SELECT ID, Corporate_Contract__r.Name, Contract_Member__c FROM Corp_Contract_Member__c WHERE Is_Active__c = true';

 

Database.query(corpMemberQuery+
                    ' AND Contract_Member__c IN ('+memMap.keySet()+')' ))

 

Any ideas on

1. If i can use keyset() in an 'IN' clause, keySet() returns a set??

2. Can I cast a Set to a List ??

Best Answer chosen by Admin (Salesforce Developers) 
lnryanlnryan

I was actually just working on this and there's a simpler way, which will spare you iterating through the map to put quotes around all the elements. 

 

You can use the same IN :keys notation that works in non-dynamic SOQL. The catch is you can't directly reference the memMap in the quoted string, you first have to assign it to a set...see the example below which i've verified runs successfully in ex-anon.

 

MAP<ID,Opportunity> os = new MAP<Id,Opportunity>([Select ID from Opportunity]);
SET<ID> keys = os.keyset();
String s = 'SELECT  RecordTypeId, COUNT(Id) os FROM Opportunity Where Id IN ';
s+=':keys GROUP BY RecordTypeId';
AggregateResult[] ars = Database.query(s);
System.debug(ars[0].get('RecordTypeId'));

 

To respond to your other question, use the addAll() method to cast lists to sets and vice versa - but note above that the colon (:) notation works with either lists or sets, so it's strictly not necessary.

 

ID[] listIDs.addAll(memMap.keyset());

 

All Answers

MandyKoolMandyKool

Hi,

 

Your "memMap.keySet()" returns the keyset which looks something like this {00390000001jibcAAA,00390000001jibdAAA}.

 

And for the IN clause to work the you will need to provide the IDs like this

{'00390000001jibcAAA','00390000001jibdAAA'} --- (with single quotes around each ID).

 

So you will need to write a method which will create the string of IDs, which are enclosed in single quotes.

Then you can use that string in your "IN" clause.

 

Hope this will solve your problem.

 

Thanks,

Mandar.
lnryanlnryan

I was actually just working on this and there's a simpler way, which will spare you iterating through the map to put quotes around all the elements. 

 

You can use the same IN :keys notation that works in non-dynamic SOQL. The catch is you can't directly reference the memMap in the quoted string, you first have to assign it to a set...see the example below which i've verified runs successfully in ex-anon.

 

MAP<ID,Opportunity> os = new MAP<Id,Opportunity>([Select ID from Opportunity]);
SET<ID> keys = os.keyset();
String s = 'SELECT  RecordTypeId, COUNT(Id) os FROM Opportunity Where Id IN ';
s+=':keys GROUP BY RecordTypeId';
AggregateResult[] ars = Database.query(s);
System.debug(ars[0].get('RecordTypeId'));

 

To respond to your other question, use the addAll() method to cast lists to sets and vice versa - but note above that the colon (:) notation works with either lists or sets, so it's strictly not necessary.

 

ID[] listIDs.addAll(memMap.keyset());

 

This was selected as the best answer
Sushant RaoSushant Rao

Thanks! I'll try it ...
Sushant RaoSushant Rao

Worked like a charm, although it kinda still doesn't make sense, since keySet() is returning a set anyway why do I need to declare a new variabnle for it ?

 

Set<ID> keys = memMap.keySet(); 

 

Database.query(localCorpMemberQuery +' AND Contract_Member__c IN :keys' )
SoozeeSoozee
kulkarni wrote:

Hi,

 

Your "memMap.keySet()" returns the keyset which looks something like this {00390000001jibcAAA,00390000001jibdAAA}.

 

And for the IN clause to work the you will need to provide the IDs like this

{'00390000001jibcAAA','00390000001jibdAAA'} --- (with single quotes around each ID).

 

So you will need to write a method which will create the string of IDs, which are enclosed in single quotes.

Then you can use that string in your "IN" clause.

 

Hope this will solve your problem.

 

Thanks,

Mandar.

Can you show an example of a method that will create a string of IDs, which are enclosed in single quotes?