-
Notifications
You must be signed in to change notification settings - Fork 923
Nested slice of struct type #3170
-
I have a many to many relationship in postgres:
CREATE TABLE posts ( id VARCHAR(36) NOT NULL PRIMARY KEY, title VARCHAR(255) NOT NULL, created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP(3) NOT NULL ); CREATE TABLE users ( id VARCHAR(36) NOT NULL PRIMARY KEY, name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL UNIQUE, created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP(3) NOT NULL ); CREATE TABLE users_posts ( id VARCHAR(36) NOT NULL PRIMARY KEY, user_id VARCHAR(36) NOT NULL, post_id VARCHAR(36) NOT NULL, created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP(3) NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE, UNIQUE (user_id, post_id) );
I need to search for the user and all his posts, but SQL does not generate a struct of type []Posts ending up like this:
type FindManyUsersRow struct { ID string Name string Email string CreatedAt time.Time Post Posts }
I need it to look like this:
type FindManyUsersRow struct { ID string Name string Email string CreatedAt time.Time Post []Posts }
my query:
-- name: FindManyUsers :many SELECT u.id, u.name, p.email, u.created_at, sqlc.embed(c) FROM users u JOIN users_posts up ON up.user_id = u.id JOIN posts p ON p.id = up.post_id WHERE (up.post_id = ANY(@categories::TEXT[]) OR @categories::TEXT[] IS NULL) AND ( u.name ILIKE '%' || COALESCE(sqlc.narg('search'), @search) || '%' OR u.email ILIKE '%' || COALESCE(sqlc.narg('search'), @search)|| '%' ) ORDER BY p.created_at DESC;
Is it possible to do this with sqlc?
Beta Was this translation helpful? Give feedback.
All reactions
Replies: 3 comments 2 replies
-
This is the composite type scenario. I'm also yearning for this functionality.
In postgres the query would look like.
-- name: FindManyUsersAndTheirPosts :many
SELECT
u.id,
u.name,
u.email,
u.created_at,
(
SELECT array_agg(posts)
FROM
users_posts up
JOIN posts on posts.id=up.post_id
WHERE
up.user_id = u.id
)
FROM users u
WHERE
(
u.name ILIKE '%' || COALESCE(sqlc.narg('search'), @search) || '%'
OR
u.email ILIKE '%' || COALESCE(sqlc.narg('search'), @search) || '%'
);
It's how you would do graphql style queries in postgresql instead of something like
users {
id
name
posts {
title
}
}
}
Unfortunately I don't think it is supported in sqlc.
One workaround is to go through json using to_json but you lose type safety.
SELECT
u.id,
u.name,
u.email,
u.created_at,
(
SELECT json_agg(to_json(posts))
FROM
users_posts up
JOIN posts on posts.id=up.post_id
WHERE
up.user_id = u.id
)
FROM users u
WHERE
(
u.name ILIKE '%' || 'username' || '%'
OR
u.email ILIKE '%' || '' || '%'
);
Beta Was this translation helpful? Give feedback.
All reactions
-
I also tried this way, but we lost the security of the alternative types so far I was doing the search without using SQLC.
Beta Was this translation helpful? Give feedback.
All reactions
-
I also have interest on this feature but the issue seems to be stale, no consideration as feature request?
Beta Was this translation helpful? Give feedback.
All reactions
-
👀 1
-
As mentioned in the first comment, this requires support for composite types, which currently isn't being worked on.
I'm open to someone doing the work, but I think it would be a heavy lift with our current architecture.
Beta Was this translation helpful? Give feedback.
All reactions
-
I understand this would be difficult and require major changes, maybe lose backward compatibility and have to release as sqlc2 and you probably don't have the resources, but man full postgres type support would make sqlc magical. One day if I have spare time maybe...
Beta Was this translation helpful? Give feedback.