[stephen]

Definitely appreciate your "sometimes ORMs/sometime raw SQL" pragmatic stance. I agree!

> then if you join on both you now are loading A * B * C rows

I'm realizing it's not actually written up in our proper docs, but the "cross join lateral" trick is pretty neat:

https://joist-orm.io/blog/nextjs-sample-app#join-based-prelo...

This will return A number of (author) rows, where each author row as a `_b` column that is a JSON array of that author's books. And if you asked for C comment rows, then one of the "columns"/indices in each of those book arrays will itself be a nested array of the book's comments.

So all the data gets returned, but with no repetition/Cartesian product.

Hat tip to PostGraphile where I first saw the "cross join later" + "json_agg" technique used. Disclaimer we're not actually running this Joist feature in production yet--just haven't had time to run it through the test suite.

[superice]

I wasn't aware of Joist until I read a comment of yours somewhere else in this comment section, interesting stuff! I think Joist is the closest thing I've seen to an actual fix to this problem. The way you use the JS event loop as your 'scope' is pretty clever, and satisfies most use-cases I think.

It aligns pretty closely to what we do internally. We don't use classes or annotations to do entity definitions, instead we'll have something like this:

    const User = {
        fields: {
            id: defineIdField(),
            email: defineField('string', false), // false = NOT NULL
        },
        manyToOne: {
            role: { entity: 'Role', nullable: false },
        },
    };

Then the way to query and save is something like this:

    datasource.inTransaction(async (orm) => {
        const users = await orm.findMany('User', { where: sql`...`, orderBy: sql`email asc` });
    
        // Await in this loop might look bad, but only blocks for the first user
        for (const user of users) {
            console.log(user.email, (await user.role).name);
        }

        const adminRole = await orm.findOne('Role', 1); // by ID, throws if not found
        return orm.saveMany('User', users.map(u => ({ ...u, role: adminrole }));
    });

Which looks different from what Joist is doing, but the fetching mechanics under the hood are very similar I think. Our 'scope' is just somewhat more explicit, since we don't use the event loop mechanics of JS but rather use the inTransaction callback as the boundary.

(The reason we haven't open sourced this yet is that I'd like to iron out some of the ugly parts first, like the string entity identifiers, and the forced usage of an id column, and the lack of type safe where clauses right now)