Hacker News Reader

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.

View on HN

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

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 })); });