0

I have table UserPer which contains columns like uid1, uid2 ...

What I want to do is like this

Select uid1, (select name from user where id = up.uid1) from UserPer up

How do I achieve this using SOQL?

1 Answer 1

0

Assuming uid1 is a custom field in the UserPer object (so is actually uid__c) you want to do this...

select Id, uid1__r.Name from UserPer

uid1__r represents the relationship between the two objects and (obviously) is specific to SOQL/apex.

It is a regular point of confusion. The __r doesn’t represent the custom field, that is __c. When you create object relationships in SF it automatically creates this ‘relationship pointer’ __r that you can reference to reach across into the related parent record. The tricky bit is that you have to remember the conventions used for how you reference parent records vs child records.

Querying fields from child records is even weirder - it uses the __r as well, but because you are on the parent object and reaching out for the child records (note plural) the convention is that the relationship pointer is pluralized and it is the object name that is used as the relationship name i.e UserPers__r. And then the query is changed so that a looks like a subquery:

select Name, (Name from UserPers__r) from User

Note the pluralized UserPer object and the relationship suffix. If it were a standard object it would be pluralized but without the __r.

3
  • great, thanks so much Mar 8, 2021 at 1:10
  • one more question, if you are available (sorry). The field I have is actually named uid1__c . If I do this : uid1__c__r.Name -> error, it must be uid1__r.Name, Could you explain to me why we need to remove the __c ? I understand __c is for custom field but I don't get it why we can change the name of a field .... Mar 8, 2021 at 2:39
  • expanded the main answer - hope that helps
    – edralph
    Mar 8, 2021 at 7:26

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .