问题描述
在Golang中处理一对多或多对多SQL关系时，将行映射到结构的最佳方式(高效，推荐的类似Go")是什么?
When dealing with a one-to-many or many-to-many SQL relationship in Golang, what is the best (efficient, recommended, "Go-like") way of mapping the rows to a struct?
以下面的示例设置为例，我试图详细介绍每种方法的优缺点，但想知道社区的建议.
Taking the example setup below I have tried to detail some approaches with Pros and Cons of each but was wondering what the community recommends.
- 与PostgreSQL配合使用(可以通用，但不包括MySQL/Oracle特定功能)
- 效率-无需强求每种组合
- 无ORM-理想情况下，仅使用
database/sql和jmoiron/sqlx
为清楚起见，我已删除错误处理
模型
type Tag struct {
ID int
Name string
}
type Item struct {
ID int
Tags []Tag
}
数据库
CREATE TABLE item (
id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
);
CREATE TABLE tag (
id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name VARCHAR(160),
item_id INT REFERENCES item(id)
);
方法1-选择所有项目，然后为每个项目选择标签
var items []Item
sqlxdb.Select(&items, "SELECT * FROM item")
for i, item := range items {
var tags []Tag
sqlxdb.Select(&tags, "SELECT * FROM tag WHERE item_id = $1", item.ID)
items[i].Tags = tags
}
优点
- 简单
- 易于理解
缺点
- 效率低下，数据库查询数量与项目数量成正比
方法2-构造SQL连接并手动遍历行
var itemTags = make(map[int][]Tag)
var items = []Item{}
rows, _ := sqlxdb.Queryx("SELECT i.id, t.id, t.name FROM item AS i JOIN tag AS t ON t.item_id = i.id")
for rows.Next() {
var (
itemID int
tagID int
tagName string
)
rows.Scan(&itemID, &tagID, &tagName)
if tags, ok := itemTags[itemID]; ok {
itemTags[itemID] = append(tags, Tag{ID: tagID, Name: tagName,})
} else {
itemTags[itemID] = []Tag{Tag{ID: tagID, Name: tagName,}}
}
}
for itemID, tags := range itemTags {
items = append(Item{
ID: itemID,
Tags: tags,
})
}
优点
- 单个数据库调用和游标，可以在不占用过多内存的情况下进行循环
缺点
- 具有多个联接和结构上的许多属性的复杂且难以开发的
- 表现不太好；更多的内存使用量和处理时间，而不是更多的网络调用
方法3失败-sqlx结构扫描
尽管失败了，但我想包括这种方法，因为我发现这是我当前的效率与开发简单性相结合的目标.我的希望是通过在每个结构字段上显式设置 db 标记 sqlx 可以执行一些高级结构扫描
Despite failing I want to include this approach as I find it to be my current aim of efficiency paired with development simplicity. My hope was by explicitly setting the db tag on each struct field sqlx could do some advanced struct scanning
var items []Item
sqlxdb.Select(&items, "SELECT i.id AS item_id, t.id AS tag_id, t.name AS tag_name FROM item AS i JOIN tag AS t ON t.item_id = i.id")
不幸的是，此错误显示为* [] Item 中缺少目的地名称tag_id的错误，导致我认为 StructScan 不够先进，无法递归遍历行(不批评-这是一个复杂的场景)
Unfortunately this errors out as missing destination name tag_id in *[]Item leading me to believe the StructScan is not advanced enough to recursively loop through rows (no criticism - it is a complicated scenario)
可能的方法4-PostgreSQL数组聚合器和 GROUP BY
Possible approach 4 - PostgreSQL array aggregators and GROUP BY
虽然我确定这将不会起作用，但是我已经包含了这个未经测试的选项，以查看是否可以对其进行改进，以便它可以起作用.
While I am sure this will not work I have included this untested option to see if it could be improved upon so it may work.
var items = []Item{}
sqlxdb.Select(&items, "SELECT i.id as item_id, array_agg(t.*) as tags FROM item AS i JOIN tag AS t ON t.item_id = i.id GROUP BY i.id")
我有时间的时候会尝试在这里进行一些实验.
When I have some time I will try and run some experiments here.
推荐答案
postgres中的sql:
the sql in postgres :
create schema temp;
set search_path = temp;
create table item
(
id INT generated by default as identity primary key
);
create table tag
(
id INT generated by default as identity primary key,
name VARCHAR(160),
item_id INT references item (id)
);
create view item_tags as
select id,
(
select
array_to_json(array_agg(row_to_json(taglist.*))) as array_to_json
from (
select tag.name, tag.id
from tag
where item_id = item.id
) taglist ) as tags
from item ;
-- golang query this maybe
select row_to_json(row)
from (
select * from item_tags
) row;
然后golang查询此sql:
then golang query this sql:
select row_to_json(row)
from (
select * from item_tags
) row;
并解组结构:
专业版:
-
postgres管理数据关系.使用sql函数添加/更新数据.
postgres manage the relation of data. add / update data with sql functions.
golang管理业务模型和逻辑.
golang manage business model and logic.
这是简单的方法.
.
这篇关于高效地将一对多多对多数据库映射到Golang中的struct的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！