postgresql - Go SQLBoiler:查询关系并返回
问题描述
我正在使用 SQLBoiler,如何查询关系并返回?我有两个单独的产品及其价格/货币的数据库表。我想查询两者并将它们一起返回到一个列表中,比如getAll
request.
这是初始模式文件。
-- schema.sql
drop table if exists products cascade;
drop table if exists money;
create table products (
id serial not null primary key,
user_id int not null,
name varchar(255) not null,
description text not null,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
deleted_at timestamptz
);
create table money (
product_id int not null primary key,
currency_code text not null default 'USD',
units int not null,
nanos int not null,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
foreign key (product_id) references products (id) on delete cascade
);
create unique index idx_products_name
on products (name);
insert into products (user_id, name, description) values (1, 'Something', 'This is something.');
insert into products (user_id, name, description) values (2, 'Anything', 'This is anything.');
insert into money (product_id, currency_code, units, nanos) values (1, 'GBP', 450, 75);
insert into money (product_id, currency_code, units, nanos) values (2, 'CAD', 9, 20);
...
func (s *Server) Index(context.Context, *pb.Empty) (*pb.IndexProductResponse, error) {
db := InitPg()
defer db.Close()
// Make a slice of Product type from generated protobuf
products := make([]*pb.Product, 0)
// Get all products (without price)
ps, err := models.Products().All(context.Background(), db)
LogAndReport(&pb.IndexProductResponse{}, err)
// How do I query each product's price from money table?
// Or
// How do I query all money and assign to each related products?
// fmt.Println("@ ps here >", ps)
// Append queried products into products slice to return
for _, p := range ps {
products = append(products,
&pb.Product{
Id: int32(p.ID),
Name: p.Name,
Description: p.Description,
// I need to return/assign price information here.
// I can do like this with Gorm.
//
// Price: &pb.Money{
// CurrencyCode: p.Price.CurrencyCode,
// Units: p.Price.Units,
// Nanos: p.Price.Nanos,
// },
UserId: int32(p.UserID),
})
}
return &pb.IndexProductResponse{Products: products}, nil
}
...
这是来自创建产品,但几乎与预期的 GraphQL 产品列表中的结果相同:
{
"data": {
"products": [
{
"id": "1",
"name": "Something",
"description": "This is something.",
"price": {
"currencyCode": "USD",
"units": 9,
"nanos": 93
},
"userId": "1"
},
{
"id": "2",
"name": "Anything",
"description": "This is anything.",
"price": {
"currencyCode": "USD",
"units": 24,
"nanos": 56
},
"userId": "1"
}
]
},
...
}
...
这是产品服务的 protobuf 文件。
service ProductService {
rpc Index(Empty) returns (IndexProductResponse) {}
}
message Money {
// The 3-letter currency code defined in ISO 4217.
string currency_code = 1;
// The whole units of the amount.
// For example if `currencyCode` is `"USD"`, then 1 unit is one US dollar.
int64 units = 2;
// Number of nano (10^-9) units of the amount.
// The value must be between -999,999,999 and +999,999,999 inclusive.
// If `units` is positive, `nanos` must be positive or zero.
// If `units` is zero, `nanos` can be positive, zero, or negative.
// If `units` is negative, `nanos` must be negative or zero.
// For example $-1.75 is represented as `units`=-1 and `nanos`=-750,000,000.
int32 nanos = 3;
}
message Product {
int32 id = 1;
string name = 2;
string description = 3;
Money price = 4;
int32 user_id = 5;
}
message IndexProductResponse {
repeated Product products = 1;
}
解决方案
您想加载您的主要对象 Product 及其所有相关的 Money 行吗?
这应该这样做:
products, err :=
models.Products(
qm.Load(models.ProductRels.ProductMoneys)
).All(context.Background(), db)
这将生成并执行两个 SQL 查询。第一个查询应该是
SELECT * FROM "products"
并将检索所有产品。第二个查询应该类似于
SELECT * FROM "money" WHERE ("money"."product_id" IN ($1,$2,$3))
并将检索所有产品的所有款项。(该IN
条款的确切内容将取决于退回的产品数量。)
然后您应该能够按如下方式访问相关行:
products[0].R.ProductMoneys
注意:ProductRels
和/或ProductMoneys
可能不是 SQLBoiler 生成的确切名称。我不得不在这里猜测。确保检查生成的代码以获取正确的名称。
推荐阅读
- javascript - html2canvas 在 SVG 上下载带有背景图像的图像
- python - 从其他两个列表派生一个新列表列表,如果 L1 中的元素不在 L2 中,则将其追加
- meteor - Vue路由器无法使用MeteorJS处理单个文件组件
- bash - 变量神秘消失?AWS CodeBuild
- php - 仅在实时服务器上升级到 PHP 7.1 后 CSV 导入超慢
- react-native - 隐藏 topBar 时弹出手势停止工作
- javascript - 将 img src 转换为 javascript 或 jquery 中的输入文件
- html - CSS Flexbox 中的分栏符
- input - “ ts.get_intraday ”内的用户输入
- spring-boot - 带有 Mockito 的 Spring Boot 测试:@Validated 注释在单元测试期间被忽略