runQuery: Running a Query Against a Database

In retrography/OrientR: R Wrapper for OrientDB's REST API

Description

This function runs a query on the specified database and returns the results as a Data Frame.

Usage

runQuery(db, query, batch = -1, conv.dates = TRUE, date.fmt = "ymd", auto.na = TRUE,
        rm.meta = TRUE, conv.rid = FALSE, unwind = FALSE, formats = c(), ...)

Arguments

db	The connection string obtained from getDB.
query	The SQL query you would like to run.
batch	Batch size limit (similar to SQL LIMIT keyword).
conv.dates	Specifies whether fields marked as date-time have to be automatically casted into POSIXlt and POSIXct objects according to the format specified in date.fmt. Defaults to TRUE.
date.fmt	Specifies the format of the datetime strings. This is a simplified format string as defined in lubridate package and defaults to "ymd".
auto.na	Specifies whether NULLs and empty strings in the resultset have to be converted into NA. Defaults to TRUE.
rm.meta	Specifies whether auto-generated metadata fields (e.g. @class, @rid, @fieldTypes) have to be removed from the resultset. Defaults to TRUE.
conv.rid	When set to TRUE automatically strips the cluster number from the fields marked as RID and casts the remaining ID section into an integer. Defaults to FALSE.
unwind	When set to TRUE automatically unwinds the fields marked as containing lists or collections within the resultset metadata. Defaults to FALSE.
formats	Takes a named vector or a list as parameter. The list must specify the formats of the fields that have to be convreted or casted. The conversion will happen according to the order specified in the list. Each field name can have a vector of formats assigned to it if more than one conversion in desired. Mentioning a field name here will disable the default conversions based on resultset metadata. The accepted values are "rid", "vector", "list", "time", "date", "datetime" and "factor". Numbers and strings are currently imported as Numeric and Character.
...	Additional named parameters will be handed down to jsonlite::fromJSON.

Examples

runQuery(db, "select from concerts", batch = 100)


runQuery(db,
        "select @rid as rid, name, model, year from cars",
        batch = 100,
        conv.rid = TRUE,
        date.fmt = "y")

# Converts the "rid" field from "#{cluster}:{ID}" format to a simple integer ID.
# Casts the "year" field into a POSIX date-time object while considering the field
# content to only include a year (either in "YY" or "YYYY" format).
# Sample results:
#   4533, "Toyota", "Yaris", 2001


query = "select @rid as id, name, model, year, in('manufactures') as manufacturer, in('sells') as vendor from cars"
runQuery(db,
        query,
        batch = 100,
        date.fmt = "y",
        formats = list(id="rid", year="datetime", manfacturer="rid",vendor=c("list","rid"))
        )

# Explicitly specifies the fields that have to be casted or converted.
# The resultset will be unwinded based on "vendor" field and then the unlisted
# vendor RIDs will be converted into integer IDs.
# Sample results:
#   4533, "Toyota", "Yaris", 2001, 32, 2
#   4533, "Toyota", "Yaris", 2001, 32, 12
#   4533, "Toyota", "Yaris", 2001, 32, 7


runQuery(db,
        "select title, dependencies from packages",
        flatten = TRUE,
        formats = c(dependencies.runtime="list", dependencies.devel="list")
        )

# Will pass the value of flatten parameter down to the JSON conversion engine.
# For instance if "dependencies" field contains a collection such as
# {runtime:["xpack", "ypack"], devel:["zpack"]} the resultset will contain the
# following fields: title, dependencies.runtime, dependencies.devel
# Sample results:
#   "thispack", "xpack", "zpack"
#   "thispack", "ypack", "zpack"
# Note that both "dependencies" fields are being unwinded using the "formats" parameter.

retrography/OrientR documentation built on May 27, 2019, 5:54 a.m.