Join scanning

[hiendaovinh]

Hi,
I was trying to preload ToMany relationship with custom Left Join. Could you guide me to any docs of join scanning of results so I can load the relationship to R?
Thank you.

[hiendaovinh]

It would be better if we can preload ToMany relationships and scan the results back to R with group by from PK.

[stephenafamo]

Preloading a ToMany is kind of difficult. By default, using a left join that has more than one match will duplicate the row for each match. There are several ways to make this work.

Option 1: Caching

Have a cache of all scanned objects by primary key and reuse them if encountered again. I think this is very error-prone. It also requires the scanner to keep track of every row which breaks features like Cursor. Also, significant changes will have to be made to the scan package and I think this may be fighting against SQL which is not what Bob is about.

Additionally, the complexity of this method grows with each nested load. It would seem that a flattened cache of each object and their relationship will have to be maintained to be able to handle nested loaders well.

Option 2: Use array_agg

I had a prototype of this at some point. The main blockers are:

1. MySQL and SQLite have no concept of arrays so this can only work for Postgres.
   We can use JSON aggregate functions for MySQL and SQLite, however, this will break custom types that rely on Scan and Value because it will instead need to serialize/deserialize from JSON instead of DB values.
2. No package provides nested array types for Postgres. lib/pq has a GenericArray type, but it does not handle nested arrays yet. So even with this method, it will not be possible to handle nested ToMany preloads.

---

Summary: I tried a few things, but I did not quite find a way to make this work reliably. If there's a way, I'll be happy to try it out

[hiendaovinh]

After consideration and experiments, I came to the same conclusion. Thanks for your detailed explaination.