Module Query Views

2024-03-28

The queries directory holds SQL queries, and ways to surface those queries in the LabKey Server UI. The following file types are supported:
  • SQL queries on the database (.SQL files)
  • Metadata on the above queries (.query.xml files).
  • Named views on pre-existing queries (.qview.xml files)
  • Trigger scripts attached to a query (.js files) - these scripts are run whenever there an event (insert, update, etc.) on the underlying table.
In this step you will define a "query view" on the Peptides table, in particular on the default query of the Peptides table, a built-in query on the server. Notice that the target schema and query are determined by the directories the view rests inside -- a view located at "ms2/Peptides/SomeView.qview.xml" means "a view named SomeView on the Peptides query in the ms2 schema".

Additionally, if you wish to just create a default view that overrides the system generated one, be sure to just name the file as .qview.xml, so there is no actual name of the file. If you use default.qview.xml, this will create another view called "default", but it will not override the existing default.

Create an XML-based SQL Query

  • Add two directories (ms2 and Peptides) and a file (High Prob Matches.qview.xml), as shown below.
  • The directory structure tells LabKey Server that the view is in the "ms2" schema and on the "Peptides" table.

reportDemo │ module.properties └───resources     ├───queries     │ └───ms2     │ └───Peptides     │ High Prob Matches.qview.xml     │     ├───reports     └───views

View Source

The view will display peptides with high Peptide Prophet scores (greater than or equal to 0.9).

  • Save High Prob Matches.qview.xml with the following content:
<customView xmlns="http://labkey.org/data/xml/queryCustomView">
<columns>
<column name="Scan"/>
<column name="Charge"/>
<column name="PeptideProphet"/>
<column name="Fraction/FractionName"/>
</columns>
<filters>
<filter column="PeptideProphet" operator="gte" value="0.9"/>
</filters>
<sorts>
<sort column="PeptideProphet" descending="true"/>
</sorts>
</customView>

  • The root element of the qview.xml file must be <customView> and you should use the namespace indicated.
  • <columns> specifies which columns are displayed. Lookups columns can be included (e.g., "Fraction/FractionName").
  • <filters> may contain any number of filter definitions. (In this example, we filter for rows where PeptideProphet >= 0.9). (docs: <filter>)
  • <sorts> section will be applied in the order they appear in this section. In this example, we sort descending by the PeptideProphet column. To sort ascending simply omit the descending attribute.

See the View

To see the view on the ms2.Peptides table:

  • Build and restart the server.
  • Go to the Peptides table and click (Grid Views) -- the view High Prob Matches has been added to the list.
    • Select (Admin) > Developer Links > Schema Browser.
    • Open ms2, scroll down to Peptides.
    • Select (Grid Views) > High Prob Matches.

Previous Step | Next Step (3 of 5)