4 ms·
I'm also awed by this! :) 1. Whether `Selectable[]` can be used to query for a subset of fields and how. Right — this is not (currently) supported. I guess i
by gmac 7y ago
I'm also awed by this!
:)
1. Whether `Selectable[]` can be used to query for a subset of fields and how.
Right — this is not (currently) supported. I guess if you had wide tables of large values, this could be an important optimisation, but it hasn't been a need for me as yet.
2. In the `to_jsonb(authors)` example, what would you get back in the `author` field if there were multiple authors with the same `author.id` value? An array of `author.Selectable` objects? This part is awesome but brittle, isn't it?
Multiple authors with the same id isn't going to happen, since id is intended as a primary key, so I'd argue that the example as given isn't brittle. On the other hand, there's a fair question about what happens for many-to-many joins, and since my use case hasn't yet required this I haven't given it much thought.
- gmac 7y agoOK, I gave the one-to-many queries a bit more thought, and the converse join query (getting each author with all their books, rather than all books each with their author) works nicely with a GROUP BY: type authorBookSQL = s.authors.SQL | s.books.SQL; type authorBookSelectable = s.authors.Selectable & { books: s.books.Selectable }; const query = db.sql<authorBookSQL>` SELECT ${"authors"}.*, jsonb_agg(${"books"}.*) AS ${"books"} FROM ${"books"} JOIN ${"authors"} ON ${"authors"}.${"id"} = ${"books"}.${"authorId"} GROUP BY ${"authors"}.${"id"}`, authorBooks: authorBookSelectable[] = await query.run(db.pool); This exploits the fact that selecting all fields is, logically enough, permitted when grouping by primary key (https://www.postgresql.org/docs/current/sql-select.html#SQL-GROUPBY https://www.postgresql.org/docs/current/sql-select.html#SQL-... and https://dba.stackexchange.com/questions/158015/why-can-i-select-all-fields-when-grouping-by-primary-key-but-not-when-grouping-b https://dba.stackexchange.com/questions/158015/why-can-i-sel...) I'll update demo.ts and README shortly.