SOQL
1. What is SOQL?
Ans: SOQL stands for Salesforce Object Query Language.
- SOQL is used to retrieve the data from Database.
- It is syntactically similar to SQL
- We can write SOQL query in apex code or in Query editor( in Developer console).
- We can retrieve data from single object or from multiple objects which are related to each other
- We can have 100 SOQL queries per transaction.
- We can retrieve 50000 records using SOQL query or queries.
Syntax: SELECT
FROM
WHERE
WITH
GROUP BY
HAVING
ORDER BY
LIMIT
OFFSET
FOR{VIEW, UPDATE}
Parent to Child( Inner query )
Whenever we writing query on Parent Object, we need child records that are associated with that parent then we go for inner query
Standard Objects:
SELECT Id,Name,(SELECT Id,Name FROM Contacts) FROM Account
Account : Parent Object
Contact : Child Object
* Inside inner query we need to give relationship name. In the above example Contacts is the relationship name between Account and Contact.
Custom Objeccts:
SELECT Id,Name,(SELECT Id,Name FROM Student1__r) FROM Company__c;
Company__c : Parent Object
Student__c : Child Object
Relationship name between this Objects Student1.
For Custom Objects we need to append with __r.
i.e Student1__r.(Child relationship name __r).
Child to Parent
When we are writing query on child object, but we also need parent of that child record means then we go for Child to Parent Query.
Standard Objects:
SELECT Id,Name,Account.Name,Account.Active__c FROM Contact;
Contact: Child Object
Account : Parent Object
We can query parent record fields with .(dot) notation
Custom Objects:
SELECT Id,Name,Company__r.Name,Company__r.Active__c FROM Student__c;
Company__c : Parent Object
Student__c : Child Object
* for custom objects we need to use Parent__r.FieldName
Note: When we are writing a query from child to parent, relationship name will be Master-Detail Field Name or Lookup Field Name.
Queries can be executed in formats like:
- List<Sobject> result = [query statements];
- List<sObject> result = Database.getQueryLocator
- Database.QueryLocator result = Database.getQueryLocator(query Statement);
- Map<Id,sObject> result = new Map<Id,sObject>([query statement]);
Date Literals(Keywords) in Salesforce
DATE and TIME keywords
TODAY, YESTERDAY, THIS_WEEK, NEXT_WEEK, LAST_WEEK
LAST_N_WEEK:N, NEXT_N_WEEK:N
DATE and TIME methods
calender_month()
calender_quarter()
calender_year()
Day_month()
Day_week()
Query Examples:
1. Write a query to fetch Accounts that do not have any Contacts?
Ans: [SELECT Id,Name FROM Account WHERE Id NOT IN(SELECT AccountId FROM Contact)];
2. Write a query to fetch Accounts that have atleast one Contact?
Ans:[SELECT Id,Name FROM Account WHERE Id IN(SELECT AccountId FROM Contact)];
3. There is a Queue with name MyQueue. How to query it using SOQL from the database?
Ans: Queues will store in Group Object. To query for MyQueue from the database using SOQL, we should use the following syntax.
Group grp=[SELECT Id,Name FROM Group WHERE Name='MyQueue' and Type='Queue' LIMIT 1];
4. How to query deleted records in Salesforce?
Ans: We can query deleted records using isDeleted=TRUE and ALLROWS.
List<Account> acc=[SELECT Id,Name,IsDeleted FROM Account WHERE IsDeleted=TRUE];
5. Write SOQL to fetch from 5th osition Account records from 10 records.
Ans:
List<Account> accs=[SELECT Id,Name, FROM Account LIMIT 10 OFFSET 5];
6.Write a SOQL query to fetch last or recently 10 Accounts created.
Ans:
List<Account> accounts=[SELECT Id,Name FROM Account ORDER BY DESC LIMIT 10 ];
7. Write a SOQL query to fetch all those opportunities whose Stageame is prospecting and amount is greater han 50000?
Ans:
List<Opportunity> opp=[SELECT Id,Name,Stagename,Amount FROM Opportunity WHERE Stagename='Prospecting' AND Amount>50000];
8. How can you lock records in apex code using SOQL?
Ans:
List<Account> accounts=[SELECT Id,Name FROM Account LIMIT 2 FOR UPDATE];
* The above query can lock the records.
Note: FOR UPDATE will be used only in Apex classes and Triggers.
Like clause in soql?
Ans: Like cluase is used to compare the string values or used to search for the string value in the given fields.
'%' it is used for any number of characters
'_' it is used for only one character
9. Write a query to fetch all the accounts whose name starts with United?
Ans:
SELECT Id,Name,Phone FROM Account WHERE Name Like 'United%'
10. Write a query to fetch Accounts with corresponding contacts whose phone number is 12345?
Ans:
List<Account> accList=[SELECT Id,Name,(SELECT Id,LastName,Phone FROM Contacts WHERE Phone=12345) FROM Account];
11. Write a SOQL Quey to fetch the Leads whose Record type as Partner
Ans:
List<Lead> leads= [SELECT Id, Name,RecordType FROM Lead WHERE RecordType.Name='Partner'];
12. How to use multi select picklist field in SOQL?
Ans:
SELECT Id, Name FROM Account WHERE msFieldName includes('AA', 'BBB', 'CCCC')
No comments:
Post a Comment