英文:
Structuring Nested Data with sqlc, PostgreSQL, and Golang
问题
我正在使用sqlc与Golang项目中的PostgreSQL数据库。我的数据库具有以下简化的模式:
CREATE TABLE users (
username VARCHAR(255) PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL
);
CREATE TABLE flats (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL
);
CREATE TABLE user_flats (
id SERIAL PRIMARY KEY,
username VARCHAR(255) NOT NULL REFERENCES users(username),
flat_id INTEGER NOT NULL REFERENCES flats(id)
);
我需要根据给定的flat ID检索与用户关联的flat详细信息,并返回一个类似于以下Go结构体的结果:
type FlatDetails struct {
FlatID int32
FlatName string
Users []User
}
然而,当使用SQL查询连接flats
、user_flats
和users
表时,sqlc生成的Go结构体没有嵌套的User切片,而是将每个用户字段视为单独的字段。以下是一个示例连接查询:
SELECT
f.id AS flat_id,
f.name AS flat_name,
u.username,
u.email
FROM
flats f
JOIN
user_flats uf ON f.id = uf.flat_id
JOIN
users u ON uf.username = u.username
WHERE
f.id = $1;
虽然当前的方法可以通过手动映射字段到Go结构体来实现,但在更复杂的连接和更大的表中,这种方法变得相当复杂和低效。是否有一种更高效或更符合惯例的方法来处理这种情况,使用sqlc可以直接将查询结果映射到具有嵌套切片或数组的结构体中?非常感谢您提供的任何指导或建议!
英文:
I am using sqlc with a PostgreSQL database in a Golang project. My database has the following simplified schema:
CREATE TABLE users (
username VARCHAR(255) PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL
);
CREATE TABLE flats (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL
);
CREATE TABLE user_flats (
id SERIAL PRIMARY KEY,
username VARCHAR(255) NOT NULL REFERENCES users(username),
flat_id INTEGER NOT NULL REFERENCES flats(id)
);
I need to retrieve flat details with an array of associated users given a flat ID, intending to return a Go struct like this:
type FlatDetails struct {
FlatID int32
FlatName string
Users []User
}
However, when using an SQL query to join flats
, user_flats
, and users
tables, sqlc generates a Go struct without a nested User slice, treating each user field as an individual field. Here is an example join
SELECT
f.id AS flat_id,
f.name AS flat_name,
u.username,
u.email
FROM
flats f
JOIN
user_flats uf ON f.id = uf.flat_id
JOIN
users u ON uf.username = u.username
WHERE
f.id = $1;
While the current approach works by manually mapping the fields to the Go struct, it becomes quite complicated and inefficient with more complex joins and larger tables. Is there a more efficient or idiomatic way to handle this scenario using sqlc, where I can directly map the query result to a struct with nested slices or arrays? Any pointers or recommendations are highly appreciated!
答案1
得分: 1
如果有人仍在寻找答案,你应该使用sqlc.embed。
例如:
-- name: GetFlatWithUsers :many
SELECT
sqlc.embed(f),
sqlc.embed(u)
FROM
flats f
JOIN
user_flats uf ON f.id = uf.flat_id
JOIN
users u ON uf.username = u.username
WHERE
f.id = $1;
英文:
If anyone is still seeking an answer, you should use sqlc.embed
For example
-- name: GetFlatWithUsers :many
SELECT
sqlc.embed(f),
sqlc.embed(u)
FROM
flats f
JOIN
user_flats uf ON f.id = uf.flat_id
JOIN
users u ON uf.username = u.username
WHERE
f.id = $1;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论