Salesforce with Ankit

In this article, we will discuss about different SOQL and some examples.

1. Introduction –
   • SOQL stands for Salesforce Object Query Language, and it is query language provided by Salesforce to enable us to retrieve data from Salesforce.
   • Within SOQL, there are no DML operations, as in Apex DML is performed using DML statements.
   • SOQL is bound by governor limits and developer is limited to making 100 queries within single transaction and returning across the queries, maximum of 50000 row data.
   • SOQL is not run itself against the database but passes through Force.com query optimizer execution.
2. SOQL Statement Structure –
   • SOQL query in its most basic form has the following format:
     • SELECT fieldList FROM sObject { WHERE filterConditions } { LIMIT maxRowsToRetrieve }
   • We can run query using query editor from taskbar in developer console.
3. Executing SOQL in Apex –
   • Generally, data retrieved from query is stored in List of that sObject.
   • For example: List of Accounts where AnnualRevenue is greater than 1000000.
     • List < Acccount > accs = [ SELECT Id, Name, AnnualRevenue FROM Account WHERE AnnualRevenue > 1000000 ];
4. Aggregate Functions & Grouping –
   • Aggregate functions are numeric functions, that run on a particular field to return a statistic about the values of that field in given set of records that meet some query criteria.
     • COUNT ( ) : These are possibly the most commonly used aggregate functions and returns the number of records meeting the defibed criteria.
       • Example:
       • SELECT COUNT ( Id ) FROM Account
     • If we wanted to get the average annual revenue for our accounts it would be:
       • SELECT AVG ( AnnualRevenue ) FROM Account
   • When working with aggregate functions, the returnType of our query changes from List < sObject > to List < AggregateResult >
   • AggregateResult behaves like map with key of type String.
   • Example:
     • List < AggregateResult> results = [ SELECT COUNT ( Id ), AVG (AnnualRevenue ) FROM Account ] ;
   • We can use grouping in combination with our aggregate functions, to enable us to make our statistics more granule for our records.
   • To add grouping we append, GROUP BY fieldName speciefied.
   • List < AggregateResult > accounts = [ SELECT COUNT ( Id ) FROM Account WHERE AnnualRevenue > 1000000 GROUP BY COUNTRY ] ;
5. Data from Parent Record –
   • Within SOQL, we can retrieve data from parent records by using the name of the lookup or master-detail field defined in records.
   • Example:
     • SELECT Id, firstName , LastName , AccountId FROM Contact ]
   • For standard relationships like Account & Contact, we can access parent record by parent object name. For Example:
     • [SELECT Id, FirstName, Account.Name, Account.Id FROM Countact ];
     • For Opportunity & Invoice ( custom object ), it works like following:
       • [ SELECT Name, Opportunity__r.Name FROM Invoice__c ];
6. Data from Child Record –
   • We can retrieve list of child record as part of the query result using sub-query.
   • Example:
     • [ SELECT Id, Name, { SELECT FirstName, LastName FROM Contacts } FROM Account ];
   • For custom objects:
     • [ SELECT Name , { SELECT Id, Amount__c FROM Invoices__r) FROM Opportunity ] ;
7. Dynamic Query –
   • We can run a query defined as a string using the Databbase.query method.
   • For Example:
     • Database.query ( ‘SELECT FirstName, LastName FROM Contact ‘ );
   • Dynamic query will open for attack using SQL injection, to avoid this it is preferred to use static query.

Thank you for reading till last, in next article we will discuss about SOSL and some examples.