Scan result of joining two tables with the same fields

[olezhek28]

Hello! Help me please. I tried this query and got an error.

select cr.goodzon_order_id,
       cr.seller_id,
       cr.flow,
       cr.source,
       cr.master_order_id,
       cr.is_canceled,
       cr.created_at,
       cr.updated_at,
       cr.route_plan_id,
       c.cargo_lozon_id,
       c.dimension_type,
       c.cargo_type,
       c.is_canceled,
       c.created_at
from cargo_requests cr
    left join cargoes c on cr.goodzon_order_id = c.goodzon_order_id
where cr.goodzon_order_id=700
order by cargo_lozon_id;

My query includes fields with the same name from different tables. Error about it. Scany does not understand who is who. I used table aliases but it didn't help. What am I doing wrong?

I use method pgxscan.ScanOne(dest, rows) to struct GetCargoRequestResult My structs:

// GetCargoRequestResult ...
type GetCargoRequestResult struct {
    CargoRequest CargoRequest `db:""`
    Cargo        []*Cargo     `db:""`
}

type CargoRequest struct {
    ID             int64         `db:"cr.id"`
    RoutePlanID    int64         `db:"cr.route_plan_id"`
    GoodzonOrderID int64         `db:"cr.goodzon_order_id"`
    SellerID       sql.NullInt64 `db:"cr.seller_id"`
    Flow           FlowType      `db:"cr.flow"`
    Source         SourceType    `db:"cr.source"`
    MasterOrderID  sql.NullInt64 `db:"cr.master_order_id"`
    IsCanceled     bool          `db:"cr.is_canceled"`
    CreatedAt      time.Time     `db:"cr.created_at"`
    UpdatedAt      time.Time     `db:"cr.updated_at"`
}

type Cargo struct {
    CargoLozonID   int64 `db:"cargo_lozon_id"`
    GoodzonOrderID int64 `db:"goodzon_order_id"`
    DimensionType *DimensionType `db:"c.dimension_type"`
    IsCanceled    bool           `db:"c.is_canceled"`
    CargoType     CargoType      `db:"c.cargo_type"`
    CreatedAt     time.Time      `db:"c.created_at"`
}

[georgysavva]

Hi, and thanks for submitting this! I would need the error message from Scany to help you in full. But even without it, I can spot a few problems here:

1. It's counterintuitive, but when you select a column like this: select cr.goodzon_order_id, ... the result rows' column will be goodzon_order_id, not cr.goodzon_order_id as you might expect. You can verify that if you run your query in a GUI like DataGrip or TablePlus. To override this, you need to make an explicit alias in your query: select cr.goodzon_order_id as "cr.goodzon_order_id", ...
2. Scany won't handle your Cargo []Cargo struct array field. Scany isn't an ORM; it knows nothing about your data relations. You need to change that struct field to Cargo Cargo to match the shape of your rows. Then, after scanning the raw data, you can reduce it to an array, as was your initial intention.

I hope this helps.

[georgysavva]

Closing due to inactivity. Feel free to reopen/create a new issue.

[nobleach]

Then, after scanning the raw data, you can reduce it to an array, as was your initial intention. I'd be interested in seeing how this might be done. Any examples out there?

[georgysavva]

Hey @nobleach, I don't have any scany examples for that. But it's not something related to scany, actually. You just need to create a map where the key is your primary object id, and the value is the accumulated list of many related objects for that ID.

Here is an example code generated by ChatGPT for the standard Go database library. You can follow its example:

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/go-sql-driver/mysql"
)

type UserOrder struct {
    Username  string
    Orders    []Order
}

type Order struct {
    OrderId   int
    OrderDate string
}

func main() {
    db, err := sql.Open("mysql", "user:password@tcp(localhost:3306)/database")
    if err != nil {
        panic(err.Error())
    }
    defer db.Close()

    query := "SELECT u.username, o.order_id, o.order_date FROM users u LEFT JOIN orders o ON u.user_id = o.user_id"

    rows, err := db.Query(query)
    if err != nil {
        panic(err.Error())
    }
    defer rows.Close()

    userOrders := make(map[string]*UserOrder)

    for rows.Next() {
        var username string
        var orderId sql.NullInt64
        var orderDate sql.NullString

        err := rows.Scan(&username, &orderId, &orderDate)
        if err != nil {
            panic(err.Error())
        }

        userOrder, ok := userOrders[username]
        if !ok {
            userOrder = &UserOrder{
                Username: username,
                Orders:   []Order{},
            }
            userOrders[username] = userOrder
        }

        order := Order{
            OrderId:   int(orderId.Int64),
             OrderDate: orderDate.String,
        }
        userOrder.Orders = append(userOrder.Orders, order)

    }

    if err = rows.Err(); err != nil {
        panic(err.Error())
    }

    for _, userOrder := range userOrders {
        fmt.Printf("%s:\n", userOrder.Username)
        for _, order := range userOrder.Orders {
            fmt.Printf("  Order %d placed on %s\n", order.OrderId, order.OrderDate)
        }
    }
}

Some explanation from ChatGPT: In this example, we are using the MySQL driver to connect to a MySQL database. We define a struct called UserOrder to represent the results of our query. UserOrder has a slice of Orders to represent the one-to-many relationship between users and orders. The main function connects to the database, executes the query, scans the results into a map of usernames to UserOrder pointers, and prints out each result. We use a map to group the orders by username so that we can append new orders to the existing slice of orders for a given user.

I hope this helps. Let me know if it doesn't.

[nobleach]

I realize this isn't a responsibility of Scany, so I sincerely appreciate your response. Thank you.