使用sqlc、PostgreSQL和Golang来构建嵌套数据结构

huangapple go评论87阅读模式
英文:

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查询连接flatsuser_flatsusers表时,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;

huangapple
  • 本文由 发表于 2023年6月1日 02:38:04
  • 转载请务必保留本文链接:https://go.coder-hub.com/76376420.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定