Inline *_join() + mutate(), filter() and distinct() #722
Description
*_join() always produces a subquery if followed by other verbs:
library(dplyr, warn.conflicts = FALSE)
library(dbplyr, warn.conflicts = FALSE)
lf <- lazy_frame(x = 1, a = 1)
lf2 <- lazy_frame(x = 1, b = 2)
lf3 <- lazy_frame(x = 1, c = 3)
lf2 <- lazy_frame(x = 1, z = 3)
left_join(lf, lf2, by = "x") %>%
mutate(y = y + 1, z = z + 1)
#> <SQL>
#> SELECT `x`, `y` + 1.0 AS `y`, `z` + 1.0 AS `z`
#> FROM (SELECT `LHS`.`x` AS `x`, `y`, `z`
#> FROM `df` AS `LHS`
#> LEFT JOIN `df` AS `RHS`
#> ON (`LHS`.`x` = `RHS`.`x`)
#> ) `q01`
# could be
# SELECT `LHS`.`x` AS `x`, `y` + 1.0 AS `y`, `z` + 1.0 AS `z`
# FROM `df` AS `LHS`
# LEFT JOIN `df` AS `RHS`
# ON (`LHS`.`x` = `RHS`.`x`)Depending on the verb this should be quite easy or not. E.g. in sql_build() we have to translate the symbol x to LHS`.`x and this has to be done everywhere. For example in sql_build.op_filter() we need to pass this alias to translate_sql_()
where_sql <- translate_sql_(op$dots, con, context = list(clause = "WHERE"))