Skip to main content

Query Module

warning

Query feature documentation is still work in progress!

Query module of Brobench provides highly sought after capabilities to help with writing queries, view results, create favourite queries and recall query history for easy re-execution.

Query module includes the following major capabilities

  • Write queries
  • Content Assist
  • Field insights
  • SOQL Enhancements
  • Query Execution
  • View Records by Ids/Values
  • Child queries
  • Query Snippets
  • Query History
  • Favorite Scripts

Overview

There are a lot of things in the Query module, and the below screenshot highlights many important aspects. Each of these features is explored further in the below sections.

Basic Usage

The most basic usage is to author query and execute it. To do that goto Query Editor, write or paste the query and click on Execute button. Once executed, results will be shown in the below section. At this time, Query Results don't allow you to edit the records but that capability will be added in future versions.

Once results are shown, you can explore the results. Search for particular record by entering search term in Search in Grid input box. If there are a lot of fields, you can filter fields by entering search term in Filter Fields input box. You can resize the columns by dragging the end of fields and dragging to make it wider or narrower. You can re-arrange fields if you want to see specific fields together.

If your query matched more records than shown in the Grid, Fetch Next Batch button would be enabled. Clicking on that button, the app will fetch the next batch of records.

If you want to hide some fields based on some criteria, click on the Filter fields by Criteria button where you can choose the type of fields to hide.

Query Editor

Writing queries is as easy as it gets in Brobench. Features like Content Assist of Objects/Fields/Picklist Values and Query Snippets can significantly reduce the amount of time it takes for you to author a correct query without errors.

To author, goto Query editor and start typing the query. The first thing you notice is auto-complete showing Query Snippets like below. Query Snippets are re-usable pieces of Query that act like a template of actual Query that would want to write. Select a snippet or just continue typing what you intend to type, and the app will continue to prompt auto-complete suggestions based on context.

Once you got the query, you intended next step is to execute. See Executing Query for various ways to execute the Query.

Query Comments

Brobench supports both single line comments //... and multi-line comments /*...*/. You can select the query text including comments and query executor will ignore the commented piece of the query and execute the rest.

You can use shortcut Cmd+/ or Ctrl+/ to toggle the single line comment of current line

Content Assist

Brobench supports content assist of

  • Objects
  • Fields
  • Parent Fields
  • Child Fields
  • Picklist Values

Those resources are shown in the following parts of the query

  • Select
  • Child Query
  • From
  • Where
  • Having
  • Order By
  • Sub Query

Since From <object> is essential to know the Object which defines the context, it is advised to enter select from <object> first and then compose rest of the query. This way, the app can fetch and show the appropriate list of values.

App should pop up the suggestions as you compose the query. If for whatever reason suggestions are not shown, you can request the suggestions by entering Ctrl+Space shortcut.

Here are some examples of various ways of Content Assist.

Objects

Fields

Parent Object Fields

Child Relationships in Child Queries

Child Object Fields

Picklist Values

SOQL Enhancements

Brobench provides the following enhancements on top of Salesforce SOQL query language.

Wild Card Fields

While Salesforce provides fields(all)/fields(standard)/fields(custom) constructs to fetch all fields, it comes with many limitations with critical one being you cannot fetch more than first batch of records with it. It doesn't support selecting fields by some name match.

To overcome these issues, Brobench wild card selection of fields is a more versatile and capable enhancement as outlined below

  • Enter * in place of a field. For ex., select * from account
  • Use * anywhere in a field to match by prefix or suffix or contains.
    • select *street from account will select both BillingStreet and ShippingStreet
    • select billing* from account will select all fields starting with Billing
    • select *name* from contact will select all fields containing Name like FirstName, LastName, Name etc.,
  • Use wild card multiple times
    • select billing*, shipping*, *street* from account will select all fields starting with either billing or shipping. If a field is matched by one or more matchers, then that field is included only once.
  • Use * with specific parent prefix to select matching fields in that parent object
    • select Id, *name*, Account.billing* from contact will select contact id, contact fields containing name and its account fields starting with billing

All Rows Clause

Salesforce allows you to include All Rows clause when you run the query in Apex language context. This clause shows all records, including deleted/archived records. This clause doesn't work outside of Apex Language context.

Brobench provides language enhancement to parse all rows clause and if specified, query SOQL with the Include Deleted option.

Note that you can also query to show deleted records by executing Execute as Include Deleted execution mode. See Execute Other for more information.

bb_fields Function

bb_fields is designed to allow selection of fields to be queried which provides enhanced capabilities than standard fields function.

Function syntax is bb_fields(arg1, arg2, ... argN) where each arg indicate a group of fields as follows.

  • type_<TypeBase> where TypeBase is base type of field without spaces. For ex., type_date or type_lookup or type_picklist or type_multipicklist. Type Base of field is value as shown in Object details without additional type information like digits or length, etc.,
  • category_<Category> where Category is field category, which is shown in Object details

For ex.,

  • select bb_fields(type_lookup, type_date) from account; selects all Lookup and Date fields from Account
  • select bb_fields(type_lookup, category_custom) from account selects all Lookup and Custom Fields from Account;

Execute Query

There are three ways to execute a query that you have authored.

  • Click on the Execute button in Query Editor toolbar
  • Right-click on the query text and select Execute menu item
  • Press keyboard shortcut Cmd+Enter/Ctrl+Enter

The query to be executed is picked in this order

  • If you have selected any part of the query, only that selected text will be executed.
  • If you haven't selected any text, then the paragraph around the current line will be used. The paragraph is identified by blank line on above and/or below current line. If the current line is blank line, then the paragraph below current line is executed. If the current line is blank and there is nothing else after that line, then nothing will be auto selected.

When you execute a query, result tab will be opened nad results or error will be shown. See Query Results for more information on how to navigate around the results

Query History

Every time a query is executed, it is added to Query History MRU (most recently used) lis along with execution time and execution count. You can view this history by clicking on the 'Click' icon in the Query Editor toolbar.

Favorite Scripts

If you have a set of queries that you often run, you can save them as Favorite scripts and execute them with ease.

Top 10 scripts (sorted by name) are shown in the dropdown which can be accessed by clicking on the Triangle Down button next to Star icon.

They are also shown in the Global Bookmark menu along with Objects/Records etc.,

Favorite Scripts can contain Query Vars.

Managing Favorite Scripts

To manage Favorite Scripts, click on the Star icon in the Query Editor toolbar which should show the below dialog.

Add/Edit Script

To add click the Plus icon or to edit click on the Pencil icon next to script you want to edit. It should show dialog with the following fields. Enter the values and click Save.

FieldTypeDescription
NameString (100)Name of the script. This is shown in the Fav Scripts/Global menu.
TypePicklistType of script. As of now, it is Query but later we will support Apex script as well.
DescriptionText Area (1000)Any description to help you understand the script
ConnectionsPicklistIf this snippet is applicable to only set of connections, then you can select that connection list. If so, this script will be shown in the dropdown only for those connections
FolderString (1000)Folder to organize the scripts. You can enter folder tree segments separted by /. For ex., Monitoring/Accounts
ScriptStringActual script

Delete Script

Click on the Trash icon next to the script you want to delete. After confirmation, the script will be deleted permanently.

Query Snippets

Query Snippets feature allows you to configure custom reusable soql statements with dynamic placeholders. These snippets show up as Content Assist suggestions. When selected, the app inserts a specified template into the editor and allows you to change placeholder values. Brobench comes with many standard snippets, so you can get started immediately.

Snippet Usage

Dynamic placeholders are indicated by ${n} or ${n:Default Value} where n is an integer starting with 1 and Default Value is the default value for that placeholder. Value 0 indicates final cursor position after all dynamic placeholders are navigated.

When a snippet is inserted into the editor, it will navigate you from placeholder position 1 and onwards. To go to the next placeholder, click Tab. You can also escape (esc) to abandon dynamic placeholders.

For ex., if this is the snippet content select Id${2} from ${1} where ${0};, when inserted into editor it will look like below

  • First, it will focus on the cursor after from keyword as that is placeholder ${1}. You can enter object name and click tab
  • Next, it will focus cursor next to Id field. If you want to add more fields, type those fields. Otherwise, just press Tab
  • Next (and finally), it will focus next to where keyword and concludes the dynamic placeholders as this is the final cursor position with value ${0}

List Snippets

Click on the Query Snippets icon (see Overview for location of the icon). When clicked, it shows the following dialog.

It shows both standard snippets and custom ones. Standard snippets cannot be edited or modified.

Add/Edit Snippet

Click on + icon in the toolbar to add new snippet or click on Pencil icon next to a custom snippet to edit it. It shows the dialog where you can enter details. The following table shows the fields shown in the dialog.

FieldTypeDescription
NameString (100)Name of the snippet which shows up in the Content Assist list of suggestions. Keep it brief and unique.
ContextPicklistThe context where this snippet should be used. Depending on the context, suggestions are filtered.
DescriptionText Area (1000)Any description to help you understand the snippet
ActiveBooleanIf Snippet is inactive, it will not be shown in the suggestions
ContentString (4000)Content of the snippet. As outlined in Snippet Usage you can include dynamic placeholders with optional default value

Delete Snippet

You can delete the snippet by clicking on Trash icon next to custom snippet. App will delete the snippet after confirmation.

Query Vars

Query Variables enables reusing a same query with different values in Where clause. Query Variable is defined as :queryVarName, which is similar to how they are defined in SOQL queries Apex language.

caution

Note that Apex allows you to add space between : and variable name but Brobench parser engine doesn't support that. For ex., : accountId is valid in Apex SOQL but is not valid in Brobench

Each Query can contain more than one variable. For ex., select * from account where name like :name and accountnumber = :mainAccountNum

Each variable can be used multiple times ex., select * from account where name like :name or parent.name like :name

When you execute a query with variables, the following dialog will be shown. Enter the values and then press Execute.

Query Vars in In clause

Query variables can be used in In clause. In such cases, the dialog will show input text area. Entered text will be split by new line, and each line is fed as single in clause value.

For example., for given query select id, name from account where accountnumber in (:accoutNums) and entered Query Var value of

123
456
789

then it will create the query select id, name from account where accountnumber in ('123', '456', '789')

Query Vars value history

Each query var, entered value will be persisted (except if query variable is used in In clause) and they can be recalled by the dropdown control as shown below

Saving Queries

Query editor auto saves the text you enter every once in a while as well as just before you execute any query. If you would like you can force save by clicking on the Save icon in the Query Editor

caution

If you have multiple tabs open, and editing the text in both places, it may not sync properly. If you do see the query you entered is not in the editor next time you open it, you can recall from the Query History

Execute As Options

Execute As options provide different ways to query the data you're looking for. These options can be accessed by clicking on the dropdown next to Execute button as shown below

Execute As Include Deleted

This option executes the query to include the deleted records. Note that you can also query to include deleted records using All Rows clause. Deleted records would be included when this option is selected or if query has All Rows clause hence you need not do both.

Execute As Count Query

Many times during the data analysis, you would want to see the count of records a query matches. You can do that in two ways 1) Run the query as is and see the total records matched 2) Change the select clause to count() and run the query

Both of them are not an efficient approach to get the records count. In the first case, it fetches the data unnecessarily and might also keep the pagination cursor open on the server side. In second case, you need to change the query replace select fields with count() each time you want to run and revert back to include fields.

Execute as Count Query option automates the second option. When select any query and select this option, app replaces the select fields with count() and shows the result.

Query Records by Ids

This option helps you view the records by set of ids or extracted ids from blob of the text. When you select this option, it shows the following dialog.

Enter ids or text containing ids and click View Records. The app will extract the ids and view those records.

Please keep these points in mind when you use this feature.

  • You can enter ids that belong to different objects. The app would group by the object and show multiple result tabs, one for each Object
  • You can enter blob of text and in such cases, app would try to extract the ids and show the result

Query Records by Ids in Clipboard

This is same as Query Records by Ids which automatically uses the text in clipboard

Query Results

When you execute a query, results are shown in the bottom panel of the Query Editor. This section walks through details of various UI elements and functionality.