英文:
Modeling struct for INNER JOIN
问题
我已经翻译了你提供的内容，以下是翻译的结果：
我有两个表，一个是order表，另一个是order_items表。我试图使用INNER JOIN将这两个表的列连接在一起，以在调用所有订单时创建一个新的切片。但是order_items是一个切片，结果不太好。
这是完整的代码：https://go.dev/play/p/OoqL_JX3yZX。我想要做的是根据order_id将order_items分组，以实现以下结果。
{
"id": 1,
"delivery_fee": "50660.45",
"subtotal": "50600.88",
"total": "656600.44",
"products": [
{
"order_item_id": 1,
"order_id": 1,
"product_id": 1,
"quantity": 4,
"order_status": "pending",
"order_date": "2022-11-05T10:12:27.247305Z",
"customer": "admin2"
},
{
"order_item_id": 2,
"order_id": 1,
"product_id": 2,
"quantity": 1,
"order_status": "pending",
"order_date": "2022-11-05T10:12:27.247305Z",
"customer": "admin2"
}
],
"id": 2,
"delivery_fee": "50660.45",
"subtotal": "50600.88",
"total": "656600.44",
"products": [
{
"order_item_id": 3,
"order_id": 2,
"variation_id": 1,
"quantity": 10,
"order_status": "pending",
"order_date": "2022-11-05T10:13:09.996713Z",
"customer": "admin2"
},
{
"order_item_id": 4,
"order_id": 2,
"variation_id": 2,
"quantity": 8,
"order_status": "pending",
"order_date": "2022-11-05T10:13:09.996713Z",
"customer": "admin2"
}
]
}
但是我得到的结果是这样的：
{
"id": 2,
"delivery_fee": "50660.45",
"subtotal": "50600.88",
"total": "656600.44",
"products": [
{
"order_item_id": 1,
"order_id": 1,
"product_id": 1,
"quantity": 4,
"order_status": "pending",
"order_date": "2022-11-05T10:12:27.247305Z",
"customer": "admin2"
},
{
"order_item_id": 2,
"order_id": 1,
"product_id": 2,
"quantity": 1,
"order_status": "pending",
"order_date": "2022-11-05T10:12:27.247305Z",
"customer": "admin2"
},
{
"order_item_id": 3,
"order_id": 2,
"product_id": 1,
"quantity": 10,
"order_status": "pending",
"order_date": "2022-11-05T10:13:09.996713Z",
"customer": "admin2"
},
{
"order_item_id": 4,
"order_id": 2,
"variation_id": 2,
"quantity": 8,
"order_status": "pending",
"order_date": "2022-11-05T10:13:09.996713Z",
"customer": "admin2"
}
]
}
我认为问题可能出在SQL查询中的INNER JOIN上。
这是我的结构体：
type AllOrders struct {
ID int64 `json:"order_id"`
DeliveryFee decimal.Decimal `json:"delivery_fee"`
Subtotal decimal.Decimal `json:"subtotal"`
Total decimal.Decimal `json:"total"`
Product []Product `json:"products"`
Date time.Time `json:"order_date"`
Owner string `json:"customer"`
}
type Product struct {
ID int64 `json:"order_id"`
OrderID int64 `json:"order_item_id"`
ProductID int64 `json:"product_id"`
Quantity int64 `json:"quantity"`
Status string `json:"order_status"`
Date time.Time `json:"order_date"`
Owner string `json:"customer"`
}
对于SQL CRUD函数：
-- name: ListOrdersItem :many
SELECT * FROM orders A
INNER JOIN order_items B
ON A.id = B.order_id;
const listOrdersItem = `-- name: ListOrdersItem :many
SELECT a.id, a.owner, a.status, delivery_fee, subtotal, total, a.created_at, b.id, b.owner, order_id, variation_id, b.status, quantity, b.created_at FROM orders A
INNER JOIN order_items B
ON A.id = B.order_id
`
type ListOrdersItemRow struct {
ID int64 `json:"id"`
Owner string `json:"owner"`
Status string `json:"status"`
DeliveryFee decimal.Decimal `json:"delivery_fee"`
Subtotal decimal.Decimal `json:"subtotal"`
Total decimal.Decimal `json:"total"`
CreatedAt time.Time `json:"created_at"`
ID_2 int64 `json:"id_2"`
Owner_2 string `json:"owner_2"`
OrderID int64 `json:"order_id"`
ProductID int64 `json:"product_id"`
Status_2 string `json:"status_2"`
Quantity int64 `json:"quantity"`
CreatedAt_2 time.Time `json:"created_at_2"`
}
func (q *Queries) ListOrdersItem(ctx context.Context) ([]ListOrdersItemRow, error) {
rows, err := q.db.QueryContext(ctx, listOrdersItem)
if err != nil {
return nil, err
}
defer rows.Close()
items := []ListOrdersItemRow{}
for rows.Next() {
var i ListOrdersItemRow
if err := rows.Scan(
&i.ID,
&i.Owner,
&i.Status,
&i.DeliveryFee,
&i.Subtotal,
&i.Total,
&i.CreatedAt,
&i.ID_2,
&i.Owner_2,
&i.OrderID,
&i.ProductID,
&i.Status_2,
&i.Quantity,
&i.CreatedAt_2,
); err != nil {
return nil, err
}
items = append(items, i)
}
if err := rows.Close(); err != nil {
return nil, err
}
if err := rows.Err(); err != nil {
return nil, err
}
return items, nil
}
非常抱歉代码很长，但是我已经努力解决这个问题几个星期了。我尝试了一些其他的SQL查询，但它们都产生了与我想要的结果相差很远的结果。这就是为什么我必须坚持使用JOIN的原因。请问如何实现我想要的结果？
英文:
I have two tables one is the order table, and the other is the order_items table. Using an INNER JOIN, I try to join the columns together to create a new slice when calling all the orders. But the order_items is a slice and does not come out well.
This is the full code: https://go.dev/play/p/OoqL_JX3yZX. What I am trying to do is to group the order_items according to the order_id to achieve this result.
{
"id": 1,
"delivery_fee": "50660.45",
"subtotal": "50600.88",
"total": "656600.44",
"products": [
{
"order_item_id": 1,
"order_id": 1,
"product_id": 1,
"quantity": 4,
"order_status": "pending",
"order_date": "2022-11-05T10:12:27.247305Z",
"customer": "admin2"
},
{
"order_item_id": 2,
"order_id": 1,
"product_id": 2,
"quantity": 1,
"order_status": "pending",
"order_date": "2022-11-05T10:12:27.247305Z",
"customer": "admin2"
}
],
"id": 2,
"delivery_fee": "50660.45",
"subtotal": "50600.88",
"total": "656600.44",
"products": [
{
"order_item_id": 3,
"order_id": 2,
"variation_id": 1,
"quantity": 10,
"order_status": "pending",
"order_date": "2022-11-05T10:13:09.996713Z",
"customer": "admin2"
},
{
"order_item_id": 4,
"order_id": 2,
"variation_id": 2,
"quantity": 8,
"order_status": "pending",
"order_date": "2022-11-05T10:13:09.996713Z",
"customer": "admin2"
}
]
}
But the result I am getting is this:
{
"id": 2,
"delivery_fee": "50660.45",
"subtotal": "50600.88",
"total": "656600.44",
"products": [
{
"order_item_id": 1,
"order_id": 1,
"product_id": 1,
"quantity": 4,
"order_status": "pending",
"order_date": "2022-11-05T10:12:27.247305Z",
"customer": "admin2"
},
{
"order_item_id": 2,
"order_id": 1,
"product_id": 2,
"quantity": 1,
"order_status": "pending",
"order_date": "2022-11-05T10:12:27.247305Z",
"customer": "admin2"
},
{
"order_item_id": 3,
"order_id": 2,
"product_id": 1,
"quantity": 10,
"order_status": "pending",
"order_date": "2022-11-05T10:13:09.996713Z",
"customer": "admin2"
},
{
"order_item_id": 4,
"order_id": 2,
"variation_id": 2,
"quantity": 8,
"order_status": "pending",
"order_date": "2022-11-05T10:13:09.996713Z",
"customer": "admin2"
}
]
}
I believe that the problem might be from the INNER JOIN in the SQL query.
These are my structs:
type AllOrders struct {
ID int64 `json:"order_id"`
DeliveryFee decimal.Decimal `json:"delivery_fee"`
Subtotal decimal.Decimal `json:"subtotal"`
Total decimal.Decimal `json:"total"`
Product []Product `json:"products"`
Date time.Time `json:"order_date"`
Owner string `json:"customer"`
}
type Product struct {
ID int64 `json:"order_id"`
OrderID int64 `json:"order_item_id"`
ProductID int64 `json:"product_id"`
Quantity int64 `json:"quantity"`
Status string `json:"order_status"`
Date time.Time `json:"order_date"`
Owner string `json:"customer"`
}
For the SQL CRUD function:
-- name: ListOrdersItem :many
SELECT * FROM orders A
INNER JOIN order_items B
ON A. id =B. order_id;
const listOrdersItem = `-- name: ListOrdersItem :many
SELECT a.id, a.owner, a.status, delivery_fee, subtotal, total, a.created_at, b.id, b.owner, order_id, variation_id, b.status, quantity, b.created_at FROM orders A
INNER JOIN order_items B
ON A. id =B. order_id
`
type ListOrdersItemRow struct {
ID int64 `json:"id"`
Owner string `json:"owner"`
Status string `json:"status"`
DeliveryFee decimal.Decimal `json:"delivery_fee"`
Subtotal decimal.Decimal `json:"subtotal"`
Total decimal.Decimal `json:"total"`
CreatedAt time.Time `json:"created_at"`
ID_2 int64 `json:"id_2"`
Owner_2 string `json:"owner_2"`
OrderID int64 `json:"order_id"`
ProductID int64 `json:"product_id"`
Status_2 string `json:"status_2"`
Quantity int64 `json:"quantity"`
CreatedAt_2 time.Time `json:"created_at_2"`
}
func (q *Queries) ListOrdersItem(ctx context.Context) ([]ListOrdersItemRow, error) {
rows, err := q.db.QueryContext(ctx, listOrdersItem)
if err != nil {
return nil, err
}
defer rows.Close()
items := []ListOrdersItemRow{}
for rows.Next() {
var i ListOrdersItemRow
if err := rows.Scan(
&i.ID,
&i.Owner,
&i.Status,
&i.DeliveryFee,
&i.Subtotal,
&i.Total,
&i.CreatedAt,
&i.ID_2,
&i.Owner_2,
&i.OrderID,
&i.ProductID,
&i.Status_2,
&i.Quantity,
&i.CreatedAt_2,
); err != nil {
return nil, err
}
items = append(items, i)
}
if err := rows.Close(); err != nil {
return nil, err
}
if err := rows.Err(); err != nil {
return nil, err
}
return items, nil
}
I apologize for the long code, but I have been banging my head for weeks on how to solve this. I have tried some other SQL queries but they all produce a result far from what I want. That was why I had to stick to JOIN. How do I achieve the result I want?
答案1
得分: 1
struct类型非常适合表示关系数据库的表行（也称为元组）。因此，通常情况下，为每个要在Go程序中表示其数据的表创建一个struct类型是一个好主意。例如，如果你的数据库有一个名为orders的表和另一个名为order_items的表，那么你的Go程序应该有相应的Order和OrderItem结构类型，用于专门表示这两个表中的记录。
type Order struct {
ID int `json:"id"`
Total int64 `json:"total"`
SubTotal int64 `json:"subTotal"`
Fees int64 `json:"fees"`
// ...
Items []*OrderItem `json:"items"`
}
type OrderItem struct {
ID int `json:"id"`
OrderID int `json:"orderID"`
Quantity int `json:"quantity"`
// ...
}
在关系为一对多的两个连接表中选择记录时，我认为最好分为两个独立的步骤和两个独立的查询来完成。例如：
query1 := `SELECT
id
, total
, sub_total
, fees
...
FROM orders
WHERE ...`
rows1, err := db.QueryContext(ctx, query1, args...)
if err != nil {
return err
}
defer rows1.Close()
var orders []*Order
var ordersByID = make(map[int]*Order) // 将在order_items行循环中使用
for rows1.Next() {
order := new(Order)
err := rows1.Scan(
&order.ID,
&order.Total,
&order.SubTotal,
&order.Fees,
// ...
)
if err != nil {
return err
}
orders = append(orders, order)
ordersByID[order.ID] = order
}
if err := rows1.Err(); err != nil {
return err
}
请注意，下面的JOIN仅在orders表的某些列需要在WHERE条件中使用，或者如果这些列需要包含在每个OrderItem中时才是必需的。如果您的用例中没有上述情况适用，则可以省略JOIN。
query2 := `SELECT
i.id
, i.order_id
, i.quantity
...
FROM order_items i
LEFT JOIN orders o ON o.id=i.order_id
WHERE ...`
rows2, err := db.QueryContext(ctx, query2, args...)
if err != nil {
return err
}
defer rows2.Close()
for rows2.Next() {
item := new(OrderItem)
err := rows2.Scan(
&item.ID,
&item.OrderID,
&item.Quantity,
// ...
)
if err != nil {
return err
}
// 将item附加到正确的order中
order := ordersByID[item.OrderID]
order.Items = append(order.Items, item)
}
if err := rows2.Err(); err != nil {
return err
}
// 此时，在上面代码片段中声明的orders切片将包含所有订单及其所有项目。
<details>
<summary>英文:</summary>
A `struct` type is a perfect fit for representing a relational database's table row (also known as *tuple*). So, in general, it is a good idea to have a struct type for each table whose data you want to represent in your Go program. For example, if your database has a table called `orders` and another called `order_items`, then your Go program should have the corresponding `Order` and `OrderItem` struct types that will be used specifically to represent the records in those two tables.
```go
type Order struct {
ID int `json:"id"`
Total int64 `json:"total"`
SubTotal int64 `json:"subTotal"`
Fees int64 `json:"fees"`
// ...
Items []*OrderItem `json:"items"`
}
type OrderItem struct {
ID int `json:"id"`
OrderID int `json:"orderID"`
Quantity int `json:"quantity"`
// ...
}
Selecting records from two joined tables where the relationship is one-to-many is, in my opinion at least, best done in two separate steps with two separate queries. For example:
query1 := `SELECT
id
, total
, sub_total
, fees
...
FROM orders
WHERE ...`
rows1, err := db.QueryContext(ctx, query1, args...)
if err != nil {
return err
}
defer rows1.Close()
var orders []*Order
var ordersByID = make(map[int]*Order) // will be used by order_items rows loop
for rows1.Next() {
order := new(Order)
err := rows1.Scan(
&order.ID,
&order.Total,
&order.SubTotal,
&order.Fees,
// ...
)
if err != nil {
return err
}
orders = append(orders, order)
ordersByID[order.ID] = order
}
if err := rows1.Err(); err != nil {
return err
}
Note that the JOIN below is only necessary if some of the orders table's columns need to be used in the WHERE condition, or if some of those columns need to be included in every single OrderItem. If none of the above applies to your use-case then omit the JOIN.
query2 := `SELECT
i.id
, i.order_id
, i.quantity
...
FROM order_items i
LEFT JOIN orders o ON o.id=i.order_id
WHERE ...`
rows2, err := db.QueryContext(ctx, query2, args...)
if err != nil {
return err
}
defer rows2.Close()
for rows2.Next() {
item := new(OrderItem)
err := rows2.Scan(
&item.ID,
&item.OrderID,
&item.Quantity,
// ...
)
if err != nil {
return err
}
// append the item to the correct order
order := ordersByID[item.OrderID]
order.Items = append(order.Items, item)
}
if err := rows2.Err(); err != nil {
return err
}
// At this point the orders slice declared in the previous
// snippet will contain all the orders will all their items.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论