SQL result prints in revealjs format with knitr engine?

[eitsupi]

Description

Sorry I don't have a complete example yet, but when I run the SQL code block using the knitr engine in revealjs format, the display doesn't seem to be what I intended.
Do you have a sample slide that uses SQL?

```{r}
#| include: false
#| cashe: false
con_sample <- DBI::dbConnect(duckdb::duckdb())
```

```{sql}
#| connection: con_sample
SELECT *
FROM read_csv_auto('starwars.csv', header=true)
LIMIT 5
```

[cderv]

What is the intended display ?

Here is an example

---
format: revealjs
---

```{r}
#| include: false
#| cache: false
con_sample <- dbplyr::nycflights13_sqlite(path = NULL) 
```

# Table

```{sql}
#| connection: con_sample
SELECT *
FROM airlines
LIMIT 5
```

[mcanouil]

It depends, by default kable outputs markdown, so the font size is the one from the slide.
You can try the following, and you sill see the same display.

## Slide 3

|  mpg| cyl| disp|  hp| drat|    wt|  qsec| vs| am| gear| carb|
|----:|---:|----:|---:|----:|-----:|-----:|--:|--:|----:|----:|
| 21.0|   6|  160| 110| 3.90| 2.620| 16.46|  0|  1|    4|    4|
| 21.0|   6|  160| 110| 3.90| 2.875| 17.02|  0|  1|    4|    4|
| 22.8|   4|  108|  93| 3.85| 2.320| 18.61|  1|  1|    4|    1|
| 21.4|   6|  258| 110| 3.08| 3.215| 19.44|  1|  0|    3|    1|
| 18.7|   8|  360| 175| 3.15| 3.440| 17.02|  0|  0|    3|    2|

To change it only for the table, you need to make it unique, either by adding a class or by nested it in a div, but this will also affect the source code.
I would have use class-output, but knitr seems to ignore it for sql ...

::: {.knitsql-table}

```{sql}
#| connection: con
SELECT *
FROM mtcars
LIMIT 5
```

:::

Regarding kable(), see https://bookdown.org/yihui/rmarkdown-cookbook/kable.html.
There is also kableExtra for more customisation, see https://haozhu233.github.io/kableExtra/.

[cderv]

Sorry it was no clear in my answer above: #5869 (reply in thread).
or maybe I missed something. I did not understood the issue was just changing CSS rule.

The default behavior is to add a div of class knitsql-table already. This is used to easily style content within the div.
If the solution is setting some CSS rules, then it could be used to that.

@eitsupi in your example you are explicitly trying to remove it. and @mcanouil I don't quite understand why you also overwrite sql.print to read the class on a kable.

I see some question about Markdown Table. If the aim is to output HTML for kable(), the option can be set.

options(knitr.table.format = "html")

but anyway the problem here is that the output from the SQL code block has a constant font size and does not look good when the body text font size is changed

I tried to find where the fixed font size was, but I did not found it. Did you identified the rule responsible ? I am probably still missing something, as changing the font size globally is also changing the one from the table.

I am still a bit lost on what should be improve to help your use case. Sorry.

[mcanouil]

@mcanouil I don't quite understand why you also overwrite sql.print to read the class on a kable.

There are two different examples based on provided code, either by tweaking the CSS without changing the default print method, or by doing both (note that I choose the same name class as the one from knitr but it would have been better to use another one).
knitr does not add class to markdown table for some reason or to output as it should when using. class-output.

Indeed options(knitr.table.format = "html") can be used to change the behaviour of all calls to kable but this is not necessarily wanted here.

I am still a bit lost on what should be improve to help your use case. Sorry.

I believe the issue was that kable markdown output was using the default font size from the slide leading to a very big table, while other outputs have their own font size.
Correct me if I am wrong @eitsupi.

[cderv]

knitr does not add class to markdown table for some reason or to output as it should when using. class-output.

sql engine has a specific way of outputing content - it was designed like that and would need a rewrite to change.

Indeed options(knitr.table.format = "html") can be used to change the behaviour of all calls to kable but this is not necessarily wanted here

You can apply the option to one chunk only if needed by using the R.options chunk option.

I believe the issue was that kable markdown output was using the default font size from the slide leading to a very big table, while other outputs have their own font size.

So this would be a more generic issue not realted to knitr and sql engine then ? This is about table output using the same font size as text on slides ?

Which now I understand relates to : I don't want tables as output for my sql engine. But as I said this is a knitr issue. Only way to overwrite it for now is to set sql.print to something else. Another thing that would require a eng_sql redesign.

Easy solution is to use two chunks:

1. Output the SQL result to a variable,
2. Print the variable

---
title: test
subtitle: test
format: revealjs
execute:
  echo: true
---

```{r}
#| include: false
con <- DBI::dbConnect(duckdb::duckdb())
duckdb::duckdb_register(con, "mtcars", mtcars)
```

## Slide 2

```{sql}
#| connection: con
#| output.var: tab
SELECT *
FROM mtcars
LIMIT 5
```

```{r}
#| echo: false
tab
```

Otherwise, (weird) example on how to hack knitr sql output to get a code like output

---
title: test
subtitle: test
format: revealjs
execute:
  echo: true
---

```{r}
#| include: false
con <- DBI::dbConnect(duckdb::duckdb())
duckdb::duckdb_register(con, "mtcars", mtcars)
```

## Slide 2

```{r}
#| echo: false
knitr::opts_knit$set(
  sql.print = function(x) {
    paste(c(
      "```",
      capture.output(print(x)),
      "```"),
      collapse = "\n"
    )
  }
)
```

```{sql}
#| connection: con
SELECT *
FROM mtcars
LIMIT 5
```

```{r}
#| echo: false
DBI::dbDisconnect(con)
```

Hope this times, it helps

[mcanouil]

The thread mentioned in #5869 (reply in thread) also make use of the output.var trick (and it was marked as the answer). (It's weird i never used that ...)

• How to make table rendering consistent across languages? #3253 (comment)