Cross-Folder Queries

You can perform cross-folder queries by identifying the folder that contains the data of interest during specification of the dataset. The path of the dataset is composed of the following components, strung together with a period between each item:

  • Project - This is literally the word Project, which resolves to the current folder's project.
  • Path to the folder containing the dataset, surrounded by quotes. This path is relative to the current project. So a dataset located in the Home > Study > demo subfolder would be referenced using "Study/demo/".
  • Schema name - In the example below, this is study
  • Dataset name - Surrounded by quotes if there are spaces in the name. In the example below, this is "Physical Exam"
Note that LabKey Server enforces a user's security roles when they view a cross-folder query. To view a cross folder/container query, the user must have the Reader role in each of the component folders/containers which the query draws from.

Example

The Edit SQL Query Source topic includes a simple query on the "Physical Exam" dataset which looks like this, when the dataset is in the local folder:

SELECT "Physical Exam".ParticipantID, ROUND(AVG("Physical Exam".Temp_C), 1) AS AverageTemp
FROM "Physical Exam"
GROUP BY "Physical Exam".ParticipantID

This query could reference the same dataset from a sibling folder within the same project. To do so, you would replace the string used to identify the dataset in the FROM clause ("Physical Exam" in the query used in this topic) with a fully-specified path. For this dataset, you would use:

Project."Study/demo/".study."Physical Exam"

Using the query from the example topic as a base, the cross-folder verson would read:

SELECT "Physical Exam".ParticipantID, ROUND(AVG("Physical Exam".Temp_C), 1) AS AverageTemp
FROM Project."Study/demo/".study."Physical Exam"
GROUP BY "Physical Exam".ParticipantID

Cross-Project Queries

You can perform cross-project queries using the full path for the project and folders that contain the dataset of interest. To indicate that a query is going across projects, use a full path, starting with a slash. The syntax is “/<FULL FOLDER PATH>”.<SCHEMA>.<QUERY>

  • Full path to the folder containing the dataset, surrounded by quotes. This lets you access an arbitrary folder, not just a folder in the current project. So a dataset located in the Home->Study->demo subfolder would be referenced from another project using "/Home/Study/demo/".
  • Schema name - In the example below, this is study
  • Dataset name - Surrounded by quotes if there are spaces in the name. In the example below, this is "Physical Exam"
Example

The example shown above can be rewritten using cross-project syntax by including the entire path to the dataset of interest in the FROM clause, preceded by a slash.

“/Home/Study/demo/”.study."Physical Exam"

Using the query from the example topic as a base, the cross-folder verson would read:

SELECT "Physical Exam".ParticipantID, ROUND(AVG("Physical Exam".Temp_C), 1) AS AverageTemp
FROM "/Home/Study/demo/".study."Physical Exam"
GROUP BY "Physical Exam".ParticipantID

Fields with Dependencies

A few LabKey fields/columns have dependencies. To use a field with dependencies in a custom SQL query, you must explicitly include supporting fields.

To use Assay ID in a query, you must include the run's RowId and Protocol columns. You must also use these exact names for the dependent fields. RowId and Protocol provide the Assay ID column with data for building its URL.

If you do not include the RowId and Protocol columns, you will see an error for the Run Assay ID field. The error looks something like this:

"KF-07-15: Error: no protocol or run found in result set."

Discussion

previousnext
 
expand all collapse all