Nested query result
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
:
SELECT id as user_id, name as user_name, posts.id as post_id, title as post_title, body as post_bodyFROM usersINNER JOIN posts on posts.user_id = users.id
For this query, TypeSQL by default will generate a type result like this:
const result = await selectUserPosts(conn);
//result typeconst result: { user_id: number; user_name: string; post_id: number; post_title: string; post_body: string;}[];
If you want to generate a nested query result, you must annotate the query with @nested
in a SQL comment.
For example:
-- @nestedSELECT *FROM usersINNER JOIN posts on posts.user_id = users.id
Now TypeSQL will generate a nested type that will be returned when you run selectUserPostsNested(conn)
:
const result = await selectUserPostsNested(conn);
//result typeconst result: { id: number; name: string; posts: { id: number; title: string; body: string; }[];}[];
Relationship Patterns
One To One
Below is an example where one Customer is associated with one Address.
CREATE TABLE customers( id SERIAL, full_name VARCHAR(255) NOT NULL, address_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY `pk_id`(`id`), CONSTRAINT `fk_customers_addresses` FOREIGN KEY (`address_id`) REFERENCES `addresses` (`id`));
CREATE TABLE addresses( id SERIAL, address VARCHAR(255) NOT NULL, zip_code VARCHAR(16) NOT NULL, city VARCHAR(100) NOT NULL, state CHAR(2) NOT NULL, PRIMARY KEY `pk_id`(`id`));
-- @nestedSELECT *FROM customers cINNER JOIN addresses as address ON address.id = c.address_idWHERE c.id = :customer_id
Use the generated function:
const result = await selectCustomerWithAddressNested(conn, { customer_id: 1 });
/* result type{ id: number, full_name: string, address_id: number, address: { id: number, address: string, zip_code: string, city: string, state: string }}/*
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).
-- This query can't be annotated with @nestedSELECT id, name, title, bodyFROM usersINNER JOIN posts on posts.user_id = users.id