80

Believe it or not bind variables actually work in dynamic SOQL: Map<Id,Account> accts = new Map<Id,Account>([select Id from Account]); Set<Id> accountIds = accts.keySet(); String q = 'select id from Contact where AccountId in :accountIds'; List<Contact> cts = Database.query(q); Note that you can't embed method calls (e.g. AccountId ...


29

There is no way to Select * with SOQL. You can however work-around this limitation by using dynamic SOQL and describe methods to get all of the fields on the object. This is described in detail in this post by Cory Cowgill: Building Dynamic SOQL - Select All Query


26

No no, the field (API) name is Schematic__c, you need to start from it. Think about Schematic__r (the rel. name) like a consistent table alias when you'd make JOINs in regular database, nothing more. To go "up": Schema.DescribeFieldResult f = Schema.sObjectType.Order_Line_Item__c.fields.Order__c; System.debug(f.getRelationshipName()); // Outputs "...


23

You can not use text area fields in SOQL and SOSL filter criterias. Try this approach: List<Project__c> projects = new List<Project__c>(); for(Project__c proj : [SELECT Name, Status__c, Description__c FROM Project__c WHERE Name LIKE '%' + String.escapeSingleQuotes(name) +'%' + OR Status__c = String.escapeSingleQuotes(status)]) { if(proj....


20

Please only answer when you found a way to do this without a method that manually constructs the IN Clause. I already found an even shorter, loopless solution, but I thought the auto-replacement done by Database.query() can simulated elsewhere. public String inClausify(Set<Id> ids) { String inClause = String.format( '(\'\'{0}\'\')', ...


19

You found the answer, dynamic SOQL is looking for your variable declaration within the scope of your method. I don't know why it doesn't respect public properties of the class, but that is the case and your solution of setting it to a method-scoped variable should do the trick.


18

Database.query(String) returns a List<sObject> rather than a List<Opportunity> like your query does. You need to cast the result of Database.query(String) before you can use it to populate a map using the Map<ID, sObject>(List<sObject>) constructor for a concrete sObject type. Here is an example using your code: Map <Id, ...


17

I created some helper classes for this. You can find them here. There are 3 classes: xt, xs and xr. They contain also some other tools I couldn't separate for this answer, but the method you need is xs.query() in xs.cls. To get all straight into your Org, just use the "Deploy to Salesforce" link. The usage is very simple. Just go somewhere to Execute ...


17

If you are using a data binding, you do not need to know. = will work in either case, so you don't need to ever switch to IN for the use case you outline above. I used the following code in Execute Anonymous to demonstrate: Object searchTerm = new Set<String> { 'test', 'test2' }; SObjectField field = Account.Name; SObjectType sObjectType = Account....


16

I suggest that rather than relying on some utility class, you become familiar with the platform's APIs so you can compose the appropriate solution each time you want to query all the fields, particularly if your queries are simple and only involve one object. Whoever gets to maintain the code in the future will thank you for keeping things simple and direct ...


15

You need to add all fields that you want access to to your SOQL query, this includes relationship fields. Even if you query for Agreement_CSH_to_be_completed_by__c You need to add the fields that you want to access in your query such as Agreement_CSH_to_be_completed_by__r.isActive If you update your SOQL, this should work fine EDIT in response to ...


14

This post by @ca_peterson explains you can directly use bind variables in dynamic soql Alternatively, you can use this utility method to convert a Set into a String, which can be used in Dynamic SOQL. //convert a Set<String> into a quoted, comma separated String literal for inclusion in a dynamic SOQL Query private String quoteKeySet(Set<...


13

You can actually include variable references in Dynamic SOQL strings, you just need to ensure there is a variable of that name and correct type in scope when your query is executed for example... public with sharing class DynamicSOQLDemo { public static String buildQuery() { return 'select Id, Name from Account where Id in :accountIds'; ...


13

You would want to use an AggregateResult query here. This is obviously just some shorthand code as I really don't know your object or field names so you will have to adjust those to fit in order for this to compile for you AggregateResult[] ARs = [SELECT count(Id) myCount, Cabin_Type__c, Deck_Type__c FROM cruise_ deck_Cabin_paln__c GROUP BY ...


13

Any time you use a literal String in a query, the value must be wrapped in single quotes. So in addition to removing the double AND, you need to wrap your value in quotes. If you debug the query, you will probably see SELECT Id, BillingPostalCode, County_Name__c FROM Account WHERE ZipCodeLen__c = 9 AND AND BillingCountry = United States Instead you ...


12

You have to use dynamic SOQL if you want to add fields dynamically. Merge syntax is not supported. List<String> fields = new List<String> { 'Id', 'Name' }; String soql = 'SELECT ' + String.join(fields, ',') + ' FROM MyObject__c WHERE ...'; List<MyObject__c> records = Database.query(soql); I actually often have dynamic filters as well when ...


11

Well some time back I wrote a blog regarding Dynamic SOQL. Here is excerpt about what you can do what you cant. You CANNOT use complex types in a Dynamic SOQL directly. This means that you cannot use any Sobject, Apex Class or Any other user defined data type inside the Dynamic Query. In short you cannot use a dot (".") operator to specify a field ...


11

I'm not 100% sure what makes it "groupable". The Considerations When Using GROUP BY does mention it: Some object fields have a field type that does not support grouping. You can't include fields with these field types in a GROUP BY clause. The Field object associated with DescribeSObjectResult has a groupable field that defines whether you can ...


11

As sfdcfox points out there isn't support for this natively in SOQL, however formula fields can in many cases be leveraged to do this type of work. Creating a new formula field on the account object of type checkbox with a formula of ISNUMBER(Name) should get you a new boolean column you can filter on for this effect. As formula field values are generated ...


11

Another alternative might be this: public static Boolean isCollection(Object input) { Object test; try{ test = (List<Object>)input; return true; } catch(System.TypeException ex){} return false; } This should work for any Object. Note that this does not work for Sets or Maps, which both return conversion errors ...


11

You might be able to accomplish this by executing the query given with an additional LIMIT 0 at the end. You'll still use one SOQL query, but I don't believe this will count against the total number of rows retrieved. Something like: string userQuery = 'select ID from Contact where Name like \'myContact%\''; try { Database.Query(userQuery + ' LIMIT 0')...


10

The easiest way is to use the array is as given below. List<String> operators = new List<String> {'AND','OR'}; soqlString = 'SELECT Name FROM ACCOUNT WHERE Name != null'; soqlString += ' AND Operator__c in :operators'; Apex #automagically converts the reference to collection variables in string query to the corresponding sequence of strings ...


10

I've just tried some apex to execute the Database.query() dynamically. The logic is to get the soql query from somewhere and put it to the Database.query() in some method that gets the set of id's at the same time (i hope i understand the question right). So i've created a list method that gets a soql as a string where the set name not specifically defined (...


10

In the describe for the Field there's a referenceTo property which indicates which type(s) the foreign key is for, e.g. system.debug(Schema.SObjectType.Contact.fields.AccountId.referenceTo[0].getDescribe().getName()); prints Account The docs have a section on the describe functionality


10

SOQL does not support concatenation operation like SQL. But here is work around for you. Select c.LastName, c.FirstName From Contact c WHERE name In ('Good Name','Bad Name') If above soql is for sample and you want to use this on some other fields then you can create formula field that will concatenate two fields and use that in your SOQL.


10

If you use simple bind variables in dynamic SOQL escaped quotes can be avoided (as can any danger of SOQL injection attack): String country = 'United States'; String soql = '' + ' SELECT Id, BillingPostalCode, County_Name__c' + ' FROM Account + ' WHERE zipCodeLen__c = 9' + ' AND BillingCountry = :country' ; for (...


10

Use an extra set variable set<Id> setAccountIds = new set<Id>(); Map<Id, Account> maps = new Map<Id, Account>([Select Id from Account]); setAccountIds.addAll(maps.keySet()); String s = 'Select Id from Account Where Id IN :setAccountIds '; List<Account> acc = Database.query(s); System.debug('@@' + acc);


10

Explanation The core problem here is that implicit type conversion from List<String> to String will cause truncation. It looks like this happens after the tenth element. You can see it for yourself by running the following script in Execute Anonymous: List<Integer> numbers = new List<Integer>(); for (Integer i = 0; i < 25; i++) ...


10

When you reference an apex variable in a query (standard, or dynamic as is your case), you need to prefix the variable name with a colon :. This is called expression binding. You can still use expression binding in dynamic soql. Though there are some additional considerations when using this with dynamic soql, I don't believe any of them apply in this ...


9

Have a look into Andrew Fawcett's Enterprise Design Patterns Dreamforce Session, especially the Data Mapper (Selector) part Source and links to the Video: https://github.com/financialforcedev/df12-apex-enterprise-patterns In addition check out https://github.com/apex-commons/SoqlBuilder (former apex-lang) on Github. Regarding your concerns governor ...


Only top voted, non community-wiki answers of a minimum length are eligible