Getting a record's sibling records in SOQL with a single query

Question

I've looked high and low and cannot find an answer to this question.

Lets say I have a custom object called Appointment__c which is a child of the Case object, I have a VisualForce page that is launched on the Appointment level. At the moment, the visual force page retrieves the Appointment Id and retrieves case Info. I have a separate query that retrieves the appointments using the case Id. My question is, is there any way to retrieve the case's appointments using only the original appointment Id?

For example, if I have Case 1 with Appointment A, B, C and the VisualForce page controller is instantiated with Appt A, is there any way to directly retrieve A, B, and C from A in one query without first having to query for Case 1's ID and then using another query to retrieve Appointments for case 1?

Answer 1

You don't need to query the Case ID, but you do need to query the related appointments. Simply add this code in your Visualforce:

<apex:outputText rendered="false" value="{!Appointment__c.Case__c}" />

Visualforce can automatically retrieve the parent record's fields if you include it in the markup. For example, if you also wanted the case's case number:

<apex:outputText rendered="false" value="{!Appointment__c.Case__r.CaseNumber}" />

Then in your controller's constructor:

Appointment__c record = (Appointment__c)stdController.getRecord();
Appointment__c[] relatedAppointments = [SELECT ... FROM Appointment__c WHERE Case__c = :record.Case__c];

Answer 2

You can do it in two ways

• By adding the required field in the markup and not displaying it. That way, the field that you need will be automatically picked up by the standard controller. And, then you can use Case__c field to query other related appointments.

• By adding the required field using addFields method in the constructor of the controller. Using this way, the field will be included in the standard controller.

Using Mark-up

Visualforce page

<apex:outputText rendered="false" value="{!Appointment__c.Case__c}" />
<!--Add other fields that are needed-->

Controller

Appointment__c app = (Appointment__c)stdController.getRecord();
List<Appointment__c> otherAppointments = [SELECT field1, field2.. FROM Appointment__c WHERE Case__c = :app.Case__c];

Using addFields

Controller

if (!Test.isRunningTest()) stdController.addFields(new List<String>{'Case__c'}); //can add other fields that are needed
Appointment__c app = (Appointment__c)stdController.getRecord();
List<Appointment__c> otherAppointments = [SELECT field1, field2.. FROM Appointment__c WHERE Case__c = :app.Case__c];