Add explicit preload/join feature to ORM

[juan-db]

Describe the feature

Allow the user to explicitly load fkey rows, instead of the ORM doing it automatically.

In my opinion, child relationships should only be loaded manually, never automatically, as there could be millions of connections.

Use Case

If we have a database structure like Grandparent -> Parent -> Child, when we select from Grandparent, it seems to automatically preload Parents associated with it.

sql db {
	select from Grandparent
}!
// Grandparent{name:'Anna', parents:[
//     Parent{name:'Claude', children:[]},
//     Parent{name:'Gloria', children:[]}
// }]

From what I can tell, it does try to load the children as well, but there is a logic error somewhere as mentioned in #21598.
That PR fixes the compile-time panic and causes the program to behave predictably, but I'm not sure if it's behaving correctly.

Proposed Solution

The best solution I can think of is introducing a load / preload feature to the ORM, which would optionally accept a condition similar to SQL JOIN.

sql db {
	select from Grandparent
	preload Grandparent.parents p on p.name == 'Claude'
	preload c.children
}
// Grandparent{name:'Anna', parents:[
//     Parent{name:'Claude', children:[
//         Child{name:'Ralph'},
//         Child{name:'Michael'}
//     ]}
//     /* NOTE Gloria from above isn't loaded */
// }]

I don't particularly like this exact solution but I can't think of one at the moment that wouldn't stray too far from SQL behavior while comfortably fitting V.

Other Information

No response

Acknowledgements

• I may be able to implement this feature request
• This feature might incur a breaking change

Version used

V 0.4.6 6d2c3a9

Environment details (OS name and version, etc.)

V full version: V 0.4.6 ed7f1ff.6d2c3a9
OS: macos, macOS, 14.5, 23F79
Processor: 11 cpus, 64bit, little endian, Apple M3 Pro

vexe mtime: 2024-06-03 06:55:49

VTMP: OK, value: /tmp/v_501

Git version: git version 2.39.3 (Apple Git-146)
Git vroot status: weekly.2024.22-32-g6d2c3a9c
.git/config present: true

CC version: Apple clang version 15.0.0 (clang-1500.3.9.4)
thirdparty/tcc status: thirdparty-macos-arm64 5c1d002f

Note

You can use the 👍 reaction to increase the issue's priority for developers.

Please note that only the 👍 reaction to the issue itself counts as a vote.
Other reactions and those to comments will not be taken into account.