Dynamically create SOQL query from sobject

Question

I have an SObject say Account which has some not null fields. I want to select all the accounts matching those values. Is there a way I can create a dynamic SOQL query for this.

Suppose the Account object is like this:

Account anAccount = new Account();
anAccount.Name = "sample"
anAccount.CustomName__c = "custom sample"

//Match all account matching above criteria
List<Account> matchingAccounts = [SELECT id from Account where Name=:'sample' AND CustomName__c=:'custom sample'];

I want to make above query dynamically using anAccount instance.

Answer 1

You need to build a dynamic SOQL query using the object metadata on the object itself.

Using these methods you can get and set methods on an object dynamically as well as get the metadata objects. You can loop through the field metadata to get field names and test the object to see if they are not null. If not, include them in the query.

The general idea is to build the query by selecting the fields you want from the object, then dynamically building the WHERE. Loop through the fields on the object and call Object value = anAccount.get(fieldName) to see the contents. If not null, add the field to the where: 'AND ' + fieldName + ' = \'' + value + '\''

For a more robust example, see the describeSObjects() documentation.

Answer 2

Sure! You can use DML database methods but you have to escape the single quotes. There is a bunch of code on this page for creating a dynamic search app.

You would use something like the following:

List<Account> matchingAccounts = Database.query('SELECT id from Account where Name = \''+String.escapeSingleQuotes(your-account-name-var)+'\'' AND CustomName__c = \''+String.escapeSingleQuotes(your-customer-name-var)+'\''');