Dynamic SOQL Field Names in Query !

SObject row was retrieved via SOQL without querying the requested field

This might be a smaller error which you can easily address. But, there are challenges imposed when in an Query we need to add many fields to be retrieved. For Example:

Problem: 

Say, You are building a dynamic query in which you have to extract more than 30 fields in the query. Added problem is there are a couple of Objects for which dynamic query is created.

or 

You want to remove this [SObject row was retrieved via SOQL without querying the requested field] Error permanently from your code-life. 

Solution: 

Just copy & paste the code shown below in your utility method.




During creation of query when you require to access all field names of any object just call the sober static method by passing the API Name of the Object as parameters : 

string query = BuildQueryUtil.fieldNames('Contact') + 'FROM Contact WHERE Name != null LIMIT 100 '
List<Contact> extractedContacts = Database.Query(query);

This works as a charm & results:  

SELECT phone, jigsaw, mailinglatitude, otherstate, leadsource, lastactivitydate, createdbyid, otherphone, description, isdeleted, level__c, systemmodstamp, assistantphone, isemailbounced, otherstreet, languages__c, otheraddress, fax, hasoptedoutofemail, createddate, ownerid, hasoptedoutoffax, canallowportalselfreg, jigsawcontactid, lastvieweddate, lastcuupdatedate, credit_status__c, email, donotcall, othercity, lastmodifiedbyid, mailingstate, reportstoid, photourl, department, lastcurequestdate, lastname, otherlongitude, ispersonaccount, lastmodifieddate, id, mailinglongitude, mailingcountry, mobilephone, mailingaddress, title, lastreferenceddate, email_2_other_one__c, otherlatitude, emailbounceddate, name, birthdate, mailingstreet, homephone, accountid, emailbouncedreason, masterrecordid, otherpostalcode, mailingpostalcode, firstname, assistantname, othercountry, salutation, mailingcity FROM Contact WHERE Name != null LIMIT 100

You can also extend this into nested queries to get the field Names of the Child Records.

Note: This might not serve as a Best Practice as it increases the Heap Size {Heap Size have Governor Limit } & can degrade the performance. More, Do not use if you are building packages. Use it in specific cases when you need all fields and the size of the field names is lesser than 20,000.


Happy Coding!

The Story of 'ASC' & 'DESC' in SOQL

Recently , I was trying to implement the masterpiece of JavaScript & Jeff Douglas > Dynamic Search Functionality which was getting parameters and searching the records in real time.

I was doing well but then I stuck at a issue pointed by my MIS head stating that Sorting doesn't work for the Null records and I was asked to fix this so that whenever we sort on the basis of a column , the records with null values should not come over first , they should be placed in last.

I was sorting on the basics of Registration Number but I was not getting all the null records in first. It was killing me and my time.

I was using database.query to give get the records sorted when we click on label Registration Number:-

SELECT  Id, Reg_Num FROM Courses__c order by Reg_Num DESC.




So I carved out a path with the the best friend of all developers "Google".

Keyword : NULLS LAST

SELECT  Id, Reg_Num FROM Courses__c order by Reg_Num DESC NULLS LAST

Here is what I got the explanation...


The Default Sorting of NULLs is DBMS dependent. Some of them sort at the end and some at the beginning. Salesforce does it in beginning. So, only way to ensure this is to use NULL FIRST/LAST if the DBMS supports it.

In standard SQL (and most modern DBMS like Oracle, PostgreSQL, DB2, Firebird, Apache Derby, HSQLDB and H2) you can specify keyword NULLS LAST or NULLS FIRST.

The usage of isnull() or other functions is a workaround for the missing support for NULLS.

So , I wrote this down so that I could save someone's life and important time which he would spend while consulting the best buddy "Google".

Hope this writing helps you in the thing which you were looking for.

Happy Sorting !