How to optimize soql query?

Question

I have this user case where a community user needs to see the appointments of all the contacts in his parent account.Appointment has a lookup relationship to contact. Right now, in the first soql query I am getting the contactid of the user.

myContact=[select id,email, from contact where id =:loggdinUserRecord.contactid];

Then I am running a query to get the account id of that contact:

con=[select account.name,account.id from contact where id=:myContact.id];

and in the third and fourth query, I am getting the contact names for each of those accounts and the appointment for each of those contacts.

Is this how it's supposed to be or can I optimize the soql queries to make it more efficient?

Answer 1

Without seeing all the queries involved, it's hard to say. But very likely you can optimize them using a Left Inner Join. You would basically get your second query for free as far as governor limits are concerned. It would look something like:

List<Appointment__c> appointments = [
    SELECT ... FROM Appointment__c WHERE Contact__c IN (
        SELECT Id FROM Contact WHERE AccountId = :myContact.AccountId
    )
];

No need to store sibling Contact records in memory, nor consume queries/query rows to get them. Don't forget to add AccountId to your query on myContact!

Another approach that ought to work here is a Right Inner Join, which would just look at the AccountId:

List<Appointment__c> appointments = [
    SELECT ... FROM Appointment__c WHERE Contact__r.AccountId = :myContact.AccountId
];

Answer 2

If you want to access the fields of Contact and Appointment__c and assuming the relationship between those two is 1 to many:

Id accountId = [
        select AccountId
        from Contact
        where Id = :loggdinUserRecord.contactid
        ].AccountId;
for (Contact c : [
        select Name, ..., (select Name, ... from Appointments__r order by Name)
        from Contact
        where AccountId = :accountId
        order by Name
        ]) {
    // Reference Contact fields e.g. c.Name
    for (Appointment__c app : c.Appointments__r) {
        // Reference Appointment fields e.g. app.Name
    }
}

(Trying to cut this down to one top level query hits the limitation that "The inner and outer selects should not be on the same object type".)

To see what is available in SOQL, review e.g. A Deeper look at SOQL and Relationship Queries on Force.com.