labkey.executeSql: Retrieve data from a LabKey Server using SQL commands
In Rlabkey: Data Exchange Between R and 'LabKey' Server

labkey.executeSql

R Documentation

Retrieve data from a LabKey Server using SQL commands

Description

Use Sql commands to specify data to be imported into R. Prior to import, data can be manipulated through standard SQL commands supported in LabKey SQL.

Usage

labkey.executeSql(baseUrl, folderPath, schemaName, sql,
    maxRows = NULL, rowOffset = NULL, colSort=NULL,
    showHidden = FALSE, colNameOpt='caption',
    containerFilter=NULL, parameters=NULL)

Arguments

`baseUrl`	a string specifying the `baseUrl`for the labkey server
`folderPath`	a string specifying the `folderPath`
`schemaName`	a string specifying the `schemaName` for the query
`sql`	a string containing the `sql` commands to be executed
`maxRows`	(optional) an integer specifying the maximum number of rows to return. If no value is specified, all rows are returned.
`rowOffset`	(optional) an integer specifying which row of data should be the first row in the retrieval. If no value is specified, rows will begin at the start of the result set.
`colSort`	(optional) a string including the name of the column to sort preceeded by a “+” or “-” to indicate sort direction
`showHidden`	(optional) a logical value indicating whether or not to return data columns that would normally be hidden from user view. Defaults to FALSE if no value provided.
`colNameOpt`	(optional) controls the name source for the columns of the output dataframe, with valid values of 'caption', 'fieldname', and 'rname' See labkey.selectRows for more details.
`containerFilter`	(optional) Specifies the containers to include in the scope of selectRows request. A value of NULL is equivalent to "Current". Valid values are "Current": Include the current folder only "CurrentAndSubfolders": Include the current folder and all subfolders "CurrentPlusProject": Include the current folder and the project that contains it "CurrentAndParents": Include the current folder and its parent folders "CurrentPlusProjectAndShared": Include the current folder plus its project plus any shared folders "AllFolders": Include all folders for which the user has read permission
`parameters`	(optional) List of name/value pairs for the parameters if the SQL references underlying queries that are parameterized. For example, parameters=c("X=1", "Y=2").

Details

A full dataset or any portion of a dataset can be imported into an R data frame using the labkey.executeSql function. Function arguments are components of the url that identify the location of the data and the SQL actions that should be taken on the data prior to import.

See labkey.selectRows for a discussion of the valid options and defaults for colNameOpt.

Value

The requested data are returned in a data frame with stringsAsFactors set to FALSE. Column names are set as determined by the colNameOpt parameter.

Author(s)

Valerie Obenchain

Examples

## Not run: 

## Example of an expicit join and use of group by and aggregates
# library(Rlabkey)

sql<- "SELECT AllTypesCategories.Category AS Category, 
    SUM(AllTypes.IntFld) AS SumOfIntFld,
    AVG(AllTypes.DoubleFld) AS AvgOfDoubleFld
    FROM AllTypes LEFT JOIN AllTypesCategories
    ON (AllTypes.Category = AllTypesCategories.TextKey)
    WHERE AllTypes.Category IS NOT NULL
    GROUP BY AllTypesCategories.Category"

sqlResults <- labkey.executeSql(
    baseUrl="http://localhost:8080/labkey",
    folderPath="/apisamples",
    schemaName="lists",
    sql = sql)

sqlResults


## End(Not run)

Rlabkey documentation built on Nov. 8, 2023, 1:06 a.m.