Home > Software design >  Appending a slice to another slice using a for loop
Appending a slice to another slice using a for loop

Time:11-06

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.
  • Related