You signed in with another tab or window.Reload to refresh your session.You signed out in another tab or window.Reload to refresh your session.You switched accounts on another tab or window.Reload to refresh your session.Dismiss alert
{{ message }}
This repository was archived by the owner on Sep 13, 2024. It is now read-only.
SQuery is an apex plugin that helps you to create dynamic soql queries with a structural and flexible way. It also allows you to enforce field/object level securities while you are running your queries besides that it adds the package prefixes to custom objects and its custom fields that you use in your queries.
Installation
Installation to your scratch org.
#Set the defaultdevhubusernamesfdx force:config:set defaultdevhubusername=<yourdevhubusername>#Create a new scratch orgif you don't already have itsfdx force:org:create -f config/project-scratch-def.json -a squery-org#push thesource code to your scractch orgsfdx force:source:push -u test-plcehsuzpvsp@example.com#open your org.sfdx force:org:open -u test-plcehsuzpvsp@example.com
Installation to your non-scratch org(s).
#Login your developer or sandbox org with the following sfdxcommandsfdx force:auth:web:login --setalias my-dev-org#Deploy thesource code to your orgsfdx force:source:deploy -p force-app -u my-dev-org
Usage
Select query fields
You can select query fields by using addFields methods. you can either give comma separated fields or a List of fields as an argument.If you do not add any fields to your query, Squery adds the Id field of that given sobject as a default.
List<Account>accounts=newSQuery('Account') .addFields('Id,Name,AccountSource') .runQuery();//or you can send a list of fields to addFields method//also Squery can be initialized by an SobjectTypeList<Account>accounts=newSQuery(Account.SObjecType) .addFields(newList<String>{'Id','Name,AccountSource'}) .runQuery();//this is equivalent to List<Account> accounts = [SELECT Id,Name,AccountSource From Account];
Enforce Object/Field Level Security
The following query will enforce field level securities. If the running user has no permission to read one of the fields in the query, Squery will throw a permission error.
In order for selecting parent fields, you must leverage the addParentField() or addParentFields() method.
Stringquery =newSQuery('Case') .addFields('Id,Subject,Type,Status') .addParentFields('Account.Name','Owner.Email') .toSoql();//toSoql method return the generated query as stringSystem.assertEquals(query,'SELECT Id,Subject,Type,Status,Account.Name,Owner.Email FROM Case');
Set LIMIT
Stringquery =newSQuery('Case') .addFields('Id,Subject,Type,Status') .setLimit(100) .toSoql();//toSoql method return the generated query as stringSystem.assertEquals(query,'SELECT Id,Subject,Type,Status FROM Case LIMIT 100');
Offset usage
Stringquery =newSQuery('Case') .addFields('Id,Subject,Type,Status') .setLimit(100) .offSet(10) .toSoql();//toSoql method return the generated query as stringSystem.assertEquals(query,'SELECT Id,Subject,Type,Status FROM Case LIMIT 100 OFFSET 10');
Add Order BY statement
orderBy method accepts two arguments, first param is field API name , second param is SortDirection enum type,available enum types are;SortDirection.ASCENT,SortDirection.DESCENT,SortDirection.ASC_NULL_LAST,SortDirection.DESC_NULL_LAST
List<Account> =newSQuery('Account') .addFields('Id,Name,CreatedDate') .orderBy('CreatedDate',SortDirection.ASCENT) .orderBy('Name',SortDirection.DESCENT) .runQuery();// It is equivalent to// List<Account> accounts =// Database.query('SELECT Id,Name,CreatedDate FROM Account ORDER BY CreatedDate ASC,Name DESC');
Get Record By Id
The following code will return the account by given Id.
StringaccountId ='0010Y00000rFprSQAS';Stringquery =newSQuery('Account') .addFields('Id,Name,CreatedDate') .getRecordById(accountId) .toSoql();System.assertEquals('SELECT Id,Name,CreatedDate FROM Account WHERE Id ='\'0015C00000M7IEwQAN'\'',query);
It is possible to fetch all account records by the given list of Id
List<Id>idList =newList<String>{'0010Y00000rFprSQAS','0010Y00000DWdKIQA1'};Stringquery =newSQuery('Account') .addFields('Id,Name,CreatedDate') .getRecordById(idList) .toSoql();// the query is equivalent to// SELECT Id,Name,CreatedDate FROM Account WHERE Id IN ('0015C00000M7IEwQAN','0010Y00000DWdKIQA1')
Adding conditions to your query
addCondition method provides you a way of adding a condition(s) to your queries.This method only accepts a class instance that is extended by the Condition classwhich can be either Field, AndCondition or OrCondition classes.
Stringquery =newSQuery(Contact.SObjectType) .addFields('Id,FirstName,LastName') .addCondition(newField('FirstName').equals('John')) .addCondition(newField('LastName').equals('Doe')) .toSoql();// the query is equivalent to// SELECT Id,FirstName,LastName From Contact WHERE FirstName='John' AND LastName='Doe'
Grouping Conditions
If you want to group your conditions you can leverage the AndCondition or OrCondition class, which are extended by the Condition class, so that it can be passed to addCondition method as condition argument.
List<Account>accounts =newSQuery('Account') .addFields(newList<String>{'Name','AccountSource','AnnualRevenue','NumberOfEmployees'}) .addCondition(newandCondition() .add(newField('AccountSource').equals('Web')) .add(neworCondition() .add(newField('Rating').equals('Hot')) .add(newField('AnnualRevenue').greaterThan(1500000)) ) ).runQuery();//it is equivalent to/* SELECT Name,AccountSource,AnnualRevenue,NumberOfEmployees FROM Account WHERE ( AccountSource = 'Web' AND (Rating = 'Hot' OR AnnualRevenue > 1500000))*/
Adding Sub-Queries
Stringquery =newSQuery('Account') .enforceFLS() .addFields('Id,Name,AccountSource') .addSubQuery(//Param1 : sObjectType of query object//Param2 : Child Relationship API NamenewSQuery(Opportunity.SObjectType,'Opportunities') .addFields('Id,Amount,StageName') .addCondition(newField('Amount').greaterThan(50000)) ).toSoql();System.assertEquals(query,'SELECT ID,Name,AccountSource,(SELECT Id,Amount,StageName FROM Opportunities WHERE Amount>50000 ) FROM Account');
FIELD CONDITIONS
Field class helps you to filter query records based on the condition.Since the Field class is extended by the Condition class, it can be used as an argument in addCondition methods which only accepts Condition classYou can instantiate a field class by giving the Field API name as String or SobjectField type.
Stringquery =newSQuery(Contact.SObjectType) .addFields('Id,FirstName,LastName') .addCondition(newField('FirstName').contains('John')) .toSoql();// the query is equivalent to// SELECT Id,FirstName,LastName From Contact WHERE FirstName LIKE '%John%'
Field class methods
equals(Object value)
notEquals(Object value)
lessThan(Object value)
lessThanOrEqual(Object value)
greaterThan(Object value)
greaterThanOrEqual(Object value)
isLike(Object value)
startWith(Object value)
endWith(object value)
contains(object value)
includes(object value)
excludes(object value)
isIN(Object value)
notIN(object value)
isNull(object value)
isNotNull(object value)
and toSoql() method that returns the completed condition as filter string.
Examples
// Name = 'John'newField('Name').equals('John');// Name != 'John'newField('Name').notEquals('John');// Amount < 5000newField(Opportunity.Amount).lessThan(5000);//For the date fields you can either pass a Date type or DateLiteral enum type// CreatedDate < YESTERDAYnewField('CreatedDate').lessThan(DateLiteral.YESTERDAY);// CreatedDate < 2019-1-18newField('CreatedDate').lessThan(System.today());// AnnualRevenue <= 1500000newField(Account.AnnualRevenue).lessThanOrEqual(1500000);// AnnualRevenue > 1500000newField(Account.AnnualRevenue).greaterThan(1500000);// AnnualRevenue >= 1500000newField(Account.AnnualRevenue).greaterThanOrEqual(1500000);// FirstName LIKE '%John%'newField(Contact.FirstName).isLike('%John%');// FirstName LIKE 'John%'newField(Contact.FirstName).startWith('Jan');// FirstName LIKE '%John'newField(Contact.FirstName).endWith('Doe');// FirstName LIKE '%John%'newField(Contact.FirstName).contains('Doe');// Custom_MultiSelectPicklist_Field__c INCLUDES ('itemA;itemB')newField(Custom_Object_Name__c.Custom_MultiSelectPicklist_Field__c).includes('itemA;itemB');// Custom_MultiSelectPicklist_Field__c EXCLUDES ('itemA;itemB')newField(Custom_Object_Name__c.Custom_MultiSelectPicklist_Field__c).excludes('itemA;itemB');// ID IN ('0015C00000M7IMRQA3','0015C00000M7IEwQAN')newField(Account.Id).isIN(newList<Id>{'0015C00000M7IMRQA3','0015C00000M7IEwQAN'});// ID NOT IN ('0015C00000M7IMRQA3','0015C00000M7IEwQAN')newField(Account.Id).notIN(newList<Id>{'0015C00000M7IMRQA3','0015C00000M7IEwQAN'});// FirstName = NULLnewField(Contact.FirstName).isNull();// FirstName != NULLnewField(Contact.FirstName).isNotNull();