Standard queries allow users to access certain data using pre-defined joins between the many related tables of a Slate database. With a Configurable Joins query, joins are determined by the user, based on the exact information needed. This article describes how to join data in a Configurable Joins query as well as the circumstances under which different types of filters and exports should be added in order to achieve the desired results.
For a detailed introduction to the fundamentals of Configurable Joins and an overview of the available functionality, review the articles in the Configurable Joins section of the Knowledge Base.
- 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.
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:
- 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.
- One-to-One relationships exist between some of these tables, such as School to Person.
- One-to-Many relationships exist between others, such as Person to Applications. In order to add filters and exports in these cases, it is necessary to select which one of the possible many related records is needed.
- 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.
Click or drag Export to add direct exports:
Click or drag Filter to add direct filters:
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.
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.
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.
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.
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.
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.
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: