Tag Archives: SOQL

Displaying records based on the picklist values

Here is the example of Opportunity object, based on Stage Picklist values.

Apex Class:

public class SearchOpportunity {
    public Opportunity opp  {get;set;}
    public List<Opportunity> OpportunityList  {get;set;}
    
    public SearchOpportunity () {
        opp = new Opportunity();
    }
    
    public PageReference doSearch() {
        OpportunityList =[Select Id, Name, StageName FROM Opportunity WHERE StageName =: Opp.StageName];
        return null;
    }
}

Visualforce Page:

<apex:page controller="SearchOpportunity">
    <apex:form>
        <apex:pageBlock Title="Search Opportunities">
            <b>Stage :</b>
            <apex:inputField value="{!opp.StageName}" />
            <apex:commandButton value="Search" action="{!doSearch}"/>
        </apex:pageBlock>
        
        <apex:pageBlock title="List of Opportunities">
            <apex:pageBlockSection columns="1">
                <apex:pageBlockTable value="{!OpportunityList}" var="item">
                    <apex:column value="{!item.Name}" headerValue="Name"/>
                    <apex:column value="{!item.StageName}" headerValue="Stage"/>
                </apex:pageBlockTable>
            </apex:pageBlockSection>
        </apex:pageBlock>
    </apex:form>
</apex:page>

Output:

SearchOpportunities

LIKE Clause In Salesforce Dynamic SOQL Query

Sample Code:

string conName = 'biswa';
string conNameLike = '%' + String.escapeSingleQuotes(conName.trim()) + '%';
String query = 'SELECT Id, FirstName, LastName From Contact Where Name LIKE :conNameLike';
List<Contact> conList = Database.query(query);
System.debug('conList-' + conList);

SOQL query to get last 90days created records

There is a feature of SOQL called ‘Date Literals’, Using this we can simply write query to get last 90days created records.
The below query will fetch accounts where it’s Created Dtae is more than 90 Days.

Sample SOQL:

 SELECT Id FROM Account WHERE CreatedDate > LAST_90_DAYS

There are a lot of other ‘Date Literals’ that we can use. Here is the full reference.

Date Literals in Salesforce SOQL

Date Literal Range Example
YESTERDAY Starts 12:00:00 the day before and continues for 24 hours. SELECT Id FROM Account WHERE CreatedDate =
YESTERDAY
TODAY Starts 12:00:00 of the current day and continues for 24 hours. SELECT Id FROM Account WHERE CreatedDate >
TODAY
TOMORROW Starts 12:00:00 after the current day and continues for 24 hours. SELECT Id FROM Opportunity WHERE CloseDate =
TOMORROW
LAST_WEEK Starts 12:00:00 on the first day of the week before the most recent first day
of the week and continues for seven full days. First day of the week is determined
by your locale.
SELECT Id FROM Account WHERE CreatedDate >
LAST_WEEK
THIS_WEEK Starts 12:00:00 on the most recent first day of the week before the current day
and continues for seven full days. First day of the week is determined by your
locale.
SELECT Id FROM Account WHERE CreatedDate <
THIS_WEEK
NEXT_WEEK Starts 12:00:00 on the most recent first day of the week after the current day
and continues for seven full days. First day of the week is determined by your
locale.
SELECT Id FROM Opportunity WHERE CloseDate =
NEXT_WEEK
LAST_MONTH Starts 12:00:00 on the first day of the month before the current day and
continues for all the days of that month.
SELECT Id FROM Opportunity WHERE CloseDate >
LAST_MONTH
THIS_MONTH Starts 12:00:00 on the first day of the month that the current day is in and
continues for all the days of that month.
SELECT Id FROM Account WHERE CreatedDate <
THIS_MONTH
NEXT_MONTH Starts 12:00:00 on the first day of the month after the month that the current
day is in and continues for all the days of that month.
SELECT Id FROM Opportunity WHERE CloseDate =
NEXT_MONTH
LAST_90_DAYS Starts 12:00:00 of the current day and continues for the last 90 days. SELECT Id FROM Account WHERE CreatedDate =
LAST_90_DAYS
NEXT_90_DAYS Starts 12:00:00 of the current day and continues for the next 90 days. SELECT Id FROM Opportunity WHERE CloseDate >
NEXT_90_DAYS
LAST_N_DAYS:n For the number n provided, starts 12:00:00 of the current day and continues for the last n days. SELECT Id FROM Account WHERE CreatedDate =
LAST_N_DAYS:365
NEXT_N_DAYS:n For the number n provided, starts 12:00:00 of the current day and continues for the next n days. SELECT Id FROM Opportunity WHERE CloseDate >
NEXT_N_DAYS:15
NEXT_N_WEEKS: n For the number n provided, starts 12:00:00 of the first day of the next week and continues for the next n weeks. SELECT Id FROM Opportunity WHERE CloseDate >
NEXT_N_WEEKS:4
LAST_N_WEEKS:n For the number n provided, starts 12:00:00 of the last day of the previous week and continues for the last n weeks. SELECT Id FROM Account WHERE CreatedDate = LAST_N_WEEKS:52
NEXT_N_MONTHS:n For the number n provided, starts 12:00:00 of the first day of the next month and continues for the next n months. SELECT Id FROM Opportunity WHERE CloseDate >
NEXT_N_MONTHS:2
LAST_N_MONTHS:n For the number n provided, starts 12:00:00 of the last day of the previous month and continues for the last n months. SELECT Id FROM Account WHERE CreatedDate =
LAST_N_MONTHS:12
THIS_QUARTER Starts 12:00:00 of the current quarter and continues to the end of the current
quarter.
SELECT Id FROM Account WHERE CreatedDate =
THIS_QUARTER
LAST_QUARTER Starts 12:00:00 of the previous quarter and continues to the end of that
quarter.
SELECT Id FROM Account WHERE CreatedDate >
LAST_QUARTER
NEXT_QUARTER Starts 12:00:00 of the next quarter and continues to the end of that
quarter.
SELECT Id FROM Account WHERE CreatedDate <
NEXT_QUARTER
NEXT_N_QUARTERS:n Starts 12:00:00 of the next quarter and continues to the end of the nth quarter. SELECT Id FROM Account WHERE CreatedDate <
NEXT_N_QUARTERS:2
LAST_N_QUARTERS:n Starts 12:00:00 of the previous quarter and continues to the end of the previous nth quarter. SELECT Id FROM Account WHERE CreatedDate >
LAST_N_QUARTERS:2
THIS_YEAR Starts 12:00:00 on January 1 of the current year and continues through the end
of December 31 of the current year.
SELECT Id FROM Opportunity WHERE CloseDate =
THIS_YEAR
LAST_YEAR Starts 12:00:00 on January 1 of the previous year and continues through the end
of December 31 of that year.
SELECT Id FROM Opportunity WHERE CloseDate >
LAST_YEAR
NEXT_YEAR Starts 12:00:00 on January 1 of the following year and continues through the
end of December 31 of that year.
SELECT Id FROM Opportunity WHERE CloseDate <
NEXT_YEAR
NEXT_N_YEARS:n Starts 12:00:00 on January 1 of the following year and continues through the end of December 31 of the nth year. SELECT Id FROM Opportunity WHERE CloseDate <
NEXT_N_YEARS:5
LAST_N_YEARS:n Starts 12:00:00 on January 1 of the previous year and continues through the end of December 31 of the previous nth year. SELECT Id FROM Opportunity WHERE CloseDate >
LAST_N_YEARS:5
THIS_FISCAL_QUARTER Starts 12:00:00 on the first day of the current fiscal quarter and continues
through the end of the last day of the fiscal quarter. The fiscal year is defined in
the company profile under Setup at Company Profile | Fiscal Year.
SELECT Id FROM Account WHERE CreatedDate =
THIS_FISCAL_QUARTER
LAST_FISCAL_QUARTER Starts 12:00:00 on the first day of the last fiscal quarter and continues
through the end of the last day of that fiscal quarter. The fiscal year is defined
in the company profile under Setup at Company Profile | Fiscal Year.
SELECT Id FROM Account WHERE CreatedDate >
LAST_FISCAL_QUARTER
NEXT_FISCAL_QUARTER Starts 12:00:00 on the first day of the next fiscal quarter and continues
through the end of the last day of that fiscal quarter. The fiscal year is defined
in the company profile under Setup at Company Profile | Fiscal Year.
SELECT Id FROM Account WHERE CreatedDate <
NEXT_FISCAL_QUARTER
NEXT_N_FISCAL_​QUARTERS:n Starts 12:00:00 on the first day of the next fiscal quarter and continues through the end of the last day of the nth fiscal quarter. The fiscal year is defined in the company profile under Setup atCompany Profile | Fiscal Year. SELECT Id FROM Account WHERE CreatedDate <
NEXT_N_FISCAL_QUARTERS:6
LAST_N_FISCAL_​QUARTERS:n Starts 12:00:00 on the first day of the last fiscal quarter and continues
through the end of the last day of the previous
nth fiscal
quarter. The fiscal year is defined in the company profile under Setup at Company Profile | Fiscal Year.
SELECT Id FROM Account WHERE CreatedDate >
LAST_N_FISCAL_QUARTERS:6
THIS_FISCAL_YEAR Starts 12:00:00 on the first day of the current fiscal year and continues
through the end of the last day of the fiscal year. The fiscal year is defined in
the company profile under Setup at Company Profile | Fiscal Year.
SELECT Id FROM Opportunity WHERE CloseDate =
THIS_FISCAL_YEAR
LAST_FISCAL_YEAR Starts 12:00:00 on the first day of the last fiscal year and continues through
the end of the last day of that fiscal year. The fiscal year is defined in the
company profile under Setup at Company Profile | Fiscal Year.
SELECT Id FROM Opportunity WHERE CloseDate >
LAST_FISCAL_YEAR
NEXT_FISCAL_YEAR Starts 12:00:00 on the first day of the next fiscal year and continues through
the end of the last day of that fiscal year. The fiscal year is defined in the
company profile under Setup at Company Profile | Fiscal Year.
SELECT Id FROM Opportunity WHERE CloseDate <
NEXT_FISCAL_YEAR
NEXT_N_FISCAL_​YEARS:n Starts 12:00:00 on the first day of the next fiscal year and continues through
the end of the last day of the
nth fiscal year. The fiscal year
is defined in the company profile under Setup at Company Profile | Fiscal Year.
SELECT Id FROM Opportunity WHERE CloseDate <
NEXT_N_FISCAL_YEARS:3
LAST_N_FISCAL_​YEARS:n Starts 12:00:00 on the first day of the last fiscal year and continues through
the end of the last day of the previous
nth fiscal year. The
fiscal year is defined in the company profile under Setup at Company Profile | Fiscal Year.
SELECT Id FROM Opportunity WHERE CloseDate >
LAST_N_FISCAL_YEARS:3

Use Like With List And Set Collections In SOQL Queries

Set Collection in Like:

Set<String> accountNames = new Set<String>{'%Sam%', '%App%', '%Len%','%Nok%'};
List<Account> accList = [Select Id, Name From Account Where Name LIKE :accountNames];

List Collection in Like:

List<String> accountNameList = new List<String>{'%Sam%', '%App%', '%Len%','%Nok%'};
List<Account> accList = [Select Id, Name From Account Where Name LIKE :accountNameList];