Apex Code Development You need to sign in to do that
Don't have an account?
AdamSBeal SOQL Help
I have a custom object called Server which has related list of Accounts (many accounts per server). I am trying to build a list of the accounts from a visual force controller doing something like this:
public List<Account> getAccounts() {
if(accounts == null) accounts = [SELECT (SELECT Id FROM Accounts__r) FROM Server__c WHERE Id = server.Id];
return accounts;
}
A couple of problems here the first being it doesn't like the server.Id line in the SOQL. The controller has a Server set and there is a value it just doesn't like the syntax I am using. I am wondering if someone could tell me how to put a dynamic value like that in SOQL like this above.
My second issue is I get back an account related list but need to populate that into a list of accounts. Any thoughts on that? The syntax I have doesn't work I get an error saying "Illegal assignment from LIST<Server__c> to LIST<Account>
Thanks!
public List<Account> getAccounts() { return [SELECT Id FROM Account WHERE Server__c = :server.Id AND Server__c != NULL]; }Or, if you're trying to be clever and optimize your query usage:
public Account[] accounts { get { if(accounts==null) { accounts = [SELECT Id FROM Account WHERE Server__c = :server.Id AND Server__c != NULL]; } return accounts; } private set; }Here's some optimization tips:
1) Don't declare a memory variable you'll never use. To illustrate the point:
Internally, you create a new object in memory (say, 0x00001), which is a list of Accounts with no entries. The next line creates a NEW object in memory (say, 0x00002), which contains the results of the query, and 0x00001 is eventually discarded by the garbage collector (a.k.a. GC). I'm not sure what the performance penalty is for doing it this way, but I do know it's more efficient to not make objects that will never be used.
2) Don't use a sub-query when a normal query will suffice. This point:
This counts as TWO queries, one for the parent, and one for the child. Internally, salesforce.com has to restructure the query to find this information, which looks something like:
As you can imagine, this isn't nearly as efficient.
3) Don't query more than once, unless you have to.
This is outlined in my example code, where it is "lazy queried" at the last possible moment. This works assuming that Server is set before accounts is accessed for the first time, which I presume it would be as long as you didn't implicitly initialize it. Of course, you could instead just query this data in the constructor if you wanted to do it that way, as well (this would probably be ideal in most cases, as it localizes initialization to a single function). If Server__c can change, of course, then you'll need some additional logic to detect changes to the field and re-query as appropriate (in this case, you could just set accounts to null, and the next access would re-query the list).
All Answers
I figured out the first part had to do this:
WHERE Id = :server.Id];
Still not sure how to pull the relatedlist items out of the SOQL to work with them though.
You can pull them into another list. Remember, the way you have this written, you will have a list of Accounts for each server ID, since you have the query equal to one id, you will only have one list.
You will need to do something like this:
public List<Account> getAccounts() { //holder for server list List<Server__c> tmpServer = new List<Server__c>(); if(tmpServer == null){ tmpServer = [SELECT (SELECT Id FROM Accounts__r) FROM Server__c WHERE Id = server.Id]; } return tmpServer.Accounts__r; }Jim when I try this code:
public List<Account> getAccounts() {
//holder for server list
List<Server__c> tmpServer = new List<Server__c>();
if(tmpServer == null){
tmpServer = [SELECT (SELECT Id FROM Accounts__r)
FROM Server__cWHERE Id = :server.Id];
}
return tmpServer.Accounts__r;
}
which made sense I get this error (on the return line) which I don't quite understand:
Save error: Initial term of field expression must be a concrete SObject: LIST<Server__c>
public List<Account> getAccounts() { return [SELECT Id FROM Account WHERE Server__c = :server.Id AND Server__c != NULL]; }Or, if you're trying to be clever and optimize your query usage:
public Account[] accounts { get { if(accounts==null) { accounts = [SELECT Id FROM Account WHERE Server__c = :server.Id AND Server__c != NULL]; } return accounts; } private set; }Here's some optimization tips:
1) Don't declare a memory variable you'll never use. To illustrate the point:
Internally, you create a new object in memory (say, 0x00001), which is a list of Accounts with no entries. The next line creates a NEW object in memory (say, 0x00002), which contains the results of the query, and 0x00001 is eventually discarded by the garbage collector (a.k.a. GC). I'm not sure what the performance penalty is for doing it this way, but I do know it's more efficient to not make objects that will never be used.
2) Don't use a sub-query when a normal query will suffice. This point:
This counts as TWO queries, one for the parent, and one for the child. Internally, salesforce.com has to restructure the query to find this information, which looks something like:
As you can imagine, this isn't nearly as efficient.
3) Don't query more than once, unless you have to.
This is outlined in my example code, where it is "lazy queried" at the last possible moment. This works assuming that Server is set before accounts is accessed for the first time, which I presume it would be as long as you didn't implicitly initialize it. Of course, you could instead just query this data in the constructor if you wanted to do it that way, as well (this would probably be ideal in most cases, as it localizes initialization to a single function). If Server__c can change, of course, then you'll need some additional logic to detect changes to the field and re-query as appropriate (in this case, you could just set accounts to null, and the next access would re-query the list).