3 ms·
Zapatos can generate the hairy JSON stuff for you, including lateral joins that are equivalent to your example query, which would be: const result = await
by gmac 6y ago
Zapatos can generate the hairy JSON stuff for you, including lateral joins that are equivalent to your example query, which would be:
const result = await db.select(‘user’, db.all, { lateral: { posts: db.select(‘post’, { userId: db.parent(‘id’) }) } }).run(pool);
And result will have the structure you asked for, and be automatically typed as such.
(Sorry, can’t manage helpful indentation from my phone).
See: https://jawj.github.io/zapatos/index.html#joins-as-nested-json https://jawj.github.io/zapatos/index.html#joins-as-nested-js...
- renke1 6y agoThanks, Zapatos looks really nice. That library seems to be basically what I want (aside from writing SQL for joins, but I think it's just not possible in the way I imagined). The documentation is really nice. Much of what is written in it aligns with my thinking. I'll definitely give it a try!
- mekster 6y agoI just don't get why people write queries like that in a convoluted way which is hard to decipher and possibly optimize with so many noises whose knowledge becomes useless on the next language/framework of choice than a straight forward SQL whose knowledge can live for decades.
- gmac 6y agoI’m not exactly sure what you’re getting at here. The whole point of this library is that one call = one predictable query, and the SQL it generates is certainly no more convoluted than required to produce the desired result.
- mekster 6y agoWhat was so hard to grasp with my comment? If your query was meant to say, SELECT * FROM users JOIN posts ON posts.userId = users.id the ORM has so many unnecessary characters just to get to this straight forward SQL and you have to learn how to make sure the way you write will generate the SQL that you want that uses the proper index everytime you change framework is just something I wouldn't do. I didn't say the SQL generated is convoluted but the way you need to write so many characters that doesn't hold a meaning is convoluted.
- gmac 6y agoThe problem is that there's no obvious, simple way to bring the results of this query, structured and typed appropriately (with posts nested inside users), into our running code. So we have to choose what we think is the least-worst compromise. Since I wrote the library I am obviously anything but impartial, but the compromise I outlined above is one I'm pretty happy with (and it's much easier to read if you add a few line breaks). It's a bit noisier than your JOIN query, but for the price of that noise you get type safety and an appropriate JSON structure for the result.