Query Management Philosophy In Salesforce

Data Management With Salesforce

Salesforce is grandiose and tends to be a universal tool and platform for the support of any business and activities.

It contains a great scope of tools, clouds, and features for building business processes, company collaboration, and customization of anything you build inside Salesforce, like business applications, sites, etc.

When the administration tools and features are not enough, and people need something more specific than out-of-the-box solutions can provide, they may use the Salesforce native programming languages and front-end frameworks. These are Apex server language, SOQL&SOSL database languages, Visualforce Pages, and Lightning Components.

But no matter what we use, we deal with the data. Every move can be considered as data usage and/or data management.

Especially when programming the code, we deal with data management. By data management, I understand changing the existing data or creating new data.

Database Requests In Salesforce

To manage the data, we query it into Apex code that uses the data for transforming data for other data. This is ensured by SOQL queries, which are the database requests. Each query can contain its own filters and nested queries with their own filters and nested queries. The nested queries can be a combination of the logic expression like 1 AND 2 AND 3.

So, in the common case, we can imagine that each scope of SOQL requests to the database can be represented as follows (O# — means some Salesforce object, — standard or custom, FO# — means lookup field to object O#):

Picture 1: Common query structure

Here, in picture 1, the structure of the common query bundle is represented. 

I showed only two levels for the nested queries. But in fact, the query structure can be much more complicated with more nested query levels.

Also, it can have a very different boolean logic junction, like in picture 2.

Picture 2: Variant of query structure

Also, the search fields and the ID field are swapped within the request depending on what the user needs:

Picture 3: Lookup fields swapping within the request

Let’s summarize that depending on business requirements:

• There can be many nested queries to the database and many levels of nesting,
• Queries can have a different boolean logic junction,
• Lookup fields can swap with the ID field within the nested query.

Salesforce SOQL Database Language Restriction

It is known that Salesforce SOQL can support only one level for the nested query per one request to the database.

So, how does the developer handle this restriction?

The most common way is to get the IDs of the related records into a list of IDs to use in a target query. It can be done as follows:

Picture 4: auxiliary code for retrieving related records IDs list

When the business logic dictates more complicated conditions for selecting records, the developer will use some similar code as the above as many times as required. Besides, the developer will provide the order of the query executions.

And all this will be done as many times as business requirements are changed.

Huge Routine Financial Expenses for Development

This requires a certain investment of time and money. And it requires more investments in case the selection logic is complicated. And it requires far more investments in case the changes happen rarely. The developers do not always describe their good enough code, and because the companies have no project document support culture, and that’s why the changes to the previously developed code can be painful and expensive.

That’s why it is recommended to use separate classes and methods for the data retrieving. This is the principle of dividing the data querying and business logic.

But still, the development of the data querying is required, and it’s time and money-consuming.

Money Saving Solution (Part 1)

That’s why it can be profitable to skip the development of the data querying working on the business logic only.

For that purpose, I developed Query Manager, a universal tool for isolating the data querying and the business logic code.

It consists of the query controller, query builder UI, and query saving object.

The query builder UI is devoted to setting up the querying and filtering settings.

The query controller is devoted to using settings to retrieve the data and pass the data to the client business logic code.

The query saving object is the object the records of which store the querying and filtering settings, and the records are used for query controller operation.

The Query Manager allows to concentrate on the business logic developing independently of the data querying logic.

This is the way for money saving in many ways:

1. You don’t spend much time investigating the existing data-retrieving code
2. You don’t spend much time programming updates for the data-retrieving
3. You don’t spend time testing the updates
4. You can maintain the processes by exposing the purposes and making the comments right in the fields of the query settings records. Also, you can track the date of the last updates in querying logic and so on.

The Query Manager has two global methods available with the synchronous apex.

These are:

• @AuraEnabled global static String getPagedRecordsForApex(String dataTableSettingsId, integer pgNum, integer pgSize)
• @AuraEnabled global static List<SObject> getAllRecordsForApex(String dataTableSettingsId)
• The getPagedRecordsForApex method returns the records as a string for a certain page of your view list.
• The getAllRecordsForApex method returns all records that are available according to the querying setting.

For details on how to use these methods, please watch the video.

Auxiliary Batch Classes

Okay — one may say — you’ve provided the solution for the synchronous Apex, but how about the asynchronous Batchable Classes?

The question is raised because developers use the Batchable Classes to operate the big scope of records due to Salesforce limitations for SOQL (50000 queried records per transaction) and because of 10000 records for DML operations per one transaction.

Sometimes, in such cases, the auxiliary Batchable Classes appear. Auxiliary Batchable Classes: I understand the Batchable Classes that perform the same role as the code in Picture 4 (auxiliary code for retrieving related records IDs list). But despite the synchronous Apex where the separate method can be used, in the asynchronous Apex, the additional separate Batchable Class must be used. And since the querying logic can be complicated and can vary for different processes inside the organization, the developers must provide as many Batchable Classes as required for those processes, chaining them each time in the right sequence.

Again, this requires a certain investment of time and money. Even more than for synchronous Apex.

Money Saving Solution (Part 2)

And as for synchronous Apex, the solution that saves you money is skipping the auxiliary Batchable Classes using the Query Manager.

For that purpose, it has a global static void callByFilteringSettings(String className, String settingsId) method.

Calling that method, you can skip all auxiliary Batchable Classes development and get the result of the database queries from those Batchable Classes right into your target Batchable Class with your custom logic.

For details on how to use the method callByFilteringSettings please watch the video.

Fundamental Unsolved Problems

Unless we’ve got a fundamentally new development approach, there are still some unsolved keystone issues.

The first issue is that there are still 50000 queried records per transaction remaining for the synchronous apex. And apparently, there is nothing we can do with that. The only thing we can do is to improve the performance by applying my solution for the infinite pagination that was described in my other article. And this is the point for the future development.

The second issue is the heap size for the asynchronous apex solution (for Batchable Classes). 

This issue can be solved by two approaches. The first one is based on the developing encoder and decoder of the IDs of the records. This encoder and decoder must accept the first ID, last ID, order principle between first and last IDs, and SObject name. But still, I don’t know the technical decision for that approach. Please make comments on what the possible decision can be. The second approach is to use the CSV files with the IDs for auxiliary Batchable Classes. This is more clear and can help in solving the heap size and CPU Time limits.

So, this second issue is also a point for future development.

Limitations And Lacks That Have Place At This Stage

1. There is no handling of SOQL limits and other limits exceeding them. It can be uncomfortable in some cases. This is also a point for future development.
2. Only SObjects that the user can see in Object Manager inside the organization are available in the Query Manager. This is done with the purpose of not overloading the users and developers with extra information at the Query Builder UI. It can be done for a separate extended version.
3. Query Manager was not tested at all with the Queable Classes. This is a point for future investigation and development.
4. Query Management concept for managing the development and support of the project. This concept is not quite clear at the moment.
5. Transformable data tables with different functionalities and adaptive for custom functionality. This concept is not quite clear at the moment.
6. Some other disadvantages, laks, and bugs that you can find using the Query Manager.

Conclusion

The Query Manager is a handy, forward-thinking, and rule-changing product and philosophy. 

It’s free. 

The main purpose for creating and developing is to create useful and money-saving concepts.

Your usage and feedback are very pleasant and important investments.

That’s why my proposal for businesses and developers is to use the Query Manager.