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) > Go To Module > Query.
- 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.
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. It's important to note that nothing has changed about the underlying query on which your user-defined one is based. You could further define a new query based on the user-defined one as well. Each layer of query provides a new way to customize how your data is presented for different needs.
Related Topics