Joins with j computations over graph edgelists, in the style of by=.EACHI but with alternate grouping for j

Start by running the R examples in the issue and reading the data.table documentation for joins and by=.EACHI. Define the feature's behavior for grouping by an RHS field not used in the join, including the graph-edgelist examples; done means an efficient grouped join produces the requested target-level aggregates without the large intermediate allocation.

I'm going to describe this general problem as a "graph edgelist join" but motivate with a real example to make it less abstract. The setup: we trained a random forest. We can measure the training error for each node in our forest. For new observations ("targets") sharing those nodes, we want to know the average of the training errors that were experienced in the target's component nodes.

library(data.table)

# lhs is our training set: we have some node IDs and error values
set.seed(1234)
lhs <- data.table(node_ID = rep(LETTERS[1:5], 5),
                  train_err = rnorm(125, 0, 4))
setkey(lhs, node_ID)

# rhs is our test set - we have an ID for the target and the nodes each target was a member of
rhs <- data.table(target_ID = rep(c("t1", "t2", "t3"), 4),
                  node_ID = sample(LETTERS[1:5], 12, replace = TRUE))
setkey(rhs, node_ID, target_ID)

# This is what I'd like to do in one step, and in the style of by=.EACHI to avoid a large allocation 
# by joining/grouping/then executing j. However it doesn't work because target_ID is not in the 
# parent frame (DT message: Error in eval(bysub, xss, parent.frame()) : object 'target_ID' not found)
lhs[rhs, .(mean_err = mean(train_err), target_ID = target_ID), by="target_ID", allow.cartesian = TRUE]

# I can use a combination of by=.EACHI and a second j-operation to reach my goal of a mean
# error over the target_ID group, without *quite* such a large allocation, but it requires some
# careful bookkeeping in real-world cases. It also doesn't fully benefit from the by=.EACHI 
# optimization; in practice I'm still blowing up large-memory machines this way.
lhs[rhs, .(mean_err = mean(train_err), 
           target_ID = target_ID, 
           node_n = length(train_err)), 
    by=.EACHI][
      ,
      .(mean_err = weighted.mean(mean_err, node_n)),
      by = "target_ID"
    ]

I believe the general structure of this problem is 'perform a join over a graph edgelist (shared links between LHS/RHS) and run a computation over the linked entities by a grouping term not in the join.' I've encountered this problem (so far) in a handful of totally separate domains:

• The example above with a graph connecting train/test sets over shared model nodes, along with other random forest examples like clustering on reductions of the adjacency matrix;
• A clustering problem where observations were ordered into some categories (eg products returned by a specific search term) and we wanted to cluster the categories based on their similarity in returned product vectors;
• A spatial problem, where we had origin/destination pairs and a drivetime between them, and want to sum the reachable population counts in all of the destinations reachable from each distinct origin.

I keep returning to data.table's by=.EACHI feature as the correct model for an efficient solution -- perform the join and computation by group repeatedly, instead of making a huge allocation first -- but the important wrinkle for the edgelist problem is that we want to group j by a field in the key columns of RHS, but which we're not actually joining the LHS/RHS on.

I'm ~90% expecting that data.table supports this and I haven't found the right search terms. If not, I hope this sounds like an interesting feature. Thanks for considering either way!