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.
OK, 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);
:)
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.