SOQL and Relationship Queries last part

Relationship Queries Part 1
Relationship Queries Part 2

Date Functions

SOQL has a list of functions that deal with dates which makes your life a lot easier.

1) CALENDAR functions: CALENDAR_MONTH, CALENDAR_QUARTER, CALENDAR_YEAR
2) DAYDAY : DAY_IN_MONTH, DAY_IN_WEEK, DAY_IN_YEAR, DAY_ONLY
3) FISCAL: FISCAL_MONTH, FISCAL_QUARTER, FISCAL_YEAR
4) HOUR function: HOUR_IN_DAY
5) WEEK function: WEEK_IN_MONTH, WEEK_IN_YEAR

SELECT Title FROM Position__c WHERE CALENDAR_MONTH(Date_Closed__c) = 2

 
Aggregate Results with GROUP BY and HAVING

GROUP BY

GROUP BY is used in conjunction with aggregate functions to group the result-set by one or more columns.

SELECT Position__r.Department__c deptname, COUNT(id) total FROM Job_Application__c GROUP BY Position__r.Department__c

As you can see this query displayed number of Job Applications grouped by Department.

This example would make it more clear.

List aggrs = [SELECT Position__r.Department__c deptname, COUNT(id) total FROM Job_Application__c
GROUP BY Position__r.Department__c];
for (AggregateResult ja : aggrs){
System.debug(ja.get('dept') + ' | ' + ja.get('total'));
}

 
HAVING

You can use HAVING clause along with GROUP BY clause to filter the results returned by aggregate functions. A HAVING clause is similar to WHERE clause. The difference is you can include aggregate functions in a HAVING clause, but not in WHERE clause.

Lets use the above example again

SELECT Position__r.Department__c deptname, COUNT(id) total FROM Job_Application__c GROUP BY Position__r.Department__c HAVING COUNT(id)>1

This would return Job Applications GROUP BY Department for which there are more than one Job Applications.

A deeper look at SOQL and Relationship Queries

Click here to refer basics of Relationship Query

SOQL is the object oriented language for querying data in Force.com platform. You can look at it as an object oriented version of SQL. Lets start by examining the kind of relationships that are available. There are two kinds of relationships Lookup Relationship and Master-Detail Relationship.

Lookup Relationship

1) Deletion of parent record does not effect child record. This does not support roll up summary.
2) Sharing rules of parent does not apply to child. This allow us to have more flexibility in building security model.
3) A relationship doesn’t have to exit in this model. To put it in more simple terms, the lookup field doesn’t need to be populated.

Master-Detail Relationship

1) Deletion of parent record results in cascade deletion. This relationship supports roll up summary.
2) Sharing rules of parent does apply to child.
3) Unlike lookup relationship child record cannot exist on its own. Again in simple terms, the lookup field needs to be populated.

Now lets dive deep in to the relationship queries. I am using the following relationship:

 
SOQL Joins

Right Outer Join

A Right Outer Join takes all the record from the right table and then pick up any matching records from the left table.
Select Name, Position__r.Department__c From Job_Application__c

This query will return job application along with related departments

we used Position__r.Department__c to create a Right join between Job_Application__c and Position__c.

Left Outer Join

A Left Outer Join takes all the records from the left table and pick up any matching records from the right.

Select Name, (Select Name From Job_Applications__r) From Position__c

This will return all position with their related list of applications

Left Inner Join

A Left Inner Join display records only when there is a match between both tables. It takes records from the left table and pick the matching records from the right.

Select Name From Position__c Where Id IN (Select Position__c From Job_Application__c)

This will return positions for which there are associated applications

Right Inner Join

A Left Inner Join display records only when there is a match between both tables. It takes records from the right table and pick the matching records from the right.

Select Name, Position__r.Name From Job_Application__c Where Position__r.Department__c=’Sales’

This returns all applications for positions that are in ‘Sales’ department.

Left Anti Join

This is opposite to Left Inner Join.

Select Name From Position__c Where Id NOT IN (Select Position__c From Job_Application__c)

This returns name of all positions which have no job applications.

Right Anti Join

This is opposite to Right Inner Join.

Select Name From Job_Application__c Where Position__c=null

This returns name of all applications for which there are no associated positions.

Relationship Queries in SOQL

Relationship queries helps you extract data from Salesforce database with minimum possible number of queries. Let me give you simple examples of all kinds of relationship queries you can write.
 
Basic Child to Parent

Select Id, Name, Account.Name From Contact

Account is the name of relationship thats defined by AccountId lookup field on contact object.

Expanded Child to Parent

Select Id, Name, Account.Parent.Name From Contact

This will retrieve the name of the parent account of the account associated with the contact

Basic Parent to Child

Select Id, Name (Select Name From Contacts) From Account

You know what this will return.

Combined Child to Parent and Parent to Child

Select Id, Name, Account.Name (Select Quantity, UnitPrice, TotalPrice, PricebookEntry.Name, PricebookEntry.Product2.Family From OpportunityLineItems) From Opportunity

VisualForce Email Template

VisualForce email templates came as a part of winte ’09 release which leverages on VisualForce technology to provide underlying framework for email templates.

You need to know three tags to create VisualForce email template

1) <messaging:emailTemplate> – in which you specify recipient type, relatedToType, subject and email address that they can reply back to.

2) <messaging:htmlEmailBody> – in which you define the html content you want to show in your Email.

3) <messaging:plainTextEmailBody> – in which you include the text version of your Email.

Let me show you a simple example of how to create a VisualForce email template. We are going to create an email template which sends email about contacts that are related to an account.

The first step is to create an email template by going to Setup -> Administration Setup -> Communication Templates -> Email Templates -> New Template


<messaging:emailTemplate subject="Contact Information for Account: {!relatedTo.name}" recipientType="Contact" relatedToType="Account" replyTo="sivateja.s@gmail.com">
<messaging:htmlEmailBody >
<html>
<body>
<p> Dear {!recipient.name},</p>
<p> Below is the list of contacts related to your account: {!relatedTo.name}.</p>
<table border="0">
<tr>
<th> Action </th>
<th> Contact Name </th>
<th> Contact Email </th>
</tr>
<apex:repeat var="con" value="{!relatedTo.Contacts}">
<tr>
<td> <a href="na7.salesforce.com/{con.id}"> View </a>
<a href="na7.salesforce.com/{con.id}/e"> Edit </a> </td>
<td> {!con.Name} </td>
<td> {!con.Email} </td>
</tr>
</apex:repeat>
</table>
</body>
</html>
</messaging:htmlEmailBody>
<messaging:plainTextEmailBody >
Dear {!recipient.name},

Below is the list of Contacts related to Account: {!relatedTo.name}.

[Contact Name] - [Contact Email]
<apex:repeat var="cont" value="{!relatedTo.Contacts">
[cont.Name]  -  [cont.Email]
</apex:repeat>

For more detailed information login to http://www.salesforce.com
</messaging:plainTextEmailBody>
</messaging:emailTemplate>

 

Last step is to test and verify merge fields. Click Send Test and Verify Merge Fields button to verify merge fields.

 

How to Sort Query Results in Salesforce

Sometimes you want to sort query results in ascending or descending order of the value of fields, keep null at the top or bottom etc., To achieve this, the keyword you must know is ORDER BY.

Examples

//This query will arrange the query results alphabetically by First Name

Select Name From Account ORDER BY FirstName

//This query will arrange the query results reverse-alphabetically by Last Name and then in reverse-alphabetically by First Name. If you observe in this query, we can sort based on multiple fields.

Select Name From Account ORDER BY LastName DESC, FirstName DESC

//This query will arrange query results showing null email records first

Select Name From Account ORDER BY Email NULLS FIRST

Note:
You cannot sort results based on Long text area or Multi-Select picklist field or Reference field
SOQL query sorting is case insensitive.
Maximum number of fields based on which you can sort is 32.