Keywords and about SOQL apex Language in Salesforce

 

when to use SOQL:-

---------------------------

->Retrieve data from a single object or from multiple objects that are related to one another.

->count the number of records that meet specified criteria.

->sort result as part of the query.

->Retrieve data from number,date,or checkbox fields.

 

Sample SOQL Structure:-

----------------------------------

->SELECT one or more fields ,[SubQuery] from an object

            where filter statements GROUP BY ,HAVING Count(Email)>2,ORDER BY CreatedDate ASC

            LIMIT 10 ,OFFSET 5,FOR{VIEW,UPDATE}

           

TYPES OF SOQL QUERIES:-

--------------------------------------

            ->Child to Parent(Inner Query)

            ->parent to Child

 

Parent to child (inner query):-

---------------------------------------

->whenever we wanted to retrieve the related/child record for particular parent record or set of  parent record then use the concept of inner query

->for example, we wanted to retrieve the contacts Related to a particular Account then we will use a blow query.

->SELECT Id,Name,(Select Id,Name from Contacts) From Account WHERE ID IN:accList

->Here in the above Query contacts is Relationship Name.For standard Object it will always be the plural label of object name.

for example:- Contact=>Contact, Case=>Cases,Opportunity=>Opportunities

->For Custom Object, we will append_r which will represent the Relationship.For example,if the custom Object name is Book and it has lookup relationship with Account then in the Inner Query it will be Books_r

 

Child to Parent Query:-

------------------------------

-> Sometimes we want to fetch a few fields of the parent record while working with the child record then we go for the child to parent query.

-> For example,while working with contacts records users needs the account Shipping address then for this use case SOQL will look like below

 

->SELECT Id,Name,AccountId,Account.ShippingAddress from Contact.

 

 

ORDER By in SOQL:-

-----------------------------

SELECT Name,CreatedDate from Account Order By Name DESC LIMIT 5

 

 

GROUP By in SOQL:-

-----------------------------

SELECT Name,Max(CreatedDate) FROM Account GROUP BY Name LIMIT 5;

 

Note:- while using group by field in select statement either should be GROUP by or Aggregated

 

Having By in SOQL:-

---------------------------

->to find duplicate records based on a particular field.For example External Id.

->Select AccountId from Contact GROUP BY AccountId Having Count(Id)>2.

 

Aggregate Query:-

------------------------

-> To find the aggregate result for a field.Below are few methods that are supported in aggregate Query

            ->Count

            ->Sum

            ->Max

            ->Min

            ->AVG

->Select Count(Id),AccountId from Contact GROUP By AccountId

 

Polymorphic Query:-

---------------------------

 

->A polymorphic relationship is one where the current object can be one of several object types depending on a related Event

 

SELECT  FirstName,LastName FROM Contact where FirstName IN('rose','sean')

 

Limitations of OFFSET:-

-------------------------------

->the maximum offset is 2,000 rows.

->Requesting as offset greater than 2000 results in a NUMBER_OUTSIDE_VALID_RANGE error.

->OFFSET is intended to be used in a top-level query,and is not allowed in most subqueries,so the following query is invalid and returns a MALFORMED_QUERY error

 

 

SOQL Includes, Excludes Operators:-

--------------------------------------------------

->Used to filter multi picklist field values in salesforce.

->These operators are used for only multi picklist values

->its values are those whose have more than one picklist values

for example a student must have many skills to get job (salesforce,java,.net,angular etc)

Includes Operator:-

-------------------------

 ->include operator is used to filter and retrive data that contains any of the specified values in the SOQL statement.

SELECT name,country_c,Student_skills_c from Student_c WHERE Student_skills_c INCLUDES('salesforce')

->Sometimes we want to fetch a few fields of the parent record while working with the child record then we go for the child to parent query.

->for example, while working with contacts records users needs the Account Shipping address then for this use case SOQL

 from above SOQL INCLUDES operator statement we filtering the data whose student skills is equal to 'salesforce'

 

 

Excludes Operator:-

--------------------------

-> SELECT name,country_c,Student_skills_c from Student_c WHERE Student_skills_c EXCLUDES('salesforce')

-> From above SOQL statements we fetch the data whose student will have no salesforce skills.condition must be written in single quotations and braces as shown above.