Queries perform many functions in a database—most commonly to isolate, return, and display specific data. Queries can also be used to build reports in Report Builder, create Deliver recipient mailing lists, configure data layers for Voyager, and much more! Click here for an introduction to querying.
Configurable Joins offer greatly extended capabilities for data extraction by providing access to more tables in the database, as well as a “smart” join menu that includes only query populations or tables that are related to the starting population, which then includes more possible related populations as more joins are added to the query.
What are Configurable Joins?
A “join” combines data from multiple tables for querying based on relationships between them. Configurable joins allow users to specify how a related table should be joined to a query base.
Configurable Joins significantly expand access to the database by exposing more of the underlying tables that were previously available only through custom SQL or direct SQL access. It also offers greater flexibility in query building. For example, a table that utilizes ranks could be joined multiple times to the same base. Each join pertains to a single record out of multiple possible records (for example, one join on Address Rank #1, and then an additional join for Address Rank #2).
Databases
Slate data are stored in tables, and a collection of tables together comprise a Slate database. Slate databases can be described as relational databases, which means that the database tables are interrelated.
For example, when viewing a person record in Slate, an additional tab appears when the person also has an application.
This application record is represented as a row in the Application table. It is possible for a person to have multiple applications, which then take up multiple rows in the Application table.
How does Slate know that these two rows in the Application table also belong to the same individual (i.e. the same row in the Person table)? In a Slate database, there is a one-to-many relationship between the Person table and the Application table; that is, one instance on the Person table could be related to more than one instance on the Application table, but one instance on the Application table can be related back to only one instance on the Person table.
The Slate Query Builder enables access for viewing data in the underlying tables, as well as functionality for extracting and manipulating data. Currently, the Query Builder provides access to table data through a standard list of Query populations or bases. Many of these query populations present table data in a predetermined way: for example, the Prospects query population automatically excludes person records with an Opt-Out tag.
Creating a New Query
- Navigate to Queries / Reports and select either New Query or Quick Query.
- Select a Type of Configurable Joins Library and a Category:
- Records - Query bases for records (e.g. person records, application records)
- Related - Query bases that relate to a parent record (e.g. schools, tests, sports)
- System - Query bases for system tables in Slate (e.g. tags, bins, rounds)
- Choose the appropriate Base from the category. The base determines what each row of the query will represent.
Adding Exports and Filters
Important! Transitioning to Configurable joins?
All new queries should be built using Configurable Joins. To get started with querying with configurable joins, refer to:
| Data Needed | Description |
|---|---|
| Data from the same table | if a field is scoped to the query base, it will be available automatically for use as a direct export/filter. |
| Data from a related table |
if a field is scoped to a different, but related, query base, it can be accessed either via an extended export/filter or by adding a join to the query itself.
|
| Calculated data or data about the many | if the data needed is an aggregate of many related records, the result of a formula or concatenation, an existence, or other type of calculation, then subquery exports or subquery filters should be used. As with any standard query in Slate, exports determine the columns while filters limit the rows. Unlike a standard query, however, several different kinds of exports and filters can be added to a Configurable Joins query based on what data is needed: |
Adding Exports or Filters for Data from the Same Table
|
|
Click or drag Export to add direct exports:
Click or drag Filter to add direct filters:
|
Adding Exports or Filters for Data from a Related Table
|
|
Click or drag Export to add extended exports:
Click or drag Filter to add extended filters:
Click or drag Join to add a join to a related table:
|
When adding filters or exports for data on a related table, select which one of the possible one-to-many relationships should be joined.
Important!
Adding a join does not change the number of rows returned in the query.
One way to approach the selection of the join is to ask yourself two questions:
- How should the possible rows on the related table be sorted?
- When the table is sorted in a particular way, which row is needed (i.e. the top row, the next highest, and so on)?
By default, Slate ranks, or sorts, each table using certain pre-defined ranking criteria, as explained in the article on Determination of Table Ranks. In the Slate Template Library, many filters and exports make use of these Slate-defined table rankings to return data about specific rows, such as School 1 or School by Level of Study, Rank.
Joins for Rank, Rank Overall or Type, Rank make use of these pre-defined Slate rankings, and are known as Library Joins. Choose one of these options when the objective is to sort the related table using the default ranking criteria. Once selected, these join options will prompt you to select the specific row based on that ranking.
- Example: Join in data about the Rank 1 Overall School:
Alternatively, if the table should be sorted based on different, user-defined criteria rather than the Slate ranking criteria, and then the row selected based on that custom ranking, select the join option without 'Rank' (e.g. Addresses, Decisions, Schools, Relations, etc.). These are known as Subquery Joins, which allow you to specify a sort for those cases where the related table needs to be sorted in a custom way, and then use the Row Offset to indicate whether the top row, the next highest, or some other row from the table should be joined when the table is sorted using those criteria.
- Example: Join in data about the top (row offset 1) school when sorting schools by degree conferred date (desc):
Best Practice: Rename all joins to be descriptive. Always include a sort if using a custom ranking.
By default, the name of the join will reflect the join type (e.g. Application by Rank, Application by Rank Submitted). Because the name of the join will be added to each export or filter that uses it, providing a descriptive name (e.g. Rank 1 Application) helps to prevent confusion by specifying which one is being joined. If using a custom ranking, always include a sort and then rename the join accordingly.
When should I add a Join to the query and when should I use an Extended Export/Filter?
- Extended Exports and Filters allow you to see, at a glance, all of the fields that are available on a related base. This can be helpful when, as your first step, you want to decide what data points you want to add and then decide which of the possible one-to-many relationships those data points should come from.
- Joins allow you to see all the possible ways in which a different base relates to the base of your query.
- This can be helpful when, as your first step, you want to decide which of the possible one-to-many relationships to make available for use in the query, and then decide what data points from that joined relationship to include as filters or exports.
- In addition, if the data you need is more than one step removed from the base of the query, adding the necessary joins to the query will likely be the most efficient way to access the data you need. For example: From a Person query, join to the Rank 1 Application and then from the Rank 1 Application join to the Rank 1 Decision.
The end result will be the same: whether you first add an extended export/filter or a join, Slate will prompt you to define which records from a related table should be used in this query.
As an Extended Export:
As a Join:
Adding Exports and Filters for Calculated Values or Data about the Many
>
Add any Subquery Exports or Subquery Filters as desired:
|
|
|
A subquery filter or subquery export creates a query within a query, and then performs an action, such as a calculation, on the results of that query. Subqueries make it possible to look across the many of the one-to-many relationships in Slate to determine which values to return.
For more details on the outputs of subquery exports and the aggregate functions of subquery filters, see the corresponding articles in the Configurable Joins category of the Knowledge Base.
Tip
When using a subquery filter, determine first whether the aggregate function should be performed on related records, on a field value on the same table, or on a field value on a related table.
- To filter the query results based on an aggregate of related records, a join must be added within the subquery filter. For example, to filter for person records that have an application in a specific round, a join from Person to Applications is needed, followed by a join from Applications to Lookup Round.
- If the goal is to perform the function on a field value on the same table, no join is needed. Instead, an export for that field should be added within the subquery filter. For example, to filter for person records where a Birthdate exists, an export of Birthdate should be added.
- Finally, in order to reference field values on a related table, both a join and an export need to be added to the filter. For example, to filter for person records that have a School GPA below a 3.0 for at least one of the schools they attended, a join from Person to Schools is needed, along with an export for GPA.
Important!
Historically, the Prospects base excluded by default any person records where the Opt Out tag was set. The Configurable Joins base of Person does NOT automatically exclude records with the Opt Out tag: instead, it replicates the functionality of the historical Prospects (including opt-outs) base, which included all person records.
To exclude person records with the Opt Out tag from a query on the Person base, use a subquery filter with a join from Person to Tag and Tag to Lookup Tag, and filter for Lookup Tag / Name IN Opt Out. Finally, choose the Aggregate of Not Exists:
