Apex Code Development You need to sign in to do that
Don't have an account?
AxxxV SOQL: Retrieving Account data filtering by User fields based on Account.OwnerId relationship
Please help me figure out the
following seemingly trivial SOQL task:
I need to retrieve certain fields from Account object while filtering by fields
from User object based on Account.OwnerId relationship. Conceptually the SOQL statement
should look something like this:
Select Id, (Select Account.Owner.Name, Account.Owner.SomeotherField)
FROM User
WHERE User.SomeField = 'Something'
My problem is that I can't figure out how to reference the User fields through
Account.OwnerId relationship. It appears that User object is different in a way
that when you look at describeObject in ApexExplorer, the relashionship name
for Account.Owner is not specified.
I suspect that this may have something to do with polymorphic keys, but I
just can't connect all the dots here.
The main objective is to retrieve all the data in one SOQL statement as I am
dealing with very large volume of records.
Your help is greatly appreciated.
select id, name, otherAccountField__c from account where owner.somefield = 'something'
this is considerable more efficient than driving from user and using child queries.
I oversimplified that issue in my first post, so I here are some more details that will explain why this is not as straight forward, if at all possible:
My schema is as follows:
I have three objects that are relevant
1. User is a parent object
2. Account child of the user (based on the OwnerId relashionship in my scenario)
3. User_child__c a custom object that is also a child of User
The schema definition is given.
Objective is to retrieve (in one call) Accounts based on the filter criteria such as User_child__c.somefield = 'something'
This SOQL statement gets me the User fields. What I can't figure out is how to get User.Accounts fields as a nested query here...
SELECT uc.Id, uc.Name, uc.Owner.Name FROM User_child__c uc Where uc.myField__c = 'something'
I Need something like statement below, but none of the syntax I have tried worked:
SELECT uc.Id, uc.Name, uc.Owner.Name (SELECT somefields FROM uc.Owner.Accounts) FROM User_Child__c uc Where uc.myField__c = 'something'
Where (SELECT somefields FROM Owner.Accounts) is based on the Account.OwnerId relationship to the User. Again, when I looked at the describe information on the Account, this relashionship does not have name. This seems to be issue the in my case...
Message Edited by AxxxV on 05-22-2007 11:04 PM
1) as user_child is a child of user, there can potentially be multiple user_child rows per user, so you can't start from user, which is what you'd need to be able to query user child objects.
2) the reverse relationship from user to the set of accounts you own is not traversable through SOQL-R.
Internally, we'd describe the query you want to do as "join up then down" which we don't support as it basically results in a cartisean join SQL query.
You're going to have to break it up, one query to get you a list of userIds, then a second query to get the account data.
Your answer pretty much confirms what I expected after multiple trials and errors...
Regards,
Andrey
In a query like this, what is the best way to minimize the number of calls and increase efficiency?
SELECT somefields FROM Account WHERE Account.Owner = "UserId"
1. Use IN clause (UserId1, UserId2, UserId3.....)
2. Use OR operator (Account.Owner = 'UserId1' OR Account.Owner = 'UserId2' etc....)
What is the maximum number of parameters allowed in either IN or OR case? I did not find this in the SOQL documentation.
What is likely to be more efficient, executing a distinct query for each userId, or batch them as suggested above?
Is there any other way to make this as efficient as possible?
Thank you.
I've not heard of any other limitations... there's not even an "ApiFault" that describes a query as "too complex", suggesting that you should never get such a message. If you do get some off-the-wall error, be sure to contact Support.
~ sfdcfox ~