+ Start a Discussion
AxxxVAxxxV 

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.

 

 

SuperfellSuperfell
Owner on account doesn't appear to be polymporhic, so you should be able to reference all the fields on user without any problem. If accounts is your driving table, then you can do this instead of the child query approach you have.

select id, name, otherAccountField__c from account where owner.somefield = 'something'

this is considerable more efficient than driving from user and using child queries.
AxxxVAxxxV
Simon, thanks for the quick response.

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

SuperfellSuperfell
There's a couple of issues with this.
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.
AxxxVAxxxV
Thanks Simon,

Your answer pretty much confirms what I expected after multiple trials and errors...


Regards,
Andrey
AxxxVAxxxV
One more question before closing this thread:

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.
sfdcfoxsfdcfox
The only real limit of SOQL is the 10,000 character limit. IN is more compact than OR, suggesting that you'd have better results using IN instead of OR. Salesforce optimizes each of the queries before attempting to sift through the data, so either method should have the same timings (eg. both should be equally efficient), but one will allow you to query more users at once.

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 ~