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
Austen RobinsonAusten Robinson 

Retrieve all the data by object ID in salesforce object using apex sql

How can you show all the data (not just the Id's) in a salesforce object using APEX SQL?

I tried this:
  
// without an ID, simply specify the object to then derive the sobject type
DescribeSObjectResult describeResult = Account.getSObjectType().getDescribe();

List<String> fieldNames = new List<String>( describeResult.fields.getMap().keySet() );

String query =
  ' SELECT ' +
      String.join( fieldNames, ',' ) +
  ' FROM ' +
      describeResult.getName()
;

// return generic list of sobjects or typecast to expected type
List<SObject> records = Database.query( query );

System.debug( records );

but get the error: The query has to start with find or select?

Where exactly does that go?

Getting very frustrated.  In my object I have 3 fields I want to extract.  Name, Account_Owner and Wholesale_Name.  Name and Accout_Owner are strings but Wholesale_Name is a reference.  So I want to return:

Name        Account Holder             Wholesale_name

ABC          John Doe                      ABC Company

 
Best Answer chosen by Austen Robinson
Austen RobinsonAusten Robinson
This worked:

Select Id, Wholesale_Account__r.Name From ACNA__c    thanks so much!!!!!

All Answers

Martijn SchwarzerMartijn Schwarzer
Hi Austen,

I've created something similar in the past. You will find the code below. it will return a query string with all the fields of the object, except the fields listed in the fieldSet variable. Add all fields you want to exclude to this set. 
//Remove the following standard fields from the to be created select query
Set<String> fieldSet = new Set<String>{'lastactivitydate', 'lastmodifiedbyid', 'lastmodifieddate', 'lastreferenceddate', 'lastvieweddate', 'createdbyid', 'createddate', 'isdeleted', 'systemmodstamp', 'ownerid'};
    	
Integer i = 0;	//Needed to determine first loop run

//Start building query
String query = 'Select ';
		
//Dynamically add all fields for sObject type to query
for(String fieldName : objectType.getDescribe().fields.getmap().keySet()){
	if(!fieldSet.contains(fieldName)){
		query += i > 0 ? ', ' + fieldName : fieldName;	//Handle first run differently.
		i++;
	}
}
//Add from clause
query += ' FROM ' + objectType.getDescribe().getName();
return query;
Please keep in mind that this code will produce errors if the API version of the class that contains this code is different from the API version of the class calling it.

For example, let's say that Salesforce introduced a new field in API v35. If the code listed above runs in version 34, that new field will not be retrieved. On the other hand, if the above code runs in v35 it will return the new field. But if the class that is calling this code is in v34, it will get the new field, but will not recognize it and produce an error.

So if you want to use this functionality, you will have to keep the API versions in sync.

Also, this is NOT a best practice. There is a reason that Salesforce does not support Select * From Object. Since your Salesforce org is part of a multi-tentant architecture, you share resources with other tenants. Querying fields you will not use is only using system resources that could otherwise be used by other tenants.

I would advise you to write SOQL Queries for each individual task, and reuse them whereever possible.

Hope this helps!

Best regards,
Martijn Schwärzer

Ps. If my answer helps you to solve your problem please mark it as best answer. It will help other to find best answer.
Chris ByromChris Byrom
So your code worked for me on a small object to generate the SOQL. It isn't going to give you the name from a lookup though. Assuming Wholesale_Name is a lookup field, you should be able to get the name by referencing the relationship. This would look something like Wholesale_Name__r.Name assuming the field name is Wholesale_Name__c. The __r refers to the related record.
Austen RobinsonAusten Robinson
Chris,  my Wholesale_Name__c field is a reference.  Tried changing the c to an r but of course it throws an error because of the field name.  A little more insight is needed for me.  Thanks
Chris ByromChris Byrom
Here is an example from my org. I have a custome object named Order__c. This object has a field of type Lookup(Account) named Primary_Account__c. If I want to get the name of the Primary Account in SOQL I can run this:
  
Select Id, Primary_Account__r.Name From Order__c Limit 1

This will return the Name of the Account in the Primary_Account__c field. The opposite of this would be looking up things from the Account. I would be looking for the list of Orders for the Account. I can do this a couple of ways. First if I want to do it directly from Orders I can do this:
  
Select Id From Order__c Where Primary_Account__c = 'Account ID'

If I am doing an account query I can do a subquery to reference the relationship like this:
  
Select Id, Name, (Select Id, Name From PrimaryAccountOrders__r) from Account Where ID = 'Account ID'

This one is a little weird. Notice the name of the relationship in this dircection is PrimaryAccountOrders__r. If you look at the definition of the lookup field, you can see the Relationship Name. In this case the Primary_Account__c field on my Order__c object has a relationship name of PrimaryAccountOrders. This name is set when the lookup field is createds. I hope this helps. For more info on SOQL Relationships see:

https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_relationships.htm
 
Austen RobinsonAusten Robinson
Chris--I tried this
  
Select Id, Name, (Select Id, Name, Wholesale_Account__c From Wholesale_Account__r) from ACNA__c Where ID = 'Account ID'

but I get an error message saying that it doesnt understand the relationship Wholesale_Account__r.  
Austen RobinsonAusten Robinson
I also tried this
  
Select Id, Name,Wholesale_Account__c (Select Id, Name, Wholesale_Account__c From Wholesale_Account__r) from ACNA__c Where ID = 'Account ID'

 
Chris ByromChris Byrom
OK, so ACNA__c is your main object right? Is Wholesale_Account__c a lookup field on that object? Does this work?
  
Select Id, Name,Wholesale_Account__c from ACNA__c Where ID = 'Account ID'

If so, how about this:
  
Select Id, Name from Wholesale_Account__c

 
Austen RobinsonAusten Robinson
ACNA__c is my main object, that is correct.  Wholesale_Account_c is a lookup field i.e. type reference.

The first query:  Select Id, Name,Wholesale_Account__c from ACNA__c Where ID = 'Account ID'

throws an error of:  Invalid ID field: Account ID

The second query:  Select Id, Name from Wholesale_Account__c

works but what is represented in the Wholesale_Account__c field is the unique Id for that field.  Is there some sort of transformation of going up or down a level(s) with __c to __r or vice versa?  Im so new to this (3rd day) Im so lost.

 
Austen RobinsonAusten Robinson
Also, how do you find what object the Wholesale_Account__c value is stored in?
Chris ByromChris Byrom
OK, 'Account ID' is meant as a placeholder for the ID of the object you are looking at. In this case you would enter the ID of the ACNA__c record you are looking at. If you open the field definition of Wholesale_Account__c on the ANCA__c object you will see the object it is related too. To see all of the ANCA__c records you can leave off the WHERE clause, and just have:
  
Select Id, Name,Wholesale_Account__c from ACNA__c

If that and the second query works,
  
Select Id, Wholesale_Account__r.Name From ACNA__c

should work. The __r tells SOQL that you want to reference the object related to the ID stored in that field.
Austen RobinsonAusten Robinson
This worked:

Select Id, Wholesale_Account__r.Name From ACNA__c    thanks so much!!!!!
This was selected as the best answer
Austen RobinsonAusten Robinson
One more question.  Is there a way to find out where a referenced field is used throughout all objects?
Chris ByromChris Byrom
The easiest way to see where an object is referenced is to look at the related lookup filters list on the object. It will show you all objects that have a relationship with the current object. You can also look at the fields on an object to see what object it is relating to. Please mark one of my answers as best answer if I solved your problem. Thanks!