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?
CodePudding user response：
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.
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.