Salesforce.com Queue SOQL

SOQL AND SOSL in Salesforce:

What is the difference between SOQL and SOSL?

The differences are mentioned in the table below:

SOQL (Salesforce Object Query Language)	SOSL (Salesforce Object Search Language)
Only one object can be searched at a time	Many objects can be searched at a time
Can query any type of field	Can query only on email, text or phone
Can be used in classes and triggers	Can be used in classes, but not triggers
DML Operation can be performed on query results	DML Operation cannot be performed on search results
Returns records	Returns fields

SOQL(Salesforce Object Query Language):

• SOQL is a Salesforce Object Query Language, retrieves the records from the database by using SELECT keyword.
• SOQL is used if we know in which object the data is present.
• In SOQL we can query data from single object and as well as multiple objects that are related to each other.
• We can query on all fields of any datatype.
• We can use SOQL in Triggers and classes.
• We can perform DML operation on query results.

SOSL(Salesforce Object Search Language):

• SOSL is a Salesforce Object Search Language, retrieves the records from the database by using the FIND keyword.
• SOSL is used if we don’t know in which object the data is present.
• We can retrieve multiple objects and field values efficiently when the objects may or may not be related to each other.
• We can query only on fields whose data type is text, phone and Email.
• We can use in classes but not in Triggers.
• We cannot perform DML operation on search result.

Specific Date:

SELECT Id

FROM Account

WHERE CreatedDate > 2005-10-08T01:02:03Z

Salesforce.com Queue SOQL

Queues allow groups of users to manage a shared workload more effectively. A queue is a location where records can be routed to await processing by a group member. The records remain in the queue until a user accepts them for processing or they are transferred to another queue. You can specify the set of objects that are supported by each queue, as well as the set of users that are allowed to retrieve records from the queue.

Queues available in almost all Salesforce.com edition, from: Contact Manager, Group, Professional, Enterprise, Unlimited, and Developer. Queues can be created for Leads, Cases, Knowledge article version, Service contract and custom object which not a child object in a master-detail relationship.

SOQL to query all queue:
Select g.Id, g.Name, g.Email from Group g where g.Type = 'Queue'

A queue can assign into one or  many objects, here SQOL to retrieve object assigned in a queue:
Select q.Id, q.QueueId, q.Queue.Name, q.SobjectType from QueueSobject q

Note: you can you Apex Data Loader to mass insert or mass update the data in Group and QueueSobject object.

How to get the AccountId Whose Contacts are more than 10.

SELECT Accountid, COUNT(Id) FROM Contact GROUP BY Accountid HAVING COUNT(Id) > 10

Sample GROUP BY Clause in SOQL

Sample SOQL query to fetch case count based on the owner.

SELECT Owner.name, COUNT(CaseNumber)
FROM Case
GROUP BY Owner.name

How To Write a SOQL To Fetch All Events Related to Account and Opportunity

SELECT Id,subject,What.Type,whatId 
FROM Event
WHERE What.Type IN ('Account', 'Opportunity')

How to get the Owner Name and Last Modified By Name in Task Object?

Below is the Query to get the Owner Name and Last Modified By Name in Task Object

SOQL:

select owner.name,LastModifiedBy.name from task where id =t.id 

How to query This Fiscal and Last Fiscal Year opportunities.

You can use THIS FISCAL YEAR and LAST FISCAL YEAR date values to get the opportunities.

list<opportunity> opplist =[SELECT Id,closedate FROM Opportunity WHERE CloseDate = THIS_FISCAL_YEAR limit 10];

system.debug('opplist-->'+opplist);

list<opportunity> opplist2 =[SELECT Id,closedate FROM Opportunity WHERE CloseDate = LAST_FISCAL_YEAR limit 10];

system.debug('opplist2-->'+opplist2); 

https://help.salesforce.com/apex/HTViewHelpDoc?id=custom_dates.htm&language=en

https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_dateformats.htm?language=en

What is SOQL in Apex?

Salesforce Object Query Language (SOQL) is a query-only language. While similar to SQL in some ways, it's an object query language that uses relationships, not joins, for a more intuitive navigation of data. This is the main query language that is used for data retrieval of a single sOobject and its related sObjects. 

SOQL Query Examples

A SOQL query is enclosed between square brackets.

sObject s = [SELECT Id, Name FROM Merchandise__c WHERE Name='Pencils'];

A SOQL statement is centered on a single database object, specifying one or more fields to retrieve from it.The fields to select are separated by commas.

Simple SOQL Statement

SELECT Id, Name FROM Account

Filtering Records

SOQL supports filter conditions to reduce the number of records returned.A filter condition

consists of a field name to filter, an operator, and a literal value.

Valid operators are

> (greater than),

< (less than),

>= (greater than or equal to),

<= (less than or equal to),

= (equal to),

!= (not equal to),

IN and NOT IN (matches a list of literal values, and supports semi-joins and anti-joins), and INCLUDES and EXCLUDES (match against multi-select picklist values).

On String fields the LIKE operator is also available,

which applies a pattern to filter records.The pattern uses the % wildcard to match zero or

more characters, _ to match one character, and the \ character to escape the % and _

wildcards, treating them as regular characters.

EX: -

SELECT Name

FROM Account

WHERE AnnualRevenue > 100000000

AND Type = 'Customer - Direct'

AND LastModifiedDate = THIS_YEAR

SOQL Statement with Record Limit

SELECT Name, Type

FROM Account

WHERE LastModifiedDate = TODAY

LIMIT 10

Sorting Query Results

Results of a query can be sorted by up to 32 fields in ascending (ASC, the default) or descending (DESC) order. Sorting is not case-sensitive, and nulls appear first unless otherwise specified (NULLS LAST).Multi-select picklists, long text areas, and reference type fields cannot be used as sort fields.

EX: -

SELECT Name, Type, AnnualRevenue

FROM Account

ORDER BY Type, LastModifiedDate DESC

Querying Multiple Objects

The result of a SOQL query can be a simple list of records containing rows and columns or hierarchies of records containing data from multiple, related objects. Relationships between objects are navigated implicitly from the database structure.

The two ways to navigate object relationships in SOQL are child-to-parent and parent-to-child.

SOQL with Child-to-Parent Relationship

EX: -

SELECT Name, Contact__r.MailingCity, Contact__r.CreatedBy.Name

FROM Resource__c

WHERE Contact__r.MailingState = 'IL'

At most, five levels of parent objects can be referenced in a single child-to-parent query, and the query cannot reference more than 25 relationships in total.

SOQL with Parent-to-Child Relationship

The second form of relationship query is the parent-to-child query.

EX: -

SELECT Id, Name,

(SELECT Total_Hours__c

FROM Timecards__r

WHERE Week_Ending__c = THIS_MONTH)

FROM Resource__c

Note: A parent-to-child query cannot reference more than twenty child objects.

SOQL Query in Apex Using SOQL For Loop

Decimal totalHours = 0;

for (Proj__c project : [ SELECT Total_Billable_Hours_Invoiced__c FROM Proj__c

WHERE Start_Date__c = THIS_YEAR ]) {

  totalHours += project.Total_Billable_Hours_Invoiced__c;

}

How to Read/Create record types in Apex test class?

We have some important consideration while creating test data in our test classes.

So let say if we need to create a record based on the record type id , what we do normally , we query on record type object based on SobjectType and the record type Name.

for example :

let say we have a Account Record Types A & B Not if we need to create a Account Record with record type A or B . We normally query like this .

RecordType rt = [select id,Name from RecordType where SobjectType='Account' and Name='A' Limit 1];

And now use this value further while creating the Account Record.

Account acc = new Account(Name='Test' , recordTypeId=rt.id);

To avoid this query in test class we can the same in some other manner 

Schema.DescribeSObjectResult cfrSchema = Schema.SObjectType.Account; 

Map<String,Schema.RecordTypeInfo> AccountRecordTypeInfo = cfrSchema.getRecordTypeInfosByName();

Now to get the recordTypeId we will have to use a method getRecordTypeId.

Id rtId = AccountRecordTypeInfo .get('A').getRecordTypeId(),

Now Use can insert Account Record like

Account Acc = new Account(Name='test',recordtypeid=AccountRecordTypeInfo .get('A').getRecordTypeId());

insert Acc;

How to check whether user has access to a particular record or not?

Query “userRecordAccess” to check whether has access to record or not.
For example:
SELECT RecordId, HasReadAccess, HasTransferAccess, MaxAccessLevel, HasAllAccess, HasDeleteAccess, HasEditAccess FROM UserRecordAccessWHERE UserId = “005xxxxxxxxx”AND RecordId = “001xxxxxxxx”
HasAllAccess Indicates whether a user has all access–read, edit, delete, and transfer—to the record (true) or not (false).
HasReadAccess, HasEditAccess , HasDeleteAccess ,HasTransferAccess return Boolean value.
MaxAccessLevel return access level like None, read, Edit,Delete,Transfer and All.

Considerations When Using GROUP BY

When you’re creating SOQL queries with the GROUP BY clause, there are some considerations to keep in mind.

• Some object fields have a field type that does not support grouping. You can't include fields with these field types in a GROUP BY clause. The Field object associated with DescribeSObjectResult has a groupable field that defines whether you can include the field in a GROUP BY clause.
• You must use a GROUP BY clause if your query uses a LIMIT clause and an aggregated function. For example, the following query is valid:

  1	SELECT Name, Max(CreatedDate)

  2	FROM Account

  3	GROUP BY Name

  4

  LIMIT 5

  The following query is invalid as there is no GROUP BY clause:

  1	SELECT MAX(CreatedDate)

  2	FROM Account LIMIT 1

Considerations When Using HAVING

When you’re creating SOQL queries with a HAVING clause, there are some considerations to keep in mind.

• A HAVING clause can filter by aggregated values. It can also contain filter by any fields included in the GROUP BY clause. To filter by any other field values, add the filtering condition to the WHERE clause. For example, the following query is valid:

  1	SELECT LeadSource, COUNT(Name)

  2

  FROM Lead

  3	GROUP BY LeadSource

  4	HAVING COUNT(Name) > 100 and LeadSource > 'Phone'

  The following query is invalid as City is not included in the GROUP BY clause:

  1	SELECT LeadSource, COUNT(Name)

  2

  FROM Lead

  3	GROUP BY LeadSource

  4	HAVING COUNT(Name) > 100 and City LIKE 'San%'

• Similar to a WHERE clause, a HAVING clause supports all the comparison operators, such as =, in conditional expressions, which can contain multiple conditions using the logical AND, OR, and NOT operators.
• A HAVING clause can't contain any semi- or anti-joins. A semi-join is a subquery on another object in an IN clause to restrict the records returned. An anti-join is a subquery on another object in a NOT IN clause to restrict the records returned.

Grouping By One Rollup Field

This simple example rolls the results up by one field:

1	SELECT LeadSource, COUNT(Name) cnt

2

FROM Lead

3	GROUP BY ROLLUP(LeadSource)

The following table shows the query results. Note that the aggregated results include an extra row with a null value for LeadSource that gives a grand total for all the groupings. Since there is only one rollup field, there are no other subtotals.

LeadSource	cnt
Web	7
Phone Inquiry	4
Partner Referral	4
Purchased List	7
null	22

GROUP BY CUBE

The following query returns subtotals of accounts for each combination of Type and BillingCountry:

1	SELECT Type, BillingCountry,

2	GROUPING(Type) grpType, GROUPING(BillingCountry) grpCty,

3	COUNT(id) accts

4	FROM Account

5	GROUP BY CUBE(Type, BillingCountry)

6	ORDER BY GROUPING(Type), GROUPING(BillingCountry)

The following table shows the query results. The query uses ORDER BY GROUPING(Type), GROUPING(BillingCountry) so that the subtotal and grand total rows are returned after the aggregated data rows. This is not necessary, but it can help you when you are iterating through the query results in your code. The Comment column explains each row.

Type	BillingCountry	grpType	grpCty	accts	Comment
Customer - Direct	null	0	0	6	Six accounts with Type = Customer - Direct withBillingCountry = null
Customer - Channel	USA	0	0	1	One account with Type = Customer - Channel withBillingCountry = USA
Customer - Channel	null	0	0	2	Two accounts with Type = Customer - Channel withBillingCountry = null
Customer - Direct	USA	0	0	1	One account with Type = Customer - Direct withBillingCountry = USA
Customer - Channel	France	0	0	1	One account with Type = Customer - Channel withBillingCountry = France
null	USA	0	0	1	One account with Type = null with BillingCountry = USA

What is SOSL in Apex?

Salesforce Object Search Language (SOSL) is a simple language for searching across all multiple persisted objects simultaneously.

1. Sosl statements evaluate to a list of SObjects where each list contains the search results for a particular sobject type.
2. SOSL queries are only supported in apex classes and anonymous blocks.
3. We can not use a SOSL query in trigger.

EX:-

The following example that searches all fields across all account and contact objects.

List<List< Sobject>> searchList = [FIND ‘Text*’  IN ALL FIELDS RETURNING Account,Contact];

system.debug(‘searchlist is :’+searchList );

Ex2 : - The Example shows the data with test in Accounts,Contacts,Leads,Oppotunities with Vf page.

Vf  Page :-

<apex:page controller="SOSLController">
  <apex:form >
  <apex:inputText value="{!searchStr}"/>
    <apex:commandButton value="Search in Account, Contact, Opportunity" action="{!soslDemo_method}" reRender="acct,error,oppt,cont" status="actStatusId" />
    <apex:actionStatus id="actStatusId">
                <apex:facet name="start" >
                    <img src="/img/loading.gif"/>                    
                </apex:facet>
    </apex:actionStatus>
  </apex:form>

    <apex:outputPanel title="" id="error">
     <apex:pageMessages ></apex:pageMessages>
     </apex:outputPanel>
      
    <apex:pageBlock title="Accounts" id="acct">
    <apex:pageblockTable value="{!accList }" var="acc">
          <apex:column value="{!acc.name}"/>
          <apex:column value="{!acc.Type}"/>
       </apex:pageblockTable>
    </apex:pageBlock>

 <apex:pageBlock title="Contacts" id="cont">
    <apex:pageblockTable value="{!conList}" var="con">
      <apex:column value="{!con.name}"/>
      <apex:column value="{!con.email}"/>
 </apex:pageblockTable>
 </apex:pageBlock>
  
 <apex:pageBlock title="Opportunities" id="oppt">
    <apex:pageblockTable value="{!optyList}" var="opty">
      <apex:column value="{!opty.name}"/>
     <apex:column value="{!opty.StageName}"/>
 </apex:pageblockTable>
 </apex:pageBlock>

</apex:page>

Apex Controller :-

Public with sharing class SOSLController{
 Public List<Opportunity> optyList {get;set;}
 Public List<contact> conList{get;set;}
 Public List<account> accList{get;set;}
  
 Public String searchStr{get;set;}

   Public SOSLController(){
   }
  
  Public void soslDemo_method(){
   optyList = New List<Opportunity>();
   conList = New List<contact>();
   accList = New List<account>();
   if(searchStr.length() > 1){
   String searchStr1 = '*'+searchStr+'*';
   String searchQuery = 'FIND \'' + searchStr1 + '\' IN ALL FIELDS RETURNING  Account (Id,Name,type limit 10),Contact(name,email limit 10),Opportunity(name,StageName limit 10)';
   List<List <sObject>> searchList = search.query(searchQuery);
    accList = ((List<Account>)searchList[0]);
    conList  = ((List<contact>)searchList[1]);
    optyList = ((List<Opportunity>)searchList[2]);
   if(accList.size() == 0 && conList.size() == 0 && optyList.size() == 0){
       apexPages.addmessage(new apexpages.message(apexpages.severity.Error, 'Sory, no results returned with matching string..'));
       return;
   }
   }
   else{
   apexPages.addmessage(new apexpages.message(apexpages.severity.Error, 'Please enter at least two characters..'));
   return;
   }
  }
}

What is difference between SOQL and SOSL?

SOQL	SOSL
SOQL (Salesforce Object Query Language ) retrieves the records from the database by using “SELECT” keyword.	SOSL(Salesforce Object Search Language) retrieves the records from the database by using the “FIND” keyword.
By Using SOQL we can know in Which objects or fields the data resides.	By using SOSL, we don’t know in which object or field the data resides.
We can retrieve data from single object or from multiple objects that are related to each other.	We can retrieve multiple objects and field values efficiently when the objects may or may not be related to each other.
We can Query on only one table.	We can query on multiple tables.

How to get Maps from SOQL Query in salesforce?

As we all know, Apex code has limitation of  Number of code statements that can be executed. While writing Apex code, we should take care of number of code statement that are  executed to avoid governor limits. It is useful to understand how to reduce the number of executed code statements so that we can stay within the governor limits.

Normally we get list of records in a SOQL query.Sometime we also need set of id’s or map of record id’s and record or list of record id’s.

Apes code for above requirement will be as below:


//Creating List of all account Ids
List<id> accIdsList = new List<id>() ;

//Creating set of all account Ids
Set<id> accIdsSet = new Set<id>() ;

//Creating Map with account id as key and account record as value
Map<Id,Account> accountIdObjMap = new Map<Id,Account>();

//Fetching all accounts
List<account> accList = [select Id,name,site,rating,AccountNumber from account limit 50000] ;

//Fetching Account ids
for(Account acc : accList){
    accIdsSet.add(acc.id);
    accIdsList.add(acc.id);
    accountIdObjMap.put(acc.id,acc);
}


In the code above, if there are large number of records(say 50000) then for loop will be executed 50000  times and also every line in for loop will be executed 50000 times. We can avoid this unnecessary for loop by using good SOQL query which will return map instead of list. After that we can easily get list of accounts or set of account Id’s or List of account Id’s using methods.

New Apex Code will be as below:

//Creating List of all account Ids
List<id> accIdsList = new List<id>() ;

//Creating set of all account Ids
Set<id> accIdsSet = new Set<id>() ;

//Fetching all accounts
List<account> accList = new List<Account>();

//Creating Map with account id as key and account record as value
Map<Id,Account> accountIdObjMap = new Map<Id,Account>([select Id,name,site,rating,AccountNumber from account limit 50000]);

//getting list of account using map.values method
accList = accountIdObjMap.values();

//getting set of account Id's using map.keySet method
accIdsSet = accountIdObjMap.keySet();

//getting list of account Id's using list.addAll method
accIdsList.addAll(accIdsSet);

What is  Aggregate Functions in SOQL?

Aggregate functions in SOQL, such as SUM() and MAX(), allow you to roll up and summarize your data in a query. You can use aggregate functions without using a GROUP BY clause. For example, you could use the AVG() aggregate function to find the average Amount for all your opportunities.

AggregateResult[] groupedResults
  = [SELECT AVG(Amount)aver FROM Opportunity];
Object avgAmount = groupedResults[0].get('aver');

So the functions count(fieldname), count_distinct(), sum(), avg(), min() and max() return an AggregateResult object (if one row is returned from the query) or a List of AggregateResult objects (if multiple rows are returned from the query). You access values in the AggregateResult object much like a map calling a “get” method with the name of the column. In the example below you can see how you can access a column name (leadsource), alias (total) and an unnamed column (expr0). If you have multiple unnamed columns you reference in the order called with expr0, expr1, expr2, etc. 

List<aggregateResult> results = [select leadsource, count(name) total,

 count(state) from lead group by leadsource ]; 

for (AggregateResult ar : results)

 System.debug(ar.get('leadsource')+'-'+ar.get('total')+'-'+ar.get('expr0')); 

The AggregateResult returns the value as an object. So for some operations you will need to cast they value to assign them appropriately.

Set<id> accountIds = new Set<id>();
for (AggregateResult results : [select accountId from contact group by accountId])
  accountIds.add((ID)results.get('accountId'));

One this to be aware of is that the count() function does not return an AggregateResult object. The resulting query result size field returns the number of rows: 

Integer rows = [select count() from contact];
System.debug('rows: ' + rows);

You can also do some cool things like embed the SOQL right in your expression:

if ([select count() from contact where email = null] > 0) {
  // do some sort of processing...
}

How to  know Recently Viewed items using Soql Apex Class?

In Summer ’13 Release salesforce introduced the new Standard Object called  RecentlyViewed, so using that we can easily get the all the recently viewed items using apex class.





Sample SOQL:




SELECT Id, Name, LastViewedDate FROM RecentlyViewed  WHERE Type IN ('Account', 'Contact', 'Case') ORDER BY LastViewedDate DESC




Note:
The RecentlyViewed object does not support the Report, KnowledgeArticle, and Article objects.