SQL queries are a powerful way to shape different ways to view data. In LabKey Server, queries are the main way to surface data from the database: using a query, you pick the columns you want to retrieve, and optionally apply any filters and sorts. SQL Queries behave like "peers" to other tables in the database: queries are added to the database schema alongside the original, core tables. You can query one table at a time, or create a query that combines data from multiple tables. Queries also provide staging for reports: start with a base query and build a report on top of that query. Queries can be created through the graphical user interface (as shown in this topic) or through a file-based module.

LabKey Server provides a number of mechanisms to simplify SQL syntax:

  • LabKey SQL: LabKey SQL is a SQL dialect that translates your queries into the native syntax of the SQL database underlying your server, whether it is PostgreSQL or Microsoft SQL Server. This lets you write in one SQL dialect but communicate with many SQL database implementations.
  • Lookups: Lookups join tables together using an intuitive syntax.
  • Query Metadata: Add additional properties to a query using metadata xml files, such as: column captions, relationships to other tables or queries, data formatting, and links
The following step-by-step tutorial shows you how to create a SQL query and begin working with it.

Create a SQL Query

In this example, we will create a query based on the Users table in the core schema.

  • Select (Admin) > Developer Links > Schema Browser.
  • Open the core schema in the left hand pane and select the Users table. Then click the button Create New Query. (This tells LabKey Server to create a query based on the table core.Users.)
  • On the New Query page:
    • Provide a name for the query (in the field "What do you want to call the new query?").
    • Confirm that the Users table is selected (in the field "Which query/table do you want this new query to be based on?")
    • Click Create and Edit Source.
  • LabKey Server will provide a "starter query" of the Users table -- a basic SELECT statement for all of the fields in the table -- essentially a duplicate of the Users table. Typically, you would modify this "starter query" to fit your needs, adding WHERE clauses, JOINs to other tables, or substituting an entirely new SQL statement. But for this tutorial, we will just use the "starter query" unchanged.
  • Click Save and Finish.
  • The results of the query are displayed in a data grid, similar to the grid shown below -- though yours will show different data.
  • Click core Schema to return to the query browser view of this schema.
  • Notice that your new query appears under user-defined queries.

Query Metadata

Each query has accompanying XML that defines properties, or metadata, for the query. In this step we will add properties to the query by editing the accompanying XML. In particular we will:

  • Change the data type of the UserId column, making it a lookup into the Users table. By showing a clickable name instead of an integer value, we can make this column more human-readable. We will accomplish this by using the graphical user interface, which will write out the XML automatically.
  • Modify the way it is displayed in the grid. We will accomplish this by editing the XML directly.
  • Click Edit Metadata.
  • On the UserId row, click in the Type column where it shows the value Integer
  • In the Choose Field Type dialog, select User. This will create a lookup between your query and the Users table.
  • Click Apply.
  • Click View Data, and click Save to confirm your changes.
  • Notice that the values in the User Id column are no longer integers, but text links -- this reflects the fact that User Id is now a lookup into the Users table. Click a value in the User Id column to see the corresponding record in the Users table (where you can see the actual user ID integer is unchanged).
This lookup is defined in the XML metadata document. Click back in your browser to return to the query, and let's see what the XML looks like.
  • Click core Schema to return to the Query Browser.
  • Click Edit Source and then select the XML Metadata tab.
  • The XML metadata will appear in a text editor. Notice the XML between the <fk>...</fk> tags. This tells LabKey Server to create a lookup (aka, a "foreign key") to the Users table in the core schema.
  • Next we will modify the XML directly to hide the "Display Name" column in our query. We don't need this column any longer because the User Id column already displays this information.
  • Add the following XML to the document, directly after the </column> tag (i.e directly before the ending </columns> tag.
<column columnName="DisplayName">
<isHidden>true</isHidden>
</column>
  • Click Save.
  • Click the Data tab to see the results without leaving the query editor.
  • Notice that the Display Name column is no longer shown.
  • Click the XML Metadata tab and now add the following XML immediately after the column section for hiding the display name. This section will display the Email column values in red.
<column columnName="Email">
<conditionalFormats>
<conditionalFormat>
<filters>
<filter operator="isnonblank"/>
</filters>
<textColor>FF0000</textColor>
</conditionalFormat>
</conditionalFormats>
</column>
  • Click Save & Finish.
  • Now that you have a SQL query, you can display it directly by using a query web part, or use it as the basis for a report, such as an R report or a visualization.

Related Topics

Discussion

previousnext
 
expand all collapse all