knitr.Rmd

---
title: Use PRQL with knitr
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{Use PRQL with knitr}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---

Loading `{prqlr}` makes the `prql` code chunks executable in R Markdown and Quarto Markdown files.

## Output formats

It can either output the results of the actual query execution or display a SQL query compiled from a PRQL query.

### Use with `{DBI}` connections

If using with database connections (set the name of `{DBI}` connection to the `connection` chunk option),
PRQL code chunks pass the output SQL to `{knitr}`'s SQL engine and
behaves like [SQL code chunks](https://bookdown.org/yihui/rmarkdown/language-engines.html#sql).
So, usage is the same as for SQL code chunks.

For example, let's render an R Markdown file named `test.Rmd` with the following contents
with the `knitr::knit()` function.

````{r}
#| echo: false
#| results: asis

# Override the default SQL print function to avoid inserting '<div class="knitsql-table">'
# https://github.com/yihui/xaringan/issues/307
knitr::opts_knit$set(sql.print = \(x) paste(knitr::kable(x, format = "markdown"), collapse = "\n"))

src_with_con <- r"(
```{r}
#| echo: false

library(DBI)
library(prqlr)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "mtcars", mtcars)
```

```{prql}
#| connection: con

from mtcars
filter cyl > 6
select {cyl, mpg}
derive {mpg_int = math.round 0 mpg}
take 3
```
)"

cat("````markdown", src_with_con, "````", sep = "")
````

After rendering, a Markdown file named `test.md` is generated with the following contents.

````{r}
#| echo: false
#| results: asis

cat(
  "````markdown",
  knitr::knit_child(text = src_with_con, quiet = TRUE),
  "````",
  sep = ""
)
````

Note that the syntax highlighting of Elm is the best for PRQL,
so the output code blocks are marked as `elm`.
This can be changed by setting the `lang` chunk option.

### Without database connections

PRQL code chunks without the `connection` option will generate SQL code blocks.

````{r}
#| echo: false
#| results: asis

src_without_con <- r"(
```{r}
#| echo: false

library(prqlr)
```

```{prql}
from mtcars
filter cyl > 6
select {cyl, mpg}
derive {mpg_int = math.round 0 mpg}
take 3
```
)"

cat("````markdown", src_without_con, "````", sep = "")
````

The R Markdown file above will be converted to the Markdown file below.

````{r}
#| echo: false
#| results: asis

cat(
  "````markdown",
  knitr::knit_child(text = src_without_con, quiet = TRUE),
  "````",
  sep = ""
)
````

## Engine options

### Compiler options

We can pass some options of `prql_compile()` via the chunk option `engine-opts` (or `engine.opts`).

Note that the `format` option is always `TRUE` for PRQL code chunks.

````{r}
#| echo: false
#| results: asis

src_engine_opts <- r"(
```{r}
#| echo: false

library(prqlr)
```

## YAML-style

```{prql}
#| engine-opts:
#|   target: sql.mssql
#|   signature_comment: false

from mtcars
take 3
```

## R-style

```{prql engine.opts=list(target="sql.mssql", signature_comment=FALSE)}
from mtcars
take 3
```
)"

# Check if it can be rendered
invisible(knitr::knit_child(text = src_engine_opts, quiet = TRUE))

cat("````markdown", src_engine_opts, "````", sep = "")
````

### Parameterized PRQL code blocks

The experimental engine option `use_glue` can be used to insert R variables into PRQL queries.

Characters enclosed in `{{` and `}}` are replaced by the `glue::glue()` function
before being passed to the `prql_compile()` function.

````{r}
#| echo: false
#| results: asis

src_glue <- r"(
```{r}
#| echo: false

library(prqlr)

cyl_min <- 6
derive_or_select <- "derive"
```

```{prql}
#| engine-opts:
#|   use_glue: true

from mtcars
filter cyl > {{cyl_min}}
select {cyl, mpg}
{{derive_or_select}} {mpg_int = math.round 0 mpg}
take 3
```
)"

cat("````markdown", src_glue, "````", sep = "")
````

The R Markdown file above will be converted to the Markdown file below.

````{r}
#| echo: false
#| results: asis

cat(
  "````markdown",
  knitr::knit_child(text = src_glue, quiet = TRUE),
  "````",
  sep = ""
)
````

#### Use query strings in R code blocks

With the experimental `use_glue` option, the query string can even be defined completely in R side.

````{r}
#| echo: false
#| results: asis

src_query_from_r <- r"(
```{r}
#| echo: false

library(prqlr)

prql_query <- "from mtcars
select cyl"
```

```{prql}
#| engine-opts:
#|   use_glue: true

{{prql_query}}
```
)"

cat("````markdown", src_query_from_r, "````", sep = "")
````

This example outputs the following.

````{r}
#| echo: false
#| results: asis

cat(
  "````markdown",
  knitr::knit_child(text = src_query_from_r, quiet = TRUE),
  "````",
  sep = ""
)
````

### Set special info string to output SQL code blocks

When making SQL code blocks output without a DB connection,
the `info_string` option can be used to control the info string of the output code block.
This is convenient when we want to add special attributes that are used in Quarto Markdown files.
The default value is `"sql"`.

For example, the following R Markdown file includes a PRQL code block with the `info_string` option will be converted to...

````{r}
#| echo: false
#| results: asis

src_info_string <- r"(
```{r}
#| echo: false

library(prqlr)
```

```{prql}
#| engine-opts:
#|   info_string: '{.sql filename="SQL"}'
from mtcars
take 3
```
)"

cat("````markdown", src_info_string, "````", sep = "")
````

This Markdown file.

````{r}
#| echo: false
#| results: asis

cat(
  "````markdown",
  knitr::knit_child(text = src_info_string, quiet = TRUE),
  "````",
  sep = ""
)
````