SOQL_SOSL

Salesforce object query language
    1. developer console
    2. workbench
    3. data loader	
	
	
	
1. Basic structure of SOQL:
   Object: Account
   Id
   Query:
   select Id from Account
   select Id, Active__c from Account     // always use field name or API
   
  Note: another way/method to open object from developer console: Ctrl+ Shift+O
  
2. Where clause:
   select Id, Active__c from Account Where Active__c ='Yes'
   select Id, Active__c,TickerSymbol from Account Where TickerSymbol='EDGE\'s'      //when you have the value in field like Ram's pen; EDGE's com
   
   Note: where clause can't exceed more than 4000
         Total characters in SOQL query can be 20,000
		 
3.  Null operator: both string as well number
                 a. Active__c is picklist (text/string value)
	select Id, Active__c from Account where Active__c =NULL     // to get records with blank value field
	select Id, Active__c from Account where Active__c !=NULL    // to get record with some value in the field
	
	             
                b. Phone_Number__c is number data type
    select Id, Phone_Number__c from Account Where Phone_Number__c=Null            // will fetch all account with field Phone_Number__c having no values 
	select Id, Phone_Number__c from Account Where Phone_Number__c!=Null           // will fetch all account with field Phone_Number__c having some values
	
4. Boolean field: to identify boolean values we use True and False
                  Check box field
    
   SELECT Id, Hot_Account__c FROM Account where Hot_Account__c =TRUE             // fetch all accounts with Hot_Account__c is checked
   SELECT Id, Hot_Account__c FROM Account where Hot_Account__c =False            // fetch all accounts with Hot_Account__c is unchecked
   
5. Operators:
   =     equal           
   !     not
   !=    not equal
   <     Less Than
   <=    Less than eual
   >     greater
   >=    greater than equal
   Like  fuzzy    logic
         %c%  =   data which have c in betweeen
		 %c   =   data which will have c in last
		 c%   =   data which will have c in begining
   OR 
   IN
   NOT
   AND
   
6. Polymorphic: 
   Task and Event
   
   select Id, Who.ID, What.ID from Task                                         //To fetch Parent Object Id from Task
   select Id, Who.ID,Who.Type, What.ID, What.Type from Task                     //To fetch Parent Object Id & Name from Task
   select Id, Who.ID,Who.Type, What.ID, What.Type from Event
   
   Who = Lead and Contact
   What = Account, Opportunity etc.
   
   Parent.ID  = Who.Id
   
   
   
   select Id, Who.ID,Who.Type, What.ID, What.Type from Event
   
7. Fuzzy logic (using Like Operator):
   select Id, Name from Account Where Name Like '%c'      
   
8. Using OR Opeartor:
   select Id, Name from Account Where Name = 'Castic' OR Name = 'Sample Part Acc' OR Name = 'Dickenson plc'
   
9. Using IN opeator:
   select Id, Name from Account Where Name IN('Castic','Sample Part Acc','Dickenson plc')
   select Id, Name from Account Where Name NOT IN('Castic','Sample Part Acc','Dickenson plc')

10. AND
    select Id, Name, Phone from Account Where Name ='Castic' AND Phone=NULL
	select Id, Name, Phone from Account Where Name ='Castic' AND Phone!=NULL 
	
11. ORDER BY==> giving some sequence
    By default ASC AND Nulls First
	ORDER BY Syntax:  Where FieldName/API ORDER BY (keyword to order)
	
	select Id, Name, Phone from Account ORDER BY Phone NULLS LAST                   // using NULLS LAST 
	select Id, Name, Phone from Account ORDER BY Phone NULLS FIRST
    
	select Id, Name, Phone from Account ORDER BY Phone DESC                        // DESC
	select Id, Name, Phone from Account ORDER BY Phone ASC                         // ASC           
	
	Here, we are referring Phone field therefore, our by default property i.e. "By default ASC AND Nulls First" will be maintained in all 4 queries writteen above
	
12. Limit 
    Limit 49999 (index starts 0) 50K
	
	select Id, Name, Phone from Account Limit 5                                       //Limit without where
	select Id, Name, Phone from Account Where Phone!=NULL Limit 5                     //Limit with Where
	
13. Group By:
    select Count(Id), LeadSource from Lead GROUP BY LeadSource 
	select LeadSource from Lead GROUP BY LeadSource
	
	Aggregate function:
	1.Count()
	2.Count_Distinct(): return number of distinct non-null field values matching query condition
	3.MAX()
	4.MIN()
	5.SUM()
	6.AVG()
	
	select Count(Id) from Lead
	select Count() from Lead
	SELECT Count(Id) FROM Account Where Name Like 't%'
	SELECT MAX(CreatedDate) FROM Account
	SELECT Count(Id), MAX(CreatedDate) FROM Account
	SELECT Count(Id), MAX(CreatedDate) FROM Account Where Name Like 't%'
	SELECT Count(Id), Count(CampaignId) FROM Opportunity
	SELECT Count(Id), CampaignId FROM Opportunity Group By CampaignId Limit 2
	SELECT Count(Id), Count(CampaignId) FROM Opportunity Group By CampaignId       // non-valid and will give error: Grouped field should not be aggregated: CampaignId
	You can't use child relationship expressions that uses __r syntx in query using GROUP BY clause
	SELECT Count(Id), CampaignId FROM Opportunity                    // non-valud query and error is Field must be grouped or aggregated: CampaignId
    
	HAVING  =  Where
	select Count(ID), Count(Name), LeadSource from Lead GROUP BY LeadSource HAVING COUNT(LeadSource)>5
	select LastName, Account.Name from Contact GROUP BY Account.Name, LastName HAVING Account.Name!='HCL'
	
	
14. Dynamic query
    Let say you want to write a very simple query:
	select ID, Name, Phone from Account        // static query
	Dynamic query for above query:
	
	list<Sobject> lstSobj = new list<Sobject>();
    string field1 ='Phone';

    string queryform='select Id,Name,'+field1+' from Account';
    system.debug('Query formation: '+queryform);
    lstSobj=Database.query(queryform);

    system.debug('Result of dynamic query: '+lstSobj);
	
	
	
	Dynamic query with more than 1 fields:
	list<Sobject> lstSobj = new list<Sobject>();
     string field1 ='Phone';
      string field2='Industry';
      string query='select Id,Name,'+field1+','+field2+' from Account';
      system.debug('Query formation: '+query);
      lstSobj=Database.query(query);

      system.debug('Result of dynamic query: '+lstSobj);
	  
	  
	  
	  Dynamic query with if-else:
	  list<Sobject> lstSobj = new list<Sobject>();
      Boolean check=True;
      if(check==False){                                   // change to True, to execute if block
      string field1 ='Phone';
     string field2='Industry';  
     string query='select Id,Name,'+field1+','+field2+' from Account';
     system.debug('Query formation: '+query); 
     lstSobj=Database.query(query);
     system.debug('Result of dynamic query: '+lstSobj);
     }
    else{
    string field1 ='Rating';
    string field2='Description'; 
    string query='select Id,Name,'+field1+','+field2+' from Account';
   system.debug('Query formation: '+query); 
   lstSobj=Database.query(query);
   system.debug('Result of dynamic query: '+lstSobj);
     }

15. Relationship query:
    2 types: parent to child  & child to parent
	
	Parent to child:
	select Id,Name,(select Id,Name from Opportunities) From Account
	select Id,Name,(select Id,Name from Opportunities), (select Id, LastName from Contacts) From Account
	
	Custom object:
	select Id,Name,(select Id,Name from Opportunities), (select Id, LastName from Contacts),(select ID,Account__c from Custom_Objects__r) From Account
    
	
	For standard and customer object: 20
	
	Child to parent:
	select Id, Account.ID, Account.Name from Opportunity
	standard object: 35
	custom object: 25
	access level: 3 e.g. select Id, Account.ID, Account.Name, Opportunity.Account.Owner.Name from Opportunity    (Opportunity.Account.Owner.Name)

	
															SOSL
Salesforce object search language
globalsfdcprofessionals18@gmail.com
list<list<Sobject>>: Return type
SOQL return type is list<Sobject>

Syntax:
FIND {search_value}                                                         FIND {test}
                                                                            FIND {globalsfdcprofessionals18@gmail.com}    
FIND {search_value} RETURNING Sobject                                       FIND {test} RETURNING Account
FIND {search_value} RETURNING Sobject(field1, field2)                       FIND {test} RETURNING Account(Id, Name, Description, Phone)
FIND {search_value} RETURNING Sobject(field1, field2),
                              Sobject2(field1,field2)                       FIND {test} RETURNING Account(Id, Name, Description, Phone), Lead(Id, Status)
							                                                FIND {test} RETURNING Account(Id, Name, Description, Phone), Lead

IN searchgroup: FIND {search_value} IN searchgroup                          searchGroup contains below option:
					
FIND {search_value} IN ALL FIELDS                        					FIND {test} IN ALL FIELDS              //FIELDS = is a keyword
FIND {search_value} IN Name FIELDS                                          FIND {test} IN Name FIELDS                  
FIND {search_value} IN Email FIELDS                                         FIND {globalsfdcprofessionals18@gmail.com} IN EMAIL FIELDS
FIND {search_value} IN Phone FIELDS                                         FIND {212} IN Phone FIELDS			

LIMIT:  optional
FIND {globalsfdcprofessionals18@gmail.com} IN EMAIL FIELDS Limit 2

WITH SPELL_CORRECTION:        option
                              WITH SPELL_CORRECTION=false                     //by default = true
							  FIND {globalsfdcprofEssionals18@gmail.com} IN EMAIL FIELDS WITH SPELL_CORRECTION=false
							  FIND {globalsfdcprofEssionals18@gmail.com} IN EMAIL FIELDS RETURNING User(Id, Country) WITH SPELL_CORRECTION=false
							  
1.  SOSL: returns data from entire org
    SOQL: returns from a single specific object
2.  SOSL: query email, text, phone
    SOQL: query all your fields
3.  SOSL: can't be use in trigger but can be in class
    SOQL: can be used in class and trigger as well
4.  SOSL: can't performed DML operation
    SOQL: yes, you can perform DML
5.  SOSL: return fields
    SOQL: return record
	
						Sample class to use SOSL:

public class SOSL_Sample_Class {

    public static void sosllivedemo()
    {
        list<list<sobject>> sobjectlist=[FIND 'test' 
                                         IN ALL FIELDS RETURNING Account(Id, Name, Rating),
                                        Contact(Id, Name, Email),
                                        Lead(Id, Name, Email)];
        list<Account> Accountlist=(list<Account>)sobjectlist.get(0);
        list<Contact> Contactlist=(list<Contact>)sobjectlist.get(1);
        list<Lead> Leadlist=(list<Lead>)sobjectlist.get(2);
        
        system.debug('@@@@@ Account list: '+Accountlist);
        system.debug('@@@@@ Contact list: '+Contactlist);
        system.debug('@@@@@ Lead list: '+Leadlist);
    }
}