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
RobJCowellRobJCowell 

SOQLBuilder and pseudo-dynamic queries

So I'm using Richard Vanhook's SOQLBuilder from the apex-lang code library and I'm trying to migrate my string-concatenation SQL code to the new approach.  The problem I'm hitting is when I need to add WHERE clauses dynamically depending on values set elsewhere.

 

Consider the following :-

 

companyquery = 'SELECT Event_to_Deal__r.Client_Company__r.ID FROM Event_Costs__c WHERE ';
            if (market != 'All')
                companyquery +='Event_to_Deal__r.LNOperation__c = \'' + market + '\'';
            
            if (industry != 'All')
            {
                if (companyquery.substring(companyquery.length()-6) != 'WHERE ')
                    companyquery += ' AND ';
                companyquery += 'Event_to_Deal__r.Client_Company__r.Industry__c = \'' + industry + '\'';
            }   
            
            if (sector != 'All')
            {
                if (companyquery.substring(companyquery.length()-6) != 'WHERE ')
                    companyquery += ' AND ';
                companyquery += 'Event_to_Deal__r.Client_Company__r.Sectors__c = \'' + sector + '\'';    
            }
            
            if (companyquery.substring(companyquery.length()-6) != 'WHERE ')
                    companyquery += ' AND ';
            companyquery += 'Event_to_Deal__r.Status__c = \'Current\'';

 Standard trickery for building these in strings, but how can you do the same kind of thing in SOQLBuilder?  It doesn't seem to support being interrupted while you do an if statement, nor does it seem to be concatenatable.

 

Thanks

 

Rob

Best Answer chosen by Admin (Salesforce Developers) 
Richard Vanhook (Salesforce)Richard Vanhook (Salesforce)
Build where clause prior to building entire soql:
String market = 'Foo';
String sector = 'Bar';
String industry = '123';

NestableCondition whereClause = new AndCondition().add(new FieldCondition('Event_to_Deal__r.Status__c').equals('Current'));
if(market != 'All')   whereClause.add(new FieldCondition('Event_to_Deal__r.LNOperation__c').equals(market));
if(industry != 'All') whereClause.add(new FieldCondition('Event_to_Deal__r.Client_Company__r.Industry__c').equals(industry));
if(sector != 'All')   whereClause.add(new FieldCondition('Event_to_Deal__r.Client_Company__r.Sectors__c').equals(sector));

String companyquery = new SoqlBuilder()
	.selectx('Event_to_Deal__r.Client_Company__r.ID')
	.fromx('Event_Costs__c')
	.wherex(whereClause)
	.toSoql();

System.debug(companyquery);

All Answers

Richard Vanhook (Salesforce)Richard Vanhook (Salesforce)
Build where clause prior to building entire soql:
String market = 'Foo';
String sector = 'Bar';
String industry = '123';

NestableCondition whereClause = new AndCondition().add(new FieldCondition('Event_to_Deal__r.Status__c').equals('Current'));
if(market != 'All')   whereClause.add(new FieldCondition('Event_to_Deal__r.LNOperation__c').equals(market));
if(industry != 'All') whereClause.add(new FieldCondition('Event_to_Deal__r.Client_Company__r.Industry__c').equals(industry));
if(sector != 'All')   whereClause.add(new FieldCondition('Event_to_Deal__r.Client_Company__r.Sectors__c').equals(sector));

String companyquery = new SoqlBuilder()
	.selectx('Event_to_Deal__r.Client_Company__r.ID')
	.fromx('Event_Costs__c')
	.wherex(whereClause)
	.toSoql();

System.debug(companyquery);
This was selected as the best answer
RobJCowellRobJCowell

Awesome, many thanks :manvery-happy: