The Query Builder

This class will teach you how to access and use the Query Builder in WorkXpress. The Query Builder provides you with a rich, visual, WYSIWYG interface that easily enables you to visually build and manage complex queries to retrieve information from your data layer that in a traditional application would need to be written by a database developer or administrator using a highly specialized language.

Prerequisites

• The Data Layer - Items and Relationships
• The Presentation Layer - Layouts and Fields
• The Logic Layer - Actions

What You'll Learn

After completing this lesson, you should be able to answer these questions:

1. What is the Query Builder?
2. How do I access the Query Builder?
3. What are the four different sections of the Query Builder and what do they do?
4. What are the elements of a Query?

What is a Query?

Put simply, a query is a lookup of data from a database.  Although that may sound like a tricky thing to accomplish, in WorkXpress it's very easy because you use the Workxpress visual Query Builder Tool to look up your data.

Where is it?

You may access the Query Builder by clicking its icon () whenever it's displayed as part of a Block's settings in the Block Editor. That is, when a query is potentially needed, the Query Builder will make itself available to you. It will launch in a separate browser window.

Often, the Query Builder will launch in tandem with the Expression Builder because both tools perform the related function of allowing you to manipulate parts of the data layer. We'll cover the Expression Builder in our next lesson.

The Query Builder has Four Sections

You will need to familiarize yourself with each of the four sections of the Query Builder Tool.

Context Section

Use the Context Section to change the starting point (the starting "node") for your query.

Query Section

In the Query Section, you will specify the details of your data query. The Query Section provides an interactive visual map of your query.

Selection Section

As you complete your query you will likely make some selections for what you want to see in your results.  The Selection Section shows what you have selected with the green arrow in the query section.

Preview Section

The Preview Section queries the database based on the query you've built and returns the actual results of that query given your current context (see Context).

Four Elements of a Query

A Query may include the following four elements, broken out by function:

What Items or Relationships am I getting my data from?

• Node
• Filter
• Path

Which data do I want to get?

• Selection

Taken together, these are the four elements of a query, and will allow you to get any data from any part of WorkXpress.  These elements are further defined below.

Node

A node is visually represented in the Query Builder as a row or level of the query (see the image below). You can think of a node as a representation of a set of Items or Relationships from which you can get Fields of data.  A more technical description is that a node is any level of the query that is represented by either an Item or a Relationship, or in some cases, a branch of a path (covered below).

Nodes are important because you can select them, you can usually select Fields of data from them, and you can apply filters to them.

Remember that the node your query begins on (the topmost node) is a special node in that it is the result of your current "context" (see Context).

The three highlighted items in the image below are each nodes:

Filter

A filter is used to restrict a node.  For example, if your node is composed of "Accounts", you might add a filter to say "Accounts <Where the status is "hot lead">.  In this example, the phrase "where the status is hot lead" is the filter you have applied to your node.  The result then is that your list of Accounts will only include the smaller set of accounts that happen to be in the "hot lead" status at the time the query is performed.

Think of a filter as the tool you use to narrow down your set of Items or Relationships.

Below is an example of a filter and how its icon appears in the Query Builder map when applied.

Path

A path is the manner by which two nodes are connected.  In database terminology this is sometimes referred to as a "join". For example, you might build a query of all of your Accounts and each of their Employees.  You would start on the Accounts Item Type, path across your Employer to Employee Relationship, and then end on your Contacts item type.

Some paths can be simple, others more complex, but paths do impart complex meanings governing how you query your database for sets and subsets of information.

The image below shows the path your query follows, represented by the highlighted solid black lines:

Must, Must Not, May

By default, all paths are "Must" paths.  That is, anything at the end of the path must exist in order for the query to return data.  There are times, however, when you will want to build a path where the things on the path "Must Not" exist, or even where they "May" exist.

The image on the left shows must paths. The Query Builder has been used to change those paths in the righthand image to a "Must Not" path between the Contact Item and the Employee to Employer Relationship, represented by a red path line, and a "May" path between the Relationship and the Company Item, represented by a grey line.

And/Or Branches

At any point in a path, you may add a branch.  A branch is simply a split in the path, allowing it to take two different directions.

By default, all branches are "And" branches, meaning that "Branch A AND Branch B" must both be true.  However, you can change a branch to be an "OR" branch, meaning that either "Branch A OR Branch B" must be true, but not necessarily both.

Selection

Up to this point we've discussed the three elements of a query that result in determining a set of Items or Relationships that you are seeking to act upon.

However, after you've defined the starting point, nodes and paths of your query, you will likely want to pick some results.  These choices represent your "Selection"...they are the reason for your query.  The selection is "the data you want to get from the database".

Selecting Items or Relationships

One use of the Selection option is to choose a specific set of Items or Relationships.  To do this you would click on the node and in the flyout menu choose "select this block".  The result will be that the Selector (the green arrow in the interface) will rest on that node.

Selecting Fields

Many times, you will use the Query Builder tool to get elements of data. Once you've built your query, click on any node and make the "Select Fields From this Block" selection.  You may then select whichever Fields you wish to pull data from.  The Query Builder will pull that data for each Item or Relationship represented by the node you are selecting from.

Building a Query

There are several activities you can perform in the course of building a query. Many of these activities can be performed in any order you choose. Building your query could involve the following activities:

• Choosing a starting node (Start my query on <this/these Item(s)>)
• Adding Pathing - moving across Relationships to new Item Types ("JOIN <this> to <that>)
• Path Restrictions (Join where <target> may/must/must not EXIST)
• Path Branching ("Path a AND/OR Path b")
• Node Filtering ("all of these WHERE <your filter is true>)
• Choosing Items or Relationships
• Choosing Fields
• Choosing a stopping point demarcated by the Selector

In this next section, you'll learn how to perform each of the above tasks as you build your queries.  Once you've built your first few queries, you'll realize how easy it is, and the process will become second nature.

Choosing a Starting Node

By default, your query will be setup to start on the Item(s) given to it by the Block from which you accessed the Query Builder (said differently, the query will start based on the context of its parent Block; context is discussed here). You may change that context by clicking the "Change" link, located beside the description of the current context. Doing so will open a new page that will allow you to change the starting point (the context) of the query:

Several options are available to change the starting point of your query to:

Use Parent's context with no changes - The default setting whereby context is simply inherited from the parent.  Build your query by branching off of this starting point.

Get context from my page - Allows you to change context to a particular node from the page on which you're currently working.  Chose the node you wish to start your query on and build your query from there.

Get context from the application environment - Allows you to change context to that of the current application or current user.  Make your selection, and build your query from the user or application nodes, filtered to be about only the current user or application.

Derive context from a list of items - Allows you to change context to any Item Type you've created for your application as well as Item Types created by WorkXpress when you spun up your project.  Choose this Item Type, and build your query from that search result list ("from that node").

In summary, you may view and change the starting context for your query using the context section of the Query Builder. 

Adding Pathing

Clicking on any node will generate a menu of choices for that node.  The first choice is always the "Add a Block on a new level" choice.  This allows you to create a network of interconnected nodes (usually Item Types and Relationship Types).

The following images illustrate the procedure of developing a simple path. Step 1: click on any node:

Step 2: use the flyouts to select Items and Relationships you wish to path across:

Step 3: review your results.  In this query, you are joining all of your Contacts and Companies using the Employee to Employer Relationship:

Restricting Pathing

At the top of any segment of a path, simply click the node or asterisk and in the resulting flyout menu choose "Filters", then "Restrict Paths", and then whether you prefer the options of must, must not, or may.

The below chart shows your query from before with the defaulted must paths in place (the solid black lines):

To change your path restriction, start by clicking on the Node whose path you wish to restrict, select the "filter" flyout menu, then "Restrict Paths" to make your choice. We've chosen to use a must not path in the image below:

Finally, review your results.  Notice the red line which signifies a must not path.  In this example, we've built the query to restrict the path so that the connection between your Contact and your Employee to Employer Relationship must not exist.

Adding a Branch

You can add a branch at any node in your query.  

Adding a Branch automatically using Pathing

The simplest branch to add occurs automatically whenever you add a path to a node that already has a child path associated with it.  Any node may have two or more paths extending from it, so the system automatically creates a branch.

To create this type of branch, simply follow the procedure for "Adding a Node" using a node that already has a pre-existing branch.

Creating a Branch structure

You may click on any node, and in the "filters" menu, you will find "Add a Branch".  Clicking this filter type creates the structure of a branch which you can later add nodes to.

Filter a Node

Any node may be filtered by the data in the Fields that are associated with that node.

Click on the node and choose "Filters" then "Filter this Block".  The result will appear as shown below:

You can see that filtering by any Field available for that Block is possible.

Evaluated Filters

One of the Workxpress Query Builder's more interesting and powerful features is the ability to build dynamic evaluations.  These are evaluations where the value being evaluated against is itself another variable!  What's more, there are no limits to how many times you can nest these evaluated filters.

For example, a normal filter might be "where the State is <Pennsylvania>".  An evaluated filter instead might be something like "where the State is <check the Current User, find which Sales Team he is related to, find which State that Sales Team is responsible for, and insert that State here>".

You create evaluated filters while in the process of creating a static filter. In the location where the static filter is entered, look for an "Expression Builder" control.  Use that Expression Builder control to define your dynamic, evaluated filter.

Choosing Items or Relationships

The goal of some Queries is simply to choose one or many Items, or one or many Relationships.  To do this, first you must develop your path.  Second, you must click on any node and choose "Select This Block", as shown below:

Choosing Fields

As mentioned above, the goal of queries is to get data from the database, often by choosing a specific Field Block.  There are two different interfaces that allow you to accomplish this task.  One allows you to choose a single Field Block, the second allows you to choose many Fields from the Block.

Setting the Stopping Point

Sometimes, particularly when building lists, it is necessary to adjust the "endpoint" of your query.  To do this, simply click on any node, and choose the option "Select This Block".  Notice that the Selector adjusts in your query to point at the node where you chose to end that query.

Creating New Item Types and Relationship Types Inline

The Query Builder allows you to create new Item Types and Relationship Types from within the pathing flyout menus.  In each case, the Query Builder will launch a wizard to dynamically create new Relationship or Item Types.  The example below shows the "Create Relationship" wizard launched while building a path:

Conclusion

This class showed you how to access and use the Query Builder in WorkXpress to build powerful queries to retrieve data.  You learned what a query was, where to find the Query Builder, what the various parts of a query are, and how to build them.  Remember, there are often many ways to build a query that will give you a similar result, so its good to have a solid understanding of the lessons learned in this course.

Often the Query Builder works in tandem with our next tool covered in The Expression Builder.  Keep it up!  You're getting closer to Cloud Mastery!