Crosstalk integration: shared data in group not linking correctly

[petrbouchal]

I've come across an odd behaviour when using crosstalk:

When linking two reactables using two SharedData objects with a common ID but different number of rows per ID instance in the two datasets, reactable seems to only return one row for each ID. This is unexpected and differs from how other crosstalk-compatible widgets behave, including DT.

Reprex including comparison with DT:

```{r setup, include=FALSE}
library(reactable)
library(crosstalk)
library(tibble)
library(DT)
```

```{r}
a <- tribble(~id,
             "id1",
             "id2")

b <- tribble(~id, ~value,
             "id1", "1A",
             "id2", "2A",
             "id2", "2B",
             "id1", "1B")

as <- SharedData$new(a, ~id, group = "g")
bs <- SharedData$new(b, ~id, group = "g")
```

# reactable

```{r}
reactable(as, onClick = "select", selection = "multiple")
```

```{r}
reactable(bs, selection = "multiple") # when "id1" is selected in the first reactable, only one row is returned in the second. When both "id1" and "id2" are selected, only two rows are returned.
```

# DT

```{r}
DT::datatable(as)
```

```{r}
DT::datatable(bs)
# here, with the same selection, two rows in the second table are returned when one ID is checked in the first.
```

[glin]

Hi,

According to the Crosstalk docs, keys must be unique for each row:

A key is a unique ID string by which a row can be identified. If you’ve used SQL databases, you can think of these as primary keys, except that their type must be character vector (whereas databases more often use integer values as keys). And indeed, the same criteria that make for good primary keys in SQL databases also make for good Crosstalk keys:

• Unchanging over the life of the data
• Relatively short
• Must never be NA or NULL or ""
• Must be unique within the dataset

https://rstudio.github.io/crosstalk/using.html

Do you know if non-unique keys are intentionally supported in DT? Or what other Crosstalk widgets support this?

[petrbouchal]

Hi Greg,

Thanks for investigating - and sorry for failing to notice this was an accidental feature in {DT}/{crosstalk}!

I suspect the "support" in DT may not be intentional, at least I can't see anything in the history of {DT} issues suggesting otherwise. {plotly} also returns multiple rows if the ID is non-unique, but I checked leaflet and it does not.

FWIW {ggiraph} also uses non-unique IDs for selection, but that is unrelated to {crosstalk}.

I completely understand if you decide not to support this behaviour not foreseen by {crosstalk}. The problem, of course, is that it is quite useful as it allows you to use one table as a more flexible alternative to a select-type input for another table.

[tedmoorman]

@petrbouchal and @glin,

I have to agree that using non-unique IDs for selection is very useful. For example, suppose I have multiple tables which are related by aggregation at different levels. The non-unique ID link would allow end-users to drill down into the data and achieve multiple data views (summary, regular, granular, etc.). Is there any chance that reactable might acquire this feature?

---
title: "links between aggregated and unaggregated data tables"
output:
  html_document:
    df_print: paged
  flexdashboard::flex_dashboard: null
  orientation: rows
  social: menu
  source_code: embed
  theme: cerulean
---
```{r setup, include=FALSE}
# setting up R Markdown options

# We want to hide the code and only see the results
knitr::opts_chunk$set(echo = F)

# We don't want to see any warnings from our code
knitr::opts_chunk$set(warning = F)

# We don't want to see any messages
knitr::opts_chunk$set(message = F)

unlink('table_interact_cache', recursive = TRUE)
```
```{r}
library(crosstalk)
library(tidyverse)
```
```{r Make dataset}
unaggregated <- structure(list(owner = structure(c(1L, 2L, 2L, 2L, 2L), .Label = c("John", "Mark"), class = "factor"), 
hp = c(250, 120, 250, 100, 110), car = structure(c(2L, 2L, 2L, 1L, 1L), .Label = c("benz", "bmw"), class = "factor"), 
id = structure(1:5, .Label = c("car1", "car2", "car3", "car4", "car5"), class = "factor")), 
.Names = c("owner", "hp", "car", "id"), row.names = c(NA, -5L), class = "data.frame")

aggregated <- unaggregated %>% group_by(owner) %>% summarise(avg_hp = mean(hp))
```
```{r Shared data}
unaggregated_sd <- SharedData$new(unaggregated, ~owner, group = "Choose owner")
aggregated_sd <- SharedData$new(aggregated, ~owner, group = "Choose owner")
```
DT-tables
---
### Aggregated Dataframe - DT
```{r}
DT::datatable(aggregated_sd)
```
### Unaggregated Dataframe - DT
```{r}
DT::datatable(unaggregated_sd)
```
reactable-tables
---
### Aggregated Dataframe - reactable
```{r}
reactable::reactable(aggregated_sd, onClick = "select")
```
### Unaggregated Dataframe - reactable
```{r}
reactable::reactable(unaggregated_sd, onClick = "select")
```

[Andryas]

I have for the map all the lat/lon by project and in the reactable I have the aggregation of those project. It would be nice if I could select the developer and all his projects appears on the map.

[samterfa]

I ran into this recently and am currently looking for a workaround. It is super useful to be able to filter on a table and an aggregation of it at the same time in two different tables.