Rlabkey (version 3.3.0)

labkey.selectRows: Retrieve data from a labkey database

Description

Import full datasets or selected rows into R. The data can be sorted and filtered prior to import.

Usage

labkey.selectRows(baseUrl = NULL, folderPath, schemaName, queryName,
    viewName = NULL, colSelect = NULL, maxRows = NULL,
    rowOffset = NULL, colSort = NULL, colFilter = NULL,
    showHidden = FALSE, colNameOpt="caption",
    containerFilter = NULL, parameters = NULL,
    includeDisplayValues = FALSE, method = "POST")

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.

Arguments

baseUrl

a string specifying the baseUrlfor the labkey server

folderPath

a string specifying the folderPath

schemaName

a string specifying the schemaName for the query

queryName

a string specifying the queryName

viewName

(optional) a string specifying the viewName associated with the query. If not specified, the default view determines the rowset returned.

colSelect

(optional) a vector of strings specifying which columns of a dataset or view to import.

• The wildcard character ("*") may also be used here to get all columns including those not in the default view.

• If you include a column that is a lookup (foreign key) the value of the primary key for that target will be returned.

• Use a backslash character ("/") to include non-primary key columns from a lookup target (foreign key), e.g "LookupColumnName/targetColumn".

• When using a string to specify the colSelect set, the column names must be separated by a comma and not include spaces between the names.

maxRows

(optional) an integer specifying how many rows of data 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, the retrieval starts with the first row.

colSort

(optional) a string including the name of the column to sort preceded by a “+” or “-” to indicate sort direction

colFilter

(optional) a vector or array object created by the makeFilter function which contains the column name, operator and value of the filter(s) to be applied to the retrieved data.

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'

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").

includeDisplayValues

(optional) a logical value indicating if display values should be included in the response object for lookup fields.

method

(optional) HTTP method to use for the request, defaults to POST.

Author

Valerie Obenchain

Details

A full dataset or any portion of a dataset can be downloaded into an R data frame using the labkey.selectRows function. Function arguments are the components of the url that identify the location of the data and what actions should be taken on the data prior to import (ie, sorting, selecting particular columns or maximum number of rows, etc.).

Stored queries in LabKey Server have an associated default view and may have one or more named views. Views determine the column set of the return data frame. View columns can be a subset or superset of the columns of the underlying query (a subset if columns from the query are left out of the view, and a superset if lookup columns in the underlying query are used to include columns from related queries). Views can also include filter and sort properties that will make their result set different from the underlying query. If no view is specified, the columns and rows returned are determined by the default view, which may not be the same as the result rows of the underlying query. Please see the topic on Saving Views in the LabKey online documentation.

In the returned data frame, there are three different ways to have the columns named: colNameOpt='caption' uses the caption value, and is the default option for backward compatibility. It may be the best option for displaying to another user, but may make scripting more difficult. colNameOpt='fieldname' uses the field name value, so that the data frame colnames are the same names that are used as arguments to labkey function calls. It is the default for the new getRows session-based function. colNameOpt='rname' transforms the field name value into valid R names by substituting an underscore for both spaces and forward slash (/) characters and lower casing the entire name. This option is the way a data frame is passed to a script running in a LabKey server in the R View feature of the data grid. If you are writing scripts for running in an R view on the server, or if you prefer to work with legal r names in the returned grid, this option may be useful.

For backward compatibility, column names returned by labkey.executeSql and labkey.selectRows are field captions by default. The getRows function has the same colNameOpt parameter but defaults to field names instead of captions.

References

https://www.labkey.org/Documentation/wiki-page.view?name=savingViews

See Also

makeFilter, labkey.executeSql, labkey.updateRows, labkey.insertRows, labkey.importRows, labkey.deleteRows, labkey.getSchemas, labkey.getQueries, labkey.getQueryViews, labkey.getQueryDetails, labkey.getDefaultViewDetails, labkey.getLookupDetails

Examples

if (FALSE) {

## select from a list named AllTypes
# library(Rlabkey)

rows <- labkey.selectRows(
	baseUrl="http://localhost:8080/labkey",
	folderPath="/apisamples",
	schemaName="lists", 
	queryName="AllTypes")
	
## select from a view on that list
viewrows <- labkey.selectRows(baseUrl="http://localhost:8080/labkey",
    folderPath="/apisamples", schemaName="Lists", queryName="AllTypes",
    viewName="rowbyrow")

## select a subset of columns
colSelect=c("TextFld", "IntFld")
subsetcols <- labkey.selectRows(baseUrl="http://localhost:8080/labkey",
    folderPath="/apisamples", schemaName="lists", queryName="AllTypes",
    colSelect=colSelect)

## including columns from a lookup (foreign key) field
lookupcols <- labkey.selectRows(baseUrl="http://localhost:8080/labkey",
    folderPath="/apisamples", schemaName="lists", queryName="AllTypes",
    colSelect="TextFld,IntFld,IntFld/LookupValue")

}