dm_filter on remote PostgreSQL db errors

[LDalby]

We have a PostgreSQL db and I would like to create local DuckDB copy of a subset of the rows on it.

My plan was to

1. create the datamodel using dm_from_con
2. filter the rows using dm_filter
3. copy to local database using copy_dm_to

I currently get this error when attempting to filter:

library(dm)
#> 
#> Attaching package: 'dm'
#> The following object is masked from 'package:stats':
#> 
#>     filter

con <- camalienr::ca_connect() # internal pkg - only used here to connect to the db
remote_pg <- dm_from_con(con, schema = "data", learn_keys = TRUE)
dm_nrow(remote_pg)
#>                 chunk             detection      detectionsummary 
#>                 33781              83777960               9372360 
#>                 image             imagemeta                   job 
#>               5557936               2890422                   124 
#>               partner          plantnetcall plantnetcall_metadata 
#>                    11                159568                157834 
#> plantnetrequestparams               species     species_whitelist 
#>                     2                 49479                  2419

# In the detectionssummary table we have a column named speciesid
# integer ID on different species.
dm_filter(.dm = remote_pg,
          detectionsummary = (speciesid == 1356471))
#> Warning in igraph::graph_from_data_frame(., directed = directed, vertices =
#> def$table): In `d' `NA' elements were replaced with string "NA"
#> Error in `map()`:
#> ℹ In index: 1.
#> Caused by error in `igraph::graph_from_data_frame()`:
#> ! Some vertex names in edge list are not listed in vertex data frame

^{Created on 2024-09-06 with reprex v2.1.1}

Session info

sessioninfo::session_info()
#> ─ Session info ───────────────────────────────────────────────────────────────
#>  setting  value
#>  version  R version 4.4.1 (2024-06-14)
#>  os       macOS Sonoma 14.6.1
#>  system   aarch64, darwin20
#>  ui       X11
#>  language (EN)
#>  collate  en_US.UTF-8
#>  ctype    en_US.UTF-8
#>  tz       Europe/Copenhagen
#>  date     2024-09-06
#>  pandoc   3.1.11 @ /Applications/RStudio.app/Contents/Resources/app/quarto/bin/tools/aarch64/ (via rmarkdown)
#> 
#> ─ Packages ───────────────────────────────────────────────────────────────────
#>  package     * version     date (UTC) lib source
#>  backports     1.5.0       2024-05-23 [1] CRAN (R 4.4.0)
#>  bit           4.0.5       2022-11-15 [1] CRAN (R 4.4.0)
#>  bit64         4.0.5       2020-08-30 [1] CRAN (R 4.4.0)
#>  blob          1.2.4       2023-03-17 [1] CRAN (R 4.4.0)
#>  cachem        1.1.0       2024-05-16 [1] CRAN (R 4.4.0)
#>  camalienr     0.3.7       2024-07-03 [1] git (https://gitlab.au.dk/ECOS/biodiversa/camalien/camalienr.git@67fbee4)
#>  cli           3.6.3       2024-06-21 [1] CRAN (R 4.4.0)
#>  DBI           1.2.3       2024-06-02 [1] CRAN (R 4.4.0)
#>  dbplyr        2.5.0       2024-03-19 [1] CRAN (R 4.4.0)
#>  digest        0.6.37      2024-08-19 [1] CRAN (R 4.4.1)
#>  dm          * 1.0.10.9010 2024-08-30 [1] https://cynkra.r-universe.dev (R 4.4.1)
#>  dplyr         1.1.4       2023-11-17 [1] CRAN (R 4.4.0)
#>  evaluate      0.24.0      2024-06-10 [1] CRAN (R 4.4.0)
#>  fansi         1.0.6       2023-12-08 [1] CRAN (R 4.4.0)
#>  fastmap       1.2.0       2024-05-15 [1] CRAN (R 4.4.0)
#>  fs            1.6.4       2024-04-25 [1] CRAN (R 4.4.0)
#>  generics      0.1.3       2022-07-05 [1] CRAN (R 4.4.0)
#>  glue          1.7.0       2024-01-09 [1] CRAN (R 4.4.0)
#>  hms           1.1.3       2023-03-21 [1] CRAN (R 4.4.0)
#>  htmltools     0.5.8.1     2024-04-04 [1] CRAN (R 4.4.0)
#>  httpuv        1.6.15      2024-03-26 [1] CRAN (R 4.4.0)
#>  igraph        2.0.3       2024-03-13 [1] CRAN (R 4.4.0)
#>  knitr         1.48        2024-07-07 [1] CRAN (R 4.4.0)
#>  later         1.3.2       2023-12-06 [1] CRAN (R 4.4.0)
#>  lifecycle     1.0.4       2023-11-07 [1] CRAN (R 4.4.0)
#>  lubridate     1.9.3       2023-09-27 [1] CRAN (R 4.4.0)
#>  magrittr      2.0.3       2022-03-30 [1] CRAN (R 4.4.0)
#>  memoise       2.0.1       2021-11-26 [1] CRAN (R 4.4.0)
#>  mime          0.12        2021-09-28 [1] CRAN (R 4.4.0)
#>  pillar        1.9.0       2023-03-22 [1] CRAN (R 4.4.0)
#>  pkgconfig     2.0.3       2019-09-22 [1] CRAN (R 4.4.0)
#>  promises      1.3.0       2024-04-05 [1] CRAN (R 4.4.0)
#>  purrr         1.0.2       2023-08-10 [1] CRAN (R 4.4.0)
#>  R6            2.5.1       2021-08-19 [1] CRAN (R 4.4.0)
#>  Rcpp          1.0.13      2024-07-17 [1] CRAN (R 4.4.0)
#>  reprex        2.1.1       2024-07-06 [1] CRAN (R 4.4.0)
#>  rlang         1.1.4       2024-06-04 [1] CRAN (R 4.4.0)
#>  rmarkdown     2.28        2024-08-17 [1] CRAN (R 4.4.0)
#>  RPostgres     1.4.7       2024-05-27 [1] CRAN (R 4.4.0)
#>  rstudioapi    0.16.0      2024-03-24 [1] CRAN (R 4.4.0)
#>  sessioninfo   1.2.2       2021-12-06 [1] CRAN (R 4.4.0)
#>  shiny         1.9.1       2024-08-01 [1] CRAN (R 4.4.0)
#>  tibble        3.2.1       2023-03-20 [1] CRAN (R 4.4.0)
#>  tidyr         1.3.1       2024-01-24 [1] CRAN (R 4.4.0)
#>  tidyselect    1.2.1       2024-03-11 [1] CRAN (R 4.4.0)
#>  timechange    0.3.0       2024-01-18 [1] CRAN (R 4.4.0)
#>  utf8          1.2.4       2023-10-22 [1] CRAN (R 4.4.0)
#>  vctrs         0.6.5       2023-12-01 [1] CRAN (R 4.4.0)
#>  withr         3.0.1       2024-07-31 [1] CRAN (R 4.4.0)
#>  xfun          0.47        2024-08-17 [1] CRAN (R 4.4.0)
#>  xtable        1.8-4       2019-04-21 [1] CRAN (R 4.4.0)
#>  yaml          2.3.10      2024-07-26 [1] CRAN (R 4.4.0)
#> 
#>  [1] /Library/Frameworks/R.framework/Versions/4.4-arm64/Resources/library
#> 
#> ──────────────────────────────────────────────────────────────────────────────

I'm a bit confused about the error - why is {iGraph} called here?