need advice how to configure driver to return prefixed or drizzle style selections
Ideally I would like to get shape with a query like drizzle offers, meaning each table has its own object (eg. products joined with tax would return multiple rows as {product:{}, tax:{}}) or at least add a prefix to the columns. From what I read yesterday, this is beyond the scope of kysely, and more of a db driver task. Has anyone done something similar in combination with kysely and could give me a hint, because I don't really know how to go about it. Thanks!
Solution:Jump to solution
That is an ORM task. You can't get something like that in the general case without thousands of lines of code. That code includes modifying the SQL to get foreign keys selected if you don't manually select them, figuring out which row goes with which row, renaming selections and all kinds of crap.
All of which will fuck up the rest of the query. You can't build the rest of the query using arbitary SQL anymore since the query has been mangled into unrecognizable abomination. The selection you try to reference no longer exists, you can't use group by since the query structure is completely differenet from what you think etc.
At that point, you need more higher level ORM concepts that are able to deal with the mangled query....
2 Replies
Solution
That is an ORM task. You can't get something like that in the general case without thousands of lines of code. That code includes modifying the SQL to get foreign keys selected if you don't manually select them, figuring out which row goes with which row, renaming selections and all kinds of crap.
All of which will fuck up the rest of the query. You can't build the rest of the query using arbitary SQL anymore since the query has been mangled into unrecognizable abomination. The selection you try to reference no longer exists, you can't use group by since the query structure is completely differenet from what you think etc.
At that point, you need more higher level ORM concepts that are able to deal with the mangled query.
If you want the queries to be performant, you can't use joins in every case. Consider getting this structure
Now let's say there are 10 of each.
Using subqueries: You fetch 10 * (10 + 10 + 10 + 10) = 400 items from the DB
Using joins: You fetch 10 * 10 * 10 * 10 * 10 = 100000 items from the db.
Selecting the query type and keeping things performant, while allowing other SQL features is borderline impossible. I've tried that with objection.js.
Kysely offers these helpers for creating JSON subqueries that don't suffer from the performance issues with multiple joins:
https://kysely.dev/docs/recipes/relations
If you want something like that to be automatic, you need an ORM. Simple as that.