Subquery with inner query referring to a different table than the outer query

How does one write a subquery with dplyr syntax where the inner query refers to a different table than the outer query?

Consider this example

library(DBI)
library(dplyr)

con <- dbConnect(RSQLite::SQLite(), ":memory:")

table1 <- data.frame(
  id = c(1,2,3),
  date = c("2019-01-04", "2019-01-04", "2019-01-05")
)

table2 <- data.frame(
  id = c(1,1,2,3),
  date = c("AAA", "BBB", "CCC", "DDD")
)

dbWriteTable(con, "table1", table1, overwrite = T)
dbWriteTable(con, "table2", table2, overwrite = T)

dbGetQuery(con, "
SELECT * 
FROM table2
WHERE id in (
  SELECT id
  FROM table1
  WHERE date='2019-01-04'
)
")

One idea is to write the inner query first and then use the result in filter(). But this will not work with my real example because there are hundreds of thousands of matching ids and the database (MS SQL Server) throws an error when executing the second query.

ids <- con %>%
  tbl("table1") %>%
  filter(date == "2019-01-04") %>%
  pull(id)

con %>%
  tbl("table2") %>%
  filter(
    id %in% ids
  ) %>%
  show_query()

<SQL>
SELECT *
FROM `table2`
WHERE (`id` IN (1.0, 2.0)) # This is going to be a problem with a large number of matching ids

As far as I understand (see this question), the way to write the subquery and execute it in the database is this

result <- con %>%
  tbl("table2") %>%
  filter(
    id %in% (
      con %>%
      tbl("table1") %>%
        filter(date == "2019-01-04") %>%
        pull(id)
    )
  )

This runs without error, but using collect() or show_query() on the result throws this error:

Error in `purrr::map_chr()`:
ℹ In index: 2.
Caused by error in `UseMethod()`:
! no applicable method for 'escape' applied to an object of class "c('SQLiteConnection', 'DBIConnection', 'DBIObject')"
Run `rlang::last_trace()` to see where the error occurred.

I think you can just use the tbl(.) references within an inner join.

inner_join(tbl(con, "table1"), tbl(con, "table2"), by = "id") %>%
  filter(date.x == "2019-01-04") %>%
  select(id, date = date.y) %>%
  collect()
# # A tibble: 3 × 2
#      id date 
#   <dbl> <chr>
# 1     1 AAA  
# 2     1 BBB  
# 3     2 CCC  

It's still a "lazy" operation, as seen here:

inner_join(tbl(con, "table1"), tbl(con, "table2"), by = "id") %>%
  filter(date.x == "2019-01-04") %>%
  select(id, date = date.y) %>%
  show_query()
# <SQL>
# SELECT `id`, `date.y` AS `date`
# FROM (
#   SELECT `LHS`.`id` AS `id`, `LHS`.`date` AS `date.x`, `RHS`.`date` AS `date.y`
#   FROM `table1` AS `LHS`
#   INNER JOIN `table2` AS `RHS`
#     ON (`LHS`.`id` = `RHS`.`id`)
# )
# WHERE (`date.x` = '2019-01-04')