function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
sooraj kesavadassooraj kesavadas 

How to write optimized soql query?

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];
 The 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?
Thanks
 
Best Answer chosen by sooraj kesavadas
Glyn Anderson 3Glyn Anderson 3
Now that I think about it, you can do it in one query and keep the Appointments organized by Contact.  This gives you a list of all the Contacts in the User's Contact's Account, and their Appointments as child records.

<pre>
List<Contact> contacts =
    [   SELECT  Id, FirstName, LastName,
            (   SELECT  Id, <other fields>
                FROM    Appointments__r
            )
        FROM    Contact__c
        WHERE   AccountId IN (SELECT Contact.AccountId FROM User WHERE Id = :loggedInUserRecord.Id)
    ];
</pre>

All Answers

Glyn Anderson 3Glyn Anderson 3
I think you can reduce this to two queries using a semi-join.  See if this works:

<pre>
Contact userContact = [SELECT AccountId FROM Contact WHERE Id = :loggedInUserRecord.ContactId];
List<Appointment__c> appointments =
    [   SELECT  Id, <other fields>
        FROM    Appointment__c
        WHERE   Contact__c IN
                (   SELECT  Id
                    FROM Contact
                    WHERE AccountId = :userContact.AccountId
                )
    ];
</pre>
 
Glyn Anderson 3Glyn Anderson 3
Now that I think about it, you can do it in one query and keep the Appointments organized by Contact.  This gives you a list of all the Contacts in the User's Contact's Account, and their Appointments as child records.

<pre>
List<Contact> contacts =
    [   SELECT  Id, FirstName, LastName,
            (   SELECT  Id, <other fields>
                FROM    Appointments__r
            )
        FROM    Contact__c
        WHERE   AccountId IN (SELECT Contact.AccountId FROM User WHERE Id = :loggedInUserRecord.Id)
    ];
</pre>
This was selected as the best answer