+ Start a Discussion
ShaTShaT 

Self Join in SOQL

Hi ,

 

How to write query for self join and how can i use count to count the realted records for that .

 

 

Thanks

Shailu

Best Answer chosen by Admin (Salesforce Developers) 
sfcksfck

Hi,

 

Hope it's ok to jump in on this.

 

record.peoples__r is a list, not an sObject (although it may be a list that contains only one sObject).

 

You should be able to access the name field of the first person in the list by doing

 

record.peoples__r[0].name

 Or if you prefer, and you are certain there will only ever be one person in the list, you can explicitly cast it as a people object and then treat it as one:

people__c peop = record.peoples__r;
system.debug(peop.name);

Cheers

s

 

 

All Answers

Prafull G.Prafull G.

You can use aggregate functions to get record count.

 

Examples

Integer act = [select count() from Account];

ShaTShaT

Thanks for Your reply!!!

 

but i need count in self join

 

This is my query . How can i count the Peoples in the inner query.

Select (Select Name, Name__c, isManager__c From Peoples__r where isManager__c=true) From People__c p where p.isManager__c=true and id='a0HZ0000000lMkg'

 

 

Thanks

Shailu

 

Prafull G.Prafull G.

Ok.

Aggregate functions can only be used on Root queries i.e. you can not use these in inner query.

 

You can count the number as

 

People__c record = [Select (Select Name, Name__c, isManager__c From Peoples__r where isManager__c=true) From People__c p where p.isManager__c=true and id='a0HZ0000000lMkg'];

 

Now to get the inner count

Integer innerCount = record.Peoples__r.size();

 

If you want to do the same for more records then you can use for loop.

 

Let me know if this is what you are looking for.

ShaTShaT

Thanks that worked for me....

 

i want to get the data for inner query how can i retrive its records.

 

Eg- record.Peoples__r.Name . its not working

 

 

 

Thanks

 

Shaliu

sfcksfck

Hi,

 

Hope it's ok to jump in on this.

 

record.peoples__r is a list, not an sObject (although it may be a list that contains only one sObject).

 

You should be able to access the name field of the first person in the list by doing

 

record.peoples__r[0].name

 Or if you prefer, and you are certain there will only ever be one person in the list, you can explicitly cast it as a people object and then treat it as one:

people__c peop = record.peoples__r;
system.debug(peop.name);

Cheers

s

 

 

This was selected as the best answer
Prafull G.Prafull G.

Hi Shailu,

 

sfck mentioned the correct way to refer inner query results.

 

Inner Query return list of records. So you can process the record.Peoples__r as normal List<Peoples__c> and do whatever you need. i.e. itirating using for loop etc.

 

Please share it this is working for you.