Salesforce SOQL relationship query question

Question

I want to get the field userhomepage from the custom table WebsiteUser via a SOQL query on the Account table. I tried about 10 different queries but i'm not getting it working...

fe. I've tried SELECT field1, (SELECT userhomepage FROM User) FROM Account with all the __c and __r combinations.

I've got the following structure:

<complexType name="Account">
 <complexContent>
  <extension base="ens:sObject">
   <sequence>
    ...
    <element name="WebsiteUser__c" nillable="true" minOccurs="0" type="tns:ID"/>
    <element name="WebsiteUser__r" nillable="true" minOccurs="0" type="ens:WebsiteUser"/>

And the WebsiteUser table has a string field called userhomepage. How do I put that in a query? i'm completely stuck, thnx in advance!

Answer 1

Maybe a child-to-parent style query will work.

Try this?

SELECT Account__r.field1, userhomepage FROM WebsiteUser__c

Relevant-looking documentation:

http://www.salesforce.com/us/developer/docs/api/index_Left.htm#StartTopic=Content/sforce_api_calls_soql.htm

Answer 2

I'm assuming that WebsiteUser__c has a lookup to Account which is what I believe the snippet you've posted is showing, however that doesn't look like the standard object XML so I'm not 100% on where it's coming from.

A lookup from WebsiteUser__c to account creates a Many Website User to One Account relationship. If you were selecting from the Website User table you'd do something like:

select Id, UserHomePage__c, Account__r.Name
from WebsiteUser__c where some conditional

Querying the other way around requires a subquery:

select Id, Name, (select Id, UserHomePage__c from WebsiteUser__r)
from Account

This will return an Account with a list of all WebsiteUser__c records which are associated with it, you could run through the results like so:

for(Account sAcct : select  Id, Name,
                            (select Id, UserHomePage__c from WebsiteUser__r)
                    from    Account limit 200)
{
    for(WebsiteUser__c sUser : sAcct.WebsiteUser__r)
    {
        System.Debug(sUser.UserHomePage__c);
        // etc.

Some things to watch out for are that WebsiteUser__r might be a plural, i.e. WebsiteUsers__r, and if you've tried all combinations and it's not working, check that you didn't put in __c or __r yourself into the API object name, the system does this automatically so you'd end up with fields ending in __c__c or __r__r.

If you say what information you have to base the query on I might be able to make this answer a little bit more specific for you!

Answer 3

SELECT id, (SELECT UserHomePage__c FROM WebsiteUser__r) FROM ACCOUNT

if websiteUser__r is the child relation

SELECT id, WebsiteUser__r.UserHomePage__c FROM ACCOUNT

if websiteUser__r is the parent relation

it seems like a parent relationship to me so I think the later query would work...

Hope that helps