All Projects → PropicSignifi → Query.apex

PropicSignifi / Query.apex

Licence: mit
A dynamic SOQL and SOSL query builder on Salesforce.com platform

Projects that are alternatives of or similar to Query.apex

apex-query-builder
Convenient query builder for dynamic SOQL queries
Stars: ✭ 37 (-52.56%)
Mutual labels:  query, salesforce, apex
json2apex
Generate strongly typed apex code from a json structure.
Stars: ✭ 121 (+55.13%)
Mutual labels:  salesforce, apex
amoss
Amoss - Apex Mock Objects, Spies and Stubs - A Simple Mocking framework for Apex (Salesforce)
Stars: ✭ 55 (-29.49%)
Mutual labels:  salesforce, apex
Apex Recipes
A library of concise, meaningful examples of Apex code for common use cases following best practices.
Stars: ✭ 307 (+293.59%)
Mutual labels:  apex, salesforce
Sfdc Convert Attachments To Chatter Files
📎 Easily migrate your Attachments to Salesforce Files.
Stars: ✭ 72 (-7.69%)
Mutual labels:  apex, salesforce
Script.apex
Evaluate Javascript expressions in Apex
Stars: ✭ 18 (-76.92%)
Mutual labels:  salesforce, apex
Ebikes Lwc
Sample application for Lightning Web Components and Communities on Salesforce Platform. Part of the sample gallery. Retail use case. Get inspired and learn best practices.
Stars: ✭ 299 (+283.33%)
Mutual labels:  apex, salesforce
apex-fp
Functional programming for Salesforce Apex
Stars: ✭ 231 (+196.15%)
Mutual labels:  salesforce, apex
Sfdc Trigger Framework
A minimal trigger framework for your Salesforce Apex Triggers
Stars: ✭ 527 (+575.64%)
Mutual labels:  apex, salesforce
Salesforcedx Vscode
Salesforce Extensions for VS Code
Stars: ✭ 653 (+737.18%)
Mutual labels:  apex, salesforce
Squery
Salesforce SOQL query builder
Stars: ✭ 16 (-79.49%)
Mutual labels:  apex, salesforce
Lwc Recipes
A collection of easy-to-digest code examples for Lightning Web Components on Salesforce Platform
Stars: ✭ 1,147 (+1370.51%)
Mutual labels:  apex, salesforce
NebulaFramework
A development framework for Salesforce's Apex language & the Force.com platform
Stars: ✭ 28 (-64.1%)
Mutual labels:  salesforce, apex
Purealoe
Salesforce Sample App part of the sample gallery. Agriculture and retail use case. Get inspired and learn best practices.
Stars: ✭ 65 (-16.67%)
Mutual labels:  apex, salesforce
apexmock
force.com Mock data and fixtures for Apex Unit Tests
Stars: ✭ 24 (-69.23%)
Mutual labels:  salesforce, apex
Lightningflowcomponents
A collection of unofficial Lightning Components that can be used to enhance Salesforce Lightning Flow and Lightning Pages.
Stars: ✭ 252 (+223.08%)
Mutual labels:  apex, salesforce
Rflib
Salesforce open source library with logging framework, trigger framework, feature switches, and advanced monitoring capabilities
Stars: ✭ 69 (-11.54%)
Mutual labels:  apex, salesforce
apex-utils
Utility classes for Salesforce Apex development
Stars: ✭ 20 (-74.36%)
Mutual labels:  salesforce, apex
salesforce-plantuml
Salesforce app to generate UML class & ER-diagrams from your org data. Leverages the PlantUML library.
Stars: ✭ 89 (+14.1%)
Mutual labels:  salesforce, apex
Npsp
The current version of the Salesforce.org Nonprofit Success Pack
Stars: ✭ 487 (+524.36%)
Mutual labels:  apex, salesforce

Query.apex

Build Status

Query.apex provides a flexible and dynamic way of building a SOQL/SOSL query on the Salesforce platform.

Our Page    Documentation    Tutorials

Why Query.apex

Although Salesforce provides Database.query method to dynamically execute a query from a string, it is far from easy to construct such a string in a bug-free, structural and flexible way. Query.apex is made to improve the flexibility of the code and consequently enhance the productivity of the development.

Features

  • Allows object oriented programming paradigm and function chaining

  • Supports complex queries including parent/child relationships, and nested conditions in a flexible way

  • Prevents SOQL injections, without manually escaping the string variables

  • Supports aggregate functions including group by methods

  • Manages the namespace of the object names and field names, while also provides the Object/Field Level Security checking

  • SOSL features are in active development. Please follow https://github.com/PropicSignifi/Query.apex/projects/1 for the progress

Examples

Get all accounts

This will return a list of all Accounts from the database.

By default it will select only the Id field.

List<Account> accounts = new Query('Account').run();

Select all fields

This will query all Accounts from the database, selecting all fields.

List<Account> accounts =
    new Query('Account').
    selectAllFields().
    run();

Select all user readable fields

This will query all Accounts from the database, selecting all fields which the user has read access on.

List<Account> accounts =
    new Query('Account').
    selectReadableFields().
    run();

Select specific fields

This will query all Accounts from the database, selecting specified fields only.

List<Account> accounts =
    new Query('Account').
    selectField('Name').
    selectFields('CreatedById').
    selectFields('CreatedDate, LastModifiedDate').
    selectFields(new List<String>{'LastActivityDate', 'LastViewedDate'}).
    run();

Get an account based on its Id

This will query the Accounts with a specific Id, and return only one SObject as a result.

Account account =
    (Account)new Query('Account').
    byId('001O000001HMWZVIA5').
    fetch();

Get a list of contacts based on a foreign key

This will query the Contacts given the foreign key AccountId.

List<Contact> contacts =
    new Query('Contact').
    lookup('AccountId', '001O000001HMWZVIA5').
    run();

Get a list of Id of the query result

This will query all the Accounts and return a list of Id as a result.

List<Account> accounts =
    new Query('Account').
    toIdList();

Get the executable query string

If you want to get the raw query string intead of executing the query, you can use the toQueryString method.

String queryString = new Query('Account').
    selectFields('Name').
    addConditionLike('Name', '%Sam%').
    toQueryString();

Database.query(queryString);

Debug

You can use the debug method to print the current query string to the log.

new Query('Account').
    selectFields('Name').
    debug().
    addConditionLike('Name', '%Sam%').
    debug()
    run();

Enforce security check

By default, when read permission of an object/field is missing, we will print a warning to the log. But we can change it to an exception if that's needed.

new Query('Account').
    enforceSecurity().
    selectFields('Name').
    run();

You may also call the static method enforceGlobalSecurity to enforce exception on all Query instances.

Query.enforceGlobalSecurity();

new Query('Account').
    selectFields('Name').
    run();

Select parent fields

This will select all the fields from the parent object Account.

List<Contact> contacts =
    new Query('Contact').
    selectAllFields('Account').
    run();

This will select all user readable fields from the parent object Account.

List<Contact> contacts =
    new Query('Contact').
    selectReadableFields('Account').
    run();

Query with simple conditions

This will query all the accounts whose 'FirstName' is 'Sam' and 'LastName' is 'Tarly'.

By default, all the conditions are joined by the 'AND' operator.

List<Account> accounts =
    new Query('Account').
    addConditionEq('Name', 'Sam').
    addConditionLt('NumberOfEmployees', 10).
    run();

Query with complex conditions

For more complicated conditions, we can use the method 'conditionXX' to create a condition variable, before using the 'doOr' method or 'doAnd' boolean operation methods to join these conditions.

List<Account> accounts =
    new Query('Account').
    addCondition(
        Query.doAnd(
            Query.doOr(
                Query.conditionEq('FirstName', 'Sam'),
                Query.conditionEq('Id', '001O000001HMWZVIA5')
            ),
            Query.conditionLe('NumberOfEmployees', 15)
        )
    ).
    run();

Query with date literal conditions

We can also use date literals in conditions.

List<Account> accounts =
    new Query('Account').
    addConditionLe('LastModifiedDate', Query.TODAY).
    addConditionEq('CreatedDate', Query.LAST_N_WEEKS(3)).
    run();

Query with conditions with INCLUDES/EXCLUDES operator

INCLUDES and EXCLUDES operator can be used on multi-picklist fields.

The following example is querying QuickText with the Channel field that includes both the two values at the same time:

List<QuickText> result = new Query('QuickText').
    addConditionIncludes('Channel', 'MyChannel;OtherChannel').
    run();

In contrast, this example is a condition that includes any of the two values:

List<QuickText> result = new Query('QuickText').
    addConditionIncludes('Channel', new List<String>{'MyChannel', 'OtherChannel'}).
    run();

Query with subqueries

Query.apex also allows selecting child relationships (subqueries), in a method chain style similar to the conditions.

List<Account> accounts =
    new Query('Account').
    addSubquery(
        Query.subquery('Contacts').
        addConditionEq('FirstName', 'Sam').
        addConditionIn('LastName', new List<String>{'Tarly'})
    ).
    run();

Query with semi-join

It is also possible to have a subquery in the condition, known as the semi-join.

List<Account> accounts =
    new Query('Account').
    lookup('Id', new Query('Opportunity').
            selectField('AccountId')).
    run();

Simple aggregate functions

Aggregate functions including 'count', 'countDistinct', 'max', 'min', 'avg', 'sum' are supported. Optional alias can be provided as the second parameter. To get the aggregate result, user needs to call the method 'aggregate()'.

The 'aggregate' method returns a list of 'AggregateResult' items.

AggregateResult result =
    new Query('Account').
    count('Name', 'countName').
    countDistinct('Rating', 'countRating').
    max('NumberOfEmployees', 'maxEmployee').
    min('NumberOfEmployees', 'minEmployee').
    avg('NumberOfEmployees', 'avgEmployee').
    sum('NumberOfEmployees', 'sumEmployee').
    aggregate()[0];

Integer countName = (Integer)result.get('countName');
Integer countRating = (Integer)result.get('countRating');
Integer maxEmployee = (Integer)result.get('maxEmployee');
Integer minEmployee = (Integer)result.get('minEmployee');
Decimal avgEmployee = (Decimal)result.get('avgEmployee');
Integer sumEmployee = (Integer)result.get('sumEmployee');

In this example, 'countName', 'maxEmployee', and so forth are the alias for the aggregate functions. Since there is no group by clauses used, the returned list has one and only one item. You can get the value of an aggregated field using the 'get' method in the first 'AggregateResult' item.

Aggregate functions combined with GROUP BY clause

Aggregate functions are more useful combined with the 'groupBy' method, so that each group can have its own aggregate result. Similar to the simple aggregate functions, the 'aggregate' method is needed to get the aggregate results, which will return a list of 'AggregateResult' items.

We can also select fields that appear in the group by list. Similar to the methods 'count', 'max', etc, optional alias as the second argument is allowed in the 'selectField' method.

List<AggregateResult> results =
    new Query('Account').
    selectField('Rating', 'rate').
    count('Name', 'countName').
    max('NumberOfEmployees', 'maxEmployees').
    min('NumberOfEmployees', 'minEmployees').
    avg('NumberOfEmployees', 'avgEmployees').
    sum('NumberOfEmployees', 'sumEmployees').
    groupBy('Rating').
    aggregate();

for (AggregateResult result : results) {
    System.debug('Rating: ' + result.get('rate'));
    System.debug('maxEmployees: ' + result.get('maxEmployees'));
    System.debug('minEmployees: ' + result.get('minEmployees'));
    System.debug('avgEmployees: ' + result.get('avgEmployees'));
    System.debug('sumEmployees: ' + result.get('sumEmployees'));
}

Note that we can only select fields that appear in the group by method. In this example, only the 'Rating' field is in the group by clause, so only the 'Rating' field can be selected.

Aggregate functions with HAVING clauses

With HAVING clauses, aggregate functions can be even more powerful. See this example:

Suppose we have a parent object Account, and a child object Opportunity, I want to query all the Accounts with at least one Opportunity. If we use child relationship query (subquery), we might still get all the Accounts, with some of them having the Opportunity child as an empty list. And then we need to do the filter manually, removing the Accounts with empty Opportunity list. Apparently, such way costs unnecessary memory.

But we can actually do it in one query, using GROUP BY and HAVING clauses.

List<AggregateResult> results =
    new Query('Opportunity').
    selectField('AccountId').
    count('Name', 'countName').
    groupBy('AccountId').
    addHaving(Query.conditionGe('Count(Name)', 1)).
    aggregate();

// Loop the aggregate result to get the account ids
List<Id> accountIds = new List<Id>();
for (AggregateResult result : results) {
    accountIds.add((Id)result.get('AccountId'));
}
Note that the project description data, including the texts, logos, images, and/or trademarks, for each open source project belongs to its rightful owner. If you wish to add or remove any projects, please contact us at [email protected].