SOQL Queries:
=============
Query:
It is a request to the database, to fetch the required records from one or more salesforce objects.
Governor Limits:
----------------
1. We can have max. of 100 SOQL Queries inside a transaction.
2. Each SOQL query can return max. of 50,000 records.
Syntax:
-------
SELECT <ColumnNames> from <ObjectName>
[ WHERE <Conditions> ]
[ GROUP BY <ColumnNames> ]
[ HAVING <Conditions> ]
[ ORDER BY <ColumnNames> <Ascending / Descending> ]
[ FOR UPDATE ]
[ LIMIT <Number Of Records To Return> ]
[ OFFSET <NumberOf Records To Skip> ]
[ ALL ROWS ]
Ways to Invoke an SOQL Query:
-----------------------------
We can invoke the SOQL query by using the below ways.
1. By using Developer Console.:
Use the "Query Editor" tab inside the Developer console, to execute the SOQL Queries to fetch the records.
2. By using Apex Programming.
3. By using DataLoader Tool.
4. By using "Third Party Tools"
1. Workbench Tool
2. Aside.io
3. DataLoader.io
4. DataLoader CliQ
5. Informatica Cloud.
// Write an SOQL Query, to fetch account ID, Name, Rating, Industry, Annual Revenue from Account object.
select id, name, rating, industry, annualrevenue from account
// Write an SOQL Query, to fetch FirstName, LastName, Title, Phone, Fax and EmailId of Lead Records.
select id, firstname, lastname, phone, fax, email from Lead
// Write an SOQL Query, to fetch Name, Location, Email Id of Hiring Manager objects.
select name, location__c, email_id__c, contact_number__C from hiring_manager__C
// Write an apex program to fetch all position records from position object. (ID, Name, Location, Open Date, Close Date, Maximum Pay and Postion Status)
select id, name, position_status__c,location__C, open_date__C, close_date__c, maximum_pay__c from position__C
// Write an SOQL Query, to fetch all Apex classes from Metadata Repository.
select id,name, body from apexclass
// Write an SOQL Query, to fetch all Email Templates from Salesforce.
select id, name, templatetype from emailtemplate
// Write an SOQL Query, to fetch all the Record Types detailes from Salesforce.
select id, name, sobjecttype from recordtype
By using Apex programming, we an execute the SOQL Queries and present the data to the user.
Types Of SOQL Queries:
----------------------
Apex provides 2 types of SOQL Queries.
1. Static SOQL Queries:
a. when the field which we want to fetch
b. when the object from which the data is fetched
c. when the conditions what we are going to apply are fixed and know to us then we make single query statement which will run throughout the business operation are called static query.
Note: All Static SOQL Queries should be enclosed in "[ ]".
Ex: string query = [select name, idustry from account];
Ex: [select name, industry from account where indusry = 'banking'];
Ex: String accindustry = 'banking';
[select name, industry from account where industry =: accindustry];
2. Dynamic SOQL Queries:
when the fields | objects | conditions changes from transaction to transaction we call it as dynamic soql queries. We have to store the Query inside a "string" variable.
Ex:
string accountsQuery = 'select id, name, rating,
industry, active__c from account';
We have to invoke / execute the query manually by using "Database.Query()" method.
Ex:
Database.Query(accountsQuery);
Upon executing the Query, it returns a resultset, which contains one or more records. We need to catch the results and store into a collection.
If Query Returns Only One Record:
---------------------------------
If query returns only one record, then to store the result we need to define a variable of the associated datatype.
Ex:
Account acc = [select id, name,rating, industry, active__c
from account];
if(acc != null)
{
system.debug('Record Id is...: '+ acc.id);
system.debug('Account Name is...: '+ acc.name);
system.debug('Rating value is...: '+ acc.rating);
system.debug('Active Status is...: '+ acc.active__c);
}
If Query Returns Multiple Records:
----------------------------------
If the query returns more than one record, then we have to store the resultset in a "List" collection class.
Ex:
List<Position__c> lstPositions = [select id, name,
location__C, open_date__C, close_date__c, position_status__c from position__C];
Once the collection store the records, then we need to iterate the collection and get each record and perform the operations on records.
if(! lstPositions.isEmpty())
{
for(Position__c pos : lstPositions)
{
system.debug('Position Id ...: '+ pos.id);
system.debug('Name ...: '+ pos.name);
system.debug('Status : '+ pos.position_status__c);
system.debug('Close Date..: '+ pos.close_date__c);
}
}
/*
Write an apex class, to fetch all the Contact records from Contact object.
*/
Class Code:
-----------
public class DatabaseHelper
{
public void GetAllContacts()
{
List<contact> lstContacts = [select id, firstname, lastname, email,
phone, fax, title from contact];
system.debug('Contact Records Count ...: '+ lstContacts.size());
if(! lstContacts.isEmpty())
{
for(Contact con : lstContacts)
{
system.debug('Contact Id is...: '+ con.id);
system.debug('Contact Name is...: '+ (con.FirstName + ' '+ con.LastName));
system.debug('Contact Email Id is...: '+ con.Email);
system.debug('Phone Number ....: '+ con.Phone);
system.debug('Fax Number ....: '+ con.Fax);
system.debug('Title ....: '+ con.Title);
system.debug('----------------------------------------------');
}
}
}
}
/*
Write an apex program, to fetch all candidate records and store into a "Map Collection"
*/
Class Code:
-----------
Public class DatabaseHelper
{
Public static void ShowAllCandidates()
{
Map<id, candidate__C> mapCandidates = new Map<Id, Candidate__C>([select id,
name, location__C, email_id__c,
contact_number__C from candidate__C]);
if(! mapCandidates.isEmpty())
{
for(Candidate__C cnd : mapCandidates.values())
{
system.debug('Candidate Id is...: '+ cnd.id);
system.debug('Candidate Name is....: '+ cnd.Name);
system.debug('Candidate Location ...: '+ cnd.Location__c);
system.debug('Email Id ...: '+ cnd.Email_ID__c);
system.debug('Contact Number ....: '+ cnd.Contact_Number__c);
system.debug('----------------------------------------------');
}
}
/* Commented Code..
for(Candidate__C cnd : [select id, name, location__C, email_id__c,
contact_number__C from candidate__C])
{
mapCandidates.put(cnd.id, cnd);
}
*/
}
}
Execution:
----------
// invoke the static method..
DatabaseHelper.ShowAllCandidates();
WHERE Clause:
=============
Where clause is used to add one or more user defined conditions along with the SOQL Query, to filter the resultset records.
Note:Where clause is an optional statement in SOQL Query.
Syntax:
[ WHERE <Conditions> ]
Each condition should follow the below syntax.
Syntax:
<fieldName> <Operator> <Value>
Ex:
Industry = 'Banking'
annualrevenue > 4500000
rating = 'Warm'
active__c = 'Yes'
isConvertedFromLead = true
accountId != null
We can add multiple conditions along with the SOQL query with the help of "Logical Operators".
Ex:
annualrevenue > 5600000 and Rating = 'Hot';
industry = 'Banking' and active__c = 'No'
location__C = 'Hyderabad' and position_status__c = 'Closed'
status = 'Closed Converted' and isConverted = false
=============
- By using SOQL queries, we can retrieve the records from one or more objects in salesforce.
- We can fetch the records from both "Standard and Custom objects".
- By using SOQL queries, we can fetch one / more / all records from the specified object.
- Upon retrieving the records, we can add one or more filter conditions to filter the records.
Query:
It is a request to the database, to fetch the required records from one or more salesforce objects.
Governor Limits:
----------------
1. We can have max. of 100 SOQL Queries inside a transaction.
2. Each SOQL query can return max. of 50,000 records.
Syntax:
-------
SELECT <ColumnNames> from <ObjectName>
[ WHERE <Conditions> ]
[ GROUP BY <ColumnNames> ]
[ HAVING <Conditions> ]
[ ORDER BY <ColumnNames> <Ascending / Descending> ]
[ FOR UPDATE ]
[ LIMIT <Number Of Records To Return> ]
[ OFFSET <NumberOf Records To Skip> ]
[ ALL ROWS ]
Ways to Invoke an SOQL Query:
-----------------------------
We can invoke the SOQL query by using the below ways.
1. By using Developer Console.:
Use the "Query Editor" tab inside the Developer console, to execute the SOQL Queries to fetch the records.
2. By using Apex Programming.
3. By using DataLoader Tool.
4. By using "Third Party Tools"
1. Workbench Tool
2. Aside.io
3. DataLoader.io
4. DataLoader CliQ
5. Informatica Cloud.
// Write an SOQL Query, to fetch account ID, Name, Rating, Industry, Annual Revenue from Account object.
select id, name, rating, industry, annualrevenue from account
// Write an SOQL Query, to fetch FirstName, LastName, Title, Phone, Fax and EmailId of Lead Records.
select id, firstname, lastname, phone, fax, email from Lead
// Write an SOQL Query, to fetch Name, Location, Email Id of Hiring Manager objects.
select name, location__c, email_id__c, contact_number__C from hiring_manager__C
// Write an apex program to fetch all position records from position object. (ID, Name, Location, Open Date, Close Date, Maximum Pay and Postion Status)
select id, name, position_status__c,location__C, open_date__C, close_date__c, maximum_pay__c from position__C
// Write an SOQL Query, to fetch all Apex classes from Metadata Repository.
select id,name, body from apexclass
// Write an SOQL Query, to fetch all Email Templates from Salesforce.
select id, name, templatetype from emailtemplate
// Write an SOQL Query, to fetch all the Record Types detailes from Salesforce.
select id, name, sobjecttype from recordtype
By using Apex programming, we an execute the SOQL Queries and present the data to the user.
Types Of SOQL Queries:
----------------------
Apex provides 2 types of SOQL Queries.
1. Static SOQL Queries:
a. when the field which we want to fetch
b. when the object from which the data is fetched
c. when the conditions what we are going to apply are fixed and know to us then we make single query statement which will run throughout the business operation are called static query.
Note: All Static SOQL Queries should be enclosed in "[ ]".
Ex: string query = [select name, idustry from account];
Ex: [select name, industry from account where indusry = 'banking'];
Ex: String accindustry = 'banking';
[select name, industry from account where industry =: accindustry];
2. Dynamic SOQL Queries:
when the fields | objects | conditions changes from transaction to transaction we call it as dynamic soql queries. We have to store the Query inside a "string" variable.
Ex:
string accountsQuery = 'select id, name, rating,
industry, active__c from account';
We have to invoke / execute the query manually by using "Database.Query()" method.
Ex:
Database.Query(accountsQuery);
Upon executing the Query, it returns a resultset, which contains one or more records. We need to catch the results and store into a collection.
If Query Returns Only One Record:
---------------------------------
If query returns only one record, then to store the result we need to define a variable of the associated datatype.
Ex:
Account acc = [select id, name,rating, industry, active__c
from account];
if(acc != null)
{
system.debug('Record Id is...: '+ acc.id);
system.debug('Account Name is...: '+ acc.name);
system.debug('Rating value is...: '+ acc.rating);
system.debug('Active Status is...: '+ acc.active__c);
}
If Query Returns Multiple Records:
----------------------------------
If the query returns more than one record, then we have to store the resultset in a "List" collection class.
Ex:
List<Position__c> lstPositions = [select id, name,
location__C, open_date__C, close_date__c, position_status__c from position__C];
Once the collection store the records, then we need to iterate the collection and get each record and perform the operations on records.
if(! lstPositions.isEmpty())
{
for(Position__c pos : lstPositions)
{
system.debug('Position Id ...: '+ pos.id);
system.debug('Name ...: '+ pos.name);
system.debug('Status : '+ pos.position_status__c);
system.debug('Close Date..: '+ pos.close_date__c);
}
}
/*
Write an apex class, to fetch all the Contact records from Contact object.
*/
Class Code:
-----------
public class DatabaseHelper
{
public void GetAllContacts()
{
List<contact> lstContacts = [select id, firstname, lastname, email,
phone, fax, title from contact];
system.debug('Contact Records Count ...: '+ lstContacts.size());
if(! lstContacts.isEmpty())
{
for(Contact con : lstContacts)
{
system.debug('Contact Id is...: '+ con.id);
system.debug('Contact Name is...: '+ (con.FirstName + ' '+ con.LastName));
system.debug('Contact Email Id is...: '+ con.Email);
system.debug('Phone Number ....: '+ con.Phone);
system.debug('Fax Number ....: '+ con.Fax);
system.debug('Title ....: '+ con.Title);
system.debug('----------------------------------------------');
}
}
}
}
Execution:
----------
// Create the object of the class..
DatabaseHelper dbHelper = new DatabaseHelper();
// Calling the method...
dbHelper.GetAllContacts();
/*
Write an apex program, to fetch Id, Name, Location, Contact Number and Email Id of All Hiring Managers.
*/
Class Code:
-----------
Public class DatabaseHelper
{
Public static void GetAllRecruiters()
{
for(Hiring_Manager__C hm : [select id, name, location__C,
Contact_Number__C, email_id__c
from Hiring_Manager__C ])
{
system.debug('Hiring Manager Record is..: '+ hm);
}
}
}
Execution:
----------
// invoke the static method..
DatabaseHelper.GetAllRecruiters();
/*
Write an apex program, to fetch all candidate records and store into a "Map Collection"
*/
Class Code:
-----------
Public class DatabaseHelper
{
Public static void ShowAllCandidates()
{
Map<id, candidate__C> mapCandidates = new Map<Id, Candidate__C>([select id,
name, location__C, email_id__c,
contact_number__C from candidate__C]);
if(! mapCandidates.isEmpty())
{
for(Candidate__C cnd : mapCandidates.values())
{
system.debug('Candidate Id is...: '+ cnd.id);
system.debug('Candidate Name is....: '+ cnd.Name);
system.debug('Candidate Location ...: '+ cnd.Location__c);
system.debug('Email Id ...: '+ cnd.Email_ID__c);
system.debug('Contact Number ....: '+ cnd.Contact_Number__c);
system.debug('----------------------------------------------');
}
}
/* Commented Code..
for(Candidate__C cnd : [select id, name, location__C, email_id__c,
contact_number__C from candidate__C])
{
mapCandidates.put(cnd.id, cnd);
}
*/
}
}
Execution:
----------
// invoke the static method..
DatabaseHelper.ShowAllCandidates();
WHERE Clause:
=============
Where clause is used to add one or more user defined conditions along with the SOQL Query, to filter the resultset records.
Note:Where clause is an optional statement in SOQL Query.
Syntax:
[ WHERE <Conditions> ]
Each condition should follow the below syntax.
Syntax:
<fieldName> <Operator> <Value>
Ex:
Industry = 'Banking'
annualrevenue > 4500000
rating = 'Warm'
active__c = 'Yes'
isConvertedFromLead = true
accountId != null
We can add multiple conditions along with the SOQL query with the help of "Logical Operators".
Ex:
annualrevenue > 5600000 and Rating = 'Hot';
industry = 'Banking' and active__c = 'No'
location__C = 'Hyderabad' and position_status__c = 'Closed'
status = 'Closed Converted' and isConverted = false
/*
Write an apex program, to fetch all the Postition records available for "Hyderabad" location.
*/
Public class DatabaseHelper
{
Public static void GetPositionsForHyderabad()
{
Map<ID, position__C> mapPositions = new Map<Id, position__C> ([select id, name,
location__C,
position_status__C, close_date__C
from position__C
where location__C = 'Hyderabad']);
system.debug('ResultsSet count...: '+ mapPositions.size());
if( ! mapPositions.isEmpty())
{
for(Position__c pos : mapPositions.values())
{
system.debug('Position Record Id ...: '+ pos.id);
system.debug('Position Record Name ...: '+ pos.name);
system.debug('Location is... ...: '+ pos.location__C);
system.debug('Position Status ...: '+ pos.position_status__C);
system.debug('-------------------------------------------');
}
}
}
}
Execution:
----------
// invoke the static method..
DatabaseHelper.GetPositionsForHyderabad();
/*
Write an apex program, to fetch all the Account records, based on the Industry supplied by the user at runtime.
*/
Class Code:
-----------
Public class DatabaseHelper
{
Public static void GetAccountsByIndustry(string industryName)
{
if(industryName != '' && industryName != null)
{
List<Account> lstAccounts = [select id, name, rating, industry, active__C,
type, annualrevenue
from Account
where industry =: industryName];
system.debug('Records Collection size is...: '+ lstAccounts.size());
if(! lstAccounts.isEmpty())
{
for(Account acc : lstAccounts)
{
system.debug('Account Record is...: '+ acc);
}
}
}
}
Public static void GetPositionsForHyderabad()
{
Map<ID, position__C> mapPositions = new Map<Id, position__C> ([select id, name,
location__C,
position_status__C, close_date__C
from position__C
where location__C = 'Hyderabad']);
system.debug('ResultsSet count...: '+ mapPositions.size());
if( ! mapPositions.isEmpty())
{
for(Position__c pos : mapPositions.values())
{
system.debug('Position Record Id ...: '+ pos.id);
system.debug('Position Record Name ...: '+ pos.name);
system.debug('Location is... ...: '+ pos.location__C);
system.debug('Position Status ...: '+ pos.position_status__C);
system.debug('-------------------------------------------');
}
}
}
}
Execution:
----------
// invoke the static method..
DatabaseHelper.GetAccountsByIndustry('Energy');
/*
DatabaseHelper.GetAccountsByIndustry('Banking');
DatabaseHelper.GetAccountsByIndustry('Finance');
*/
Assignments:
------------
1. Write an apex program, to fetch all converted Lead Records from Lead object.
2. Write an apex program, to fetch all active users from USer object.
3. Write an apex program, to fetch all contact records which are not associated with any account.
4. Write an apex class to fetch the position records based on the "Status" supplied by the user at runtime.
5. Write an apex program, to fetch all the contact records associated with the Account 'Edge Communications'.
6. Write an apex program, to show all record types associated with the specified object (object name to be supplied at runtime.)
LIMIT Clause:
-------------
This clause is used to restrict the number of records to be returned by the SOQL query.
Syntax:
[ LIMIT <Number Of Records To Return> ];
Ex:
LIMIT 1
LIMIT 10
LIMIT 2
Note:Limit clause is an optional statement in SOQL Query.
/*
Write an apex program, to fetch only one Candidate record from Candidate object.
*/
Class Code:
-----------
Public class DatabaseHelper
{
Public static void GetCandidateRecord()
{
Candidate__C cnd = [select id, name, location__c, email_id__c, contact_number__c
from Candidate__c
LIMIT 1];
if(cnd != null)
{
system.debug('Candidate Id is...: '+ cnd.id);
system.debug('Candidate Name is...: '+ cnd.Name);
system.debug('Email Id is...: '+ cnd.Email_ID__c);
system.debug('Contact Number is...: '+cnd.Contact_Number__c);
}
}
}
Execution:
----------
// Invoking the class method...
DatabaseHelper.GetCandidateRecord();
Task:
-----
1. Write an apex program, to fetch all the Tasks and Events asociated with the Account "Burlington Textiles Corporation".
2. Write an apex program to fetch 2 active User records.
ALL ROWS Clause:
----------------
This clause is used to fetch all the records exist in the object along with the deleted records also.
Note:This clause is an optional statement in SOQL Query.
Syntax: [ ALL ROWS ]
Note:All Rows clause has to be specified at the end of the SOQL Query.
Note: Salesforce provides a hidden field in every standard and custom object with the name "isDeleted". Which is a Boolean field(Checkbox type).
It holds the value TRUE, when the record has been removed from the object (i.e. exist in RecycleBin). Else it holds the value as "FALSE".
/*
Write an apex program to fetch all contact records along with the deleted records also.
*/
Class Code:
-----------
Public class DatabaseHelper
{
Public static void ExportAllContacts()
{
List<Contact> lstContacts = [select id, firstname, lastname, email, phone,
fax, title, isdeleted
from Contact
ALL ROWS];
system.debug('Collection size is...: '+ lstContacts.size());
if(! lstcontacts.isEmpty())
{
for(Contact con : lstContacts)
{
system.debug('Contact Record is...: '+ con);
}
}
}
}
Execution:
----------
// Invoking the class method...
DatabaseHelper.ExportAllContacts();
/*
Write an apex program to fetch all deleted position records.
*/
Public class DatabaseHelper
{
Public static void GetDeletedPositions()
{
List<Position__C> lstPositions = [select id, name, location__c, position_status__c, isDeleted
from Position__c
where isDeleted = TRUE
ALL ROWS];
system.debug('Deleted Records count ...: '+ lstPositions.size());
if(! lstPositions.isEmpty())
{
for(Position__c pos : lstPositions)
{
system.debug('Position Id is...: '+ pos.id);
system.debug('Position Name is...: '+ pos.Name);
system.debug('Location ....: '+ pos.Location__c);
system.debug('Position Status....: '+ pos.Position_Status__c);
system.debug('Is Deleted Record? : '+ pos.IsDeleted);
system.debug('----------------------------------------');
}
}
}
}
Execution:
----------
// Invoking the class method...
DatabaseHelper.GetDeletedPositions();
No comments:
Post a Comment