---
title: "Searching ECOTOX"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{Searching ECOTOX}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---

```{r, include = FALSE}
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>"
)
```

```{r setup, warning=FALSE, message=FALSE}
library(ECOTOXr)
```

## Searching the local database

Obviously, searching the local database is only possible after the download and build is
ready (see `?download_ecotox_data` or `?build_ecotox_sqlite`). Once built, it can
be queried with `?search_ecotox`.

> Search the local database for tests of water flea Daphnia magna exposed to benzene

```{r, eval = FALSE}
search_ecotox(
  list(
    latin_name    = list(terms = "Daphnia magna", method = "exact"),
    chemical_name = list(terms = "benzene",       method = "exact")
  )
)
```

### Three ways of querying the local database

Let's have a look at 3 different approaches for retrieving a specific record from the local database, using the
unique identifier `result_id`. The first option is to use the build in `search_ecotox` function. It uses
simple `R` syntax and allows you to search and collect any field from any table in the database. Furthermore,
all requested output fields are automatically joined to the result without the end-user needing to know much
about the database structure.

> Using the prefab function `search_ecotox` packaged by `ECOTOXr`

```{r, eval = FALSE, warning = FALSE}
search_ecotox(
  list(
    result_id = list(terms = "401386", method = "exact")
  ),
  as_data_frame = F
)
```

The approach shown above is provided for the user's convenience. However, it is a relatively
blunt instrument. It's a hammer, which is fine when
you have nails, but less effective when you have screws. In most cases it is more efficient
to create a custom query for you specific needs. In those cases you can use two different
strategies, involving either `tidyverse` verbs or the simple query language (SQL).

If you like to use [`dplyr`](https://dplyr.tidyverse.org/) verbs, you are in luck. SQLite
database can be approached using `dplyr` verbs. This approach will only return information
from the `results` table. The end-user will have to join other information (like test
species and test substance) manually. This does require knowledge of the database structure
(see `vignette("ecotox-schema")`).

> Using `dplyr` verbs

```{r, eval = FALSE, warning = FALSE}
con <- dbConnectEcotox()
dplyr::tbl(con, "results") |>
  dplyr::filter(result_id == "401386") |>
  dplyr::collect()
```

If you prefer working using `SQL` directly, that is fine too. The [`RSQLite`](https://cran.r-project.org/package=RSQLite) package
allows you to get queries using `SQL` statements. The result is identical to that of the previous approach. Here too the end-user
needs knowledge of the database structure in order to join additional data.
For more details see `vignette("ecotox-schema")`, it shows the database structure and provides
clues on how to construct custom queries with `dplyr`.

> Using `SQL` syntax

```{r, eval = FALSE, warning = FALSE}
dbGetQuery(con, "SELECT * FROM results WHERE result_id='401386'") |>
  dplyr::as_tibble()
```

All three approaches shown above generate identical results. Although, the first has
additional information joined automatically.

## Searching the online database

You can also use the package to
[search using the online webform](https://cfpub.epa.gov/ecotox/search.cfm):

```{r}
search_fields <-
  list_ecotox_web_fields(
    txAdvancedSpecEntries     = "daphnia magna",
    RBSPECSEARCHTYPE          = "EXACT",
    txAdvancedChemicalEntries = "benzene",
    RBCHEMSEARCHTYPE          = "EXACT")

search_results <- websearch_ecotox(search_fields, verify_ssl = FALSE)

search_results$`Aquatic-Export`
```

When aiming for reproducibility it is better to query the local database, as the online
tool may not be able to search and export all fields; data may change and is beyond your control;
and it is processed on the server which forms a black box. See also `vignette("reproducibility")`.