TypeSQL also has support for nested queries results.
When you create your queries, by default, TypeSQL will generate a tabular result type, even if your queries include JOINs relations. For example, consider the query below in the file select-user-posts.sql:
For this query, TypeSQL by default will generate a type result like this:
If you want to generate a nested query result, you must annotate the query with @nested in a SQL comment.
For example:
Now TypeSQL will generate a nested type that will be returned when you run selectUserPostsNested(conn):
Relationship Patterns
One To One
Below is an example where one Customer is associated with one Address.
Use the generated function:
Nested result limitations
When you use nested queries you must project the primary key of each relation in the query. If you have a query like this SELECT ... FROM users LEFT JOIN posts LEFT JOIN comments you must project the primary of the tables: users, posts and comments.
For example, the query below can’t be annotated with @nested because it doesn’t project the primary key of the posts table (posts.id).