Very nice. I also want implicit joins with alternate keys:
left.annotate_rows(x = other[left.y].z)
Let’s do it (although maybe not in this initial prototype). It seems like an Expression can carry a list of things to join (and where to join them, a UUID or something).
Things get interesting now that we have multiple keys to throw around! NB: we assume that we’ve implemented methods to join globals and left join on entries
There’s a somewhat subtler point about the indices of an expression derived from joins. Generally, the index is going to be the union of indices of expressions used. With this model, the following work:
vds2[:, :] has no indices vds2[vds.v, :] has index {'row'} vds2[:, vds.s] has index {'col'} vds2[vds.v, vds.s] has indices {'row', 'col'}
But what if vds2 has row keys Variant, String and some other column key Int32?
vds2[(vds.v, vds.s), :] should expose the row annotations of vds2 as a per-entry value in vds. This is going to be tricky to get right.
Is number 5 intended to be a left join (on only one of the keys of table2)? If so, I agree with the requirement to re-key and join again (assuming it’s as fast).
Re: your subtler point about multiple row keys, I assume this will nearly never happen with a straight vds, but will probably happen plenty with matrices?
So this works great for expressing a left join: kt2[kt1.field1]. But how do we replicate the product functionality? I would want to treat that as an array sometimes, and I have absolutely no idea what that syntax should look like.
I imagine it will be a separate argument to annotate - to clarify, if product=False, would it simply select a random entry to annotate (last seen, I assume?). If so, then I might advocate for changing it to pick or something like that (and have the default be the Array[...]).
I don’t want any other argument to annotate, because it takes arbitrary **kwargs. What if you wanted to defined a new field product which is the table.x * table.y?
I think the right place for this option is on the join syntax, but I’m not sure what it would look like.