Mutating from different tables about dm

[krlmlr]

Thanks for your input.

#89 is very high on the list of priorities, we've just finished some internal refactoring that allows us to think about updating. Your code would translate to something like:

cdm_nycflights13() %>%
  zoom_to_tbl(flights) %>%
  left_join(planes, select = c(engines)) %>%
  mutate(fuel = distance * engines * !!fuel_per_mile_per_engine) %>%
  select(-engines) %>%
  update_tbl()

Or perhaps slicker:

cdm_nycflights13() %>%
  zoom_to_tbl(flights) %>%
  left_join(planes) %>%
  mutate(fuel = distance * planes$engines * !!fuel_per_mile_per_engine) %>%
  update_tbl()

Let's focus on mutating from different tables in this issue. It seems we need to do #89 first while keeping in mind your use case. Happy to take your feedback regarding the API for "simple" updates in #89.

[krlmlr]

@courtiol: We now have joins for tables in the dm, you can use left_join() and friends on a zoomed dm. I have already used it with success. The documentation is nowhere yet but you can look at dm:::left_join.zoomed_dm to see the implementation and the extra parameters.

Our example looks like this:

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

fuel_per_mile_per_engine <- 2.5 # in gallons

zoomed <-
  cdm_nycflights13() %>%
  cdm_zoom_to_tbl(flights) %>%
  left_join(planes, select = c(tailnum, engines)) %>%
  mutate(fuel = distance * engines * !!fuel_per_mile_per_engine) %>%
  select(-engines)

zoomed %>%
  select(month, day, distance, fuel)
#> # A zoomed table of a dm: flights
#> # Filters for zoomed:     FALSE
#> # A tibble:               336,776 x 4
#>    month   day distance  fuel
#>    <int> <int>    <dbl> <dbl>
#>  1     1     1     1400  7000
#>  2     1     1     1416  7080
#>  3     1     1     1089  5445
#>  4     1     1     1576  7880
#>  5     1     1      762  3810
#>  6     1     1      719  3595
#>  7     1     1     1065  5325
#>  8     1     1      229  1145
#>  9     1     1      944  4720
#> 10     1     1      733    NA
#> # … with 336,766 more rows

# The original has 53 columns:
zoomed %>%
  cdm_update_zoomed_tbl()
#> ── Table source ──────────────────────────────────────────────────────────────────────
#> src:  <environment: R_GlobalEnv>
#> ── Data model ────────────────────────────────────────────────────────────────────────
#> Tables: `airlines`, `airports`, `flights`, `planes`, `weather`
#> Columns: 54
#> Primary keys: 3
#> Foreign keys: 3
#> ── Filters ───────────────────────────────────────────────────────────────────────────
#> None

zoomed %>%
  cdm_insert_zoomed_tbl() %>%
  cdm_draw()
#> New names:
#> * flights -> flights...3
#> * flights -> flights...6

^{Created on 2019-11-14 by the reprex package (v0.3.0)}

Do you have further suggestions at this stage? (I'm aware you're not particularly fond of "zoom" but we can discuss this separately perhaps.)

[courtiol]

This is great that it works, but I would prefer a version without left_join.
Something like:

zoomed <-
  cdm_nycflights13() %>%
    cdm_zoom_to_tbl(flights) %>%
    mutate(fuel = distance * planes$engines * !!fuel_per_mile_per_engine) 

As I understood it, the benefit of {dm} was precisely that is allows for not having to merge tables, so I would love if it was possible to stick to this core idea.

[krlmlr]

Thanks for your feedback. I'm not even sure how to approach implementation this syntax. We'll probably need to hijack the $ operator and do some advanced magic so that it works on databases and local data frames.

In this syntax it seems a left join is the only thing that makes sense -- in addition, as you mentioned, we need to ensure that the join doesn't duplicate rows.

Let's keep this issue open for now.

[courtiol]

About the $: this is exactly why I had suggested to introduce a new operator in my first post above (engines %from% planes), but perhaps you are right and overridding the $ is more natural.

[krlmlr]

I think this now can be implemented as a macro:

1. analyze the expression
2. rename in related table (use column names like ...1, ...2 etc. to avoid collision)
3. zoom into target table
4. join related table
5. perform the mutate
6. remove the helper columns
7. update-zoom in target table

Would you like to contribute?

[courtiol]

Thanks @krlmlr,
I would love to say yes and try, but I promised myself I would not take on new things for a while so as to catch up with my ongoing work.
So if anyone else feel like helping, please feel free!!
If no one jumps on it and if there is no hurry I may revisit this later.