Modules: Query Metadata: /Documentation

Modules: Query Metadata

To provide additional properties for a query, you may optionally include an associated metadata file for the query.

If supplied, the metadata file should have the same name as the .sql file, but with a ".query.xml" extension (e.g., PeptideCounts.query.xml). For details on setting up the base query, see: Module SQL Queries.

Examples
Metadata Overrides

For syntax details, see the following:

Examples

See Query Metadata: Examples.

The sample below adds table- and column-level metadata to a SQL query.

<query xmlns="http://labkey.org/data/xml/query">
  <metadata>
      <tables xmlns="http://labkey.org/data/xml">
          <table tableName="ResultsSummary" tableDbType="NOT_IN_DB">
              <columns>
                  <column columnName="Protocol">
                      <fk>
                          <fkColumnName>LSID</fkColumnName>
                          <fkTable>Protocols</fkTable>
                          <fkDbSchema>exp</fkDbSchema>
                      </fk>
                  </column>
                  <column columnName="Formulation">
                      <fk>
                          <fkColumnName>RowId</fkColumnName>
                          <fkTable>Materials</fkTable>
                          <fkDbSchema>exp</fkDbSchema>
                      </fk>
                  </column>
                  <column columnName="DM">
                      <formatString>####.#</formatString>
                  </column>
                  <column columnName="wk1">
                      <columnTitle>1 wk</columnTitle>
                      <formatString>####.#</formatString>
                  </column>
                  <column columnName="wk2">
                      <columnTitle>2 wk</columnTitle>
                      <formatString>####.###</formatString>
                  </column>
              </columns>
          </table>
      </tables>
  </metadata>
</query>

Metadata Overrides

Metadata is applied in the following order:

1. JDBC driver-reported metadata.
2. Module schemas/<schema>.xml metadata.
3. Module Java code creates UserSchema and FilteredTableInfo.
4. For study datasets and sample types, you can apply query metadata to all instances of those types within a folder.

For study datasets, use /queries/study/studyData.query.xml for metadata to apply to all datasets.
For sample types, use /queries/samples/AllSampleTypes.query.xml for metadata to apply to all sample types in the container.

5. Module queries/<schema>/<query>.query.xml metadata.

First .query.xml found in the active set of modules in the container.

6. User-override query metadata within LabKey database, specified through the Query Schema Browser.

First metadata override found by searching up container hierarchy and Shared container.

7. For LABKEY.QueryWebPart, optional metadata config parameter.

LabKey custom queries will apply the metadata on top of the underlying LabKey table's metadata. A Linked Schema may have metadata associated with the definition which will be applied on top of the source schema's metadata. The LinkedSchemas tables and queries may also have module .query.xml and metadata overrides applied using the same algorithm on top of the source schema's tables and queries.

Assays will also apply query metadata /assay/ASSAY_PROVIDER_NAME/queries/Data.query.xml, Runs.query.xml, and Batches.query.xml to their data, runs, and batches tables, respectively. This is a convenient place to put settings for columns that are shared across all assay designs of a given assay provider type.

An exception to this overriding sequence is that if a foreign key is applied directly to a database table (1), it will not be overridden by metadata in a schemas/<schema>.xml file (2), but can be overridden by metadata in a queries/<schema>/<query>.query.xml file (5).

LabKey Support

LabKey Support