英文:
Struct embedding with sqlx not returning value from db
问题
我有一个包含时间字段的结构体，该字段可能为空：
type Order struct {
...
PickupTime *time.Time `json:"-"`
}
我想使用 sqlx 将其保存到数据库中，所以我想我需要使用 pq.NullTime，如此处所建议的 链接。
为了避免直接在模型层中泄漏数据库代码，我决定将 Order 嵌入到 PgOrder 中，并修改 PickupTime 的类型：
type PgOrder struct {
Order
PickupTime pq.NullTime
}
问题是，当我简单地更新数据库中的 Order，然后再获取该订单时，返回的 PickupTime 是空的。
// 更新
func (pg Postgres) UpdateOrderPickupTime(order *Order, pickupTime time.Time) error {
_, err := pg.Exec(`UPDATE orders SET pickup_time = $1 WHERE id = $2`, pickupTime, order.ID)
return err
}
// 检索
func (pg Postgres) GetOrder(orderID DatabaseID) (*Order, error) {
pgOrder := PgOrder{}
err := pg.Get(&pgOrder, `SELECT * FROM orders WHERE id = $1`, orderID)
if err == sql.ErrNoRows {
return nil, nil
}
... // 此时 pgOrder.PickupTime 为 0001-01-01 00:00:00 +0000 UTC
}
如果我在更新和检索之间设置断点，我可以检查数据库并看到一个值被保存为 2017-04-20 12:05:37-04。所以问题必须出现在检索部分。如果我从 文档 正确理解的话，sqlx 应该能够处理嵌入的结构体。
英文:
I have a struct with a time field that may be nil:
type Order struct {
...
PickupTime *time.Time `json:"-"`
}
I want to save this to DB with sqlx so I figure I need to use pq.NullTime as suggested here.
Instead of updating the Order object (I don't want to leak DB code into the model layer), I figured I could embedd Order in a PgOrder and alter the PickupTime type:
type PgOrder struct {
Order
PickupTime pq.NullTime
}
The problem is that when I simply update an Order in DB, then turn around and fetch that order, the returned PickupTime is empty.
// update
func (pg Postgres) UpdateOrderPickupTime(order *Order, pickupTime time.Time) error {
_, err := pg.Exec(`UPDATE orders SET pickup_time = $1 WHERE id = $2`, pickupTime, order.ID)
return err
}
// retrieve
func (pg Postgres) GetOrder(orderID DatabaseID) (*Order, error) {
pgOrder := PgOrder{}
err := pg.Get(&pgOrder, `SELECT * FROM orders WHERE id = $1`, orderID)
if err == sql.ErrNoRows {
return nil, nil
}
... // at this point pgOrder.PickupTime is 0001-01-01 00:00:00 +0000 UTC
}
If I put a breakpoint betwen updating and retrieving, I can inspect the DB and see that a value is being saved as 2017-04-20 12:05:37-04. So the problem must be in the retrieve portion. If I understand right from the docs, sqlx should be able to handle embedded structs.
答案1
得分: 2
看起来你正在遮蔽PickupTime。如果我正确理解sqlx文档，这意味着它会将值存储在第一个找到的位置（在Order中），然后当你读取PgOrder中的值时，它是一个未初始化的time.Time。你可以检查PgOrder.PickupTime的Valid字段来确认这一点（它应该是无效的）。
英文:
It looks like you're shadowing PickupTime. If I'm reading the sqlx docs right that means it will store the value in the first one it found (in Order) and then when you read the one in PgOrder it's an uninitialized time.Time. You can check the Valid field of PgOrder.PickupTime to confirm this (it should be invalid).
答案2
得分: 1
如果你的字段是指向某个东西的指针，例如 *time.Time 或 *string，你不需要使用 NullXxx 类型。当你有一个非空字段，例如 time.Time 或 string，而对应的列可以是 NULL 时，应该使用这些类型。
如果你仍然想嵌入你的类型，以避免 @Dmitri Goldring 提到的潜在遮蔽问题，你可以告诉 sqlx 跳过你不想它扫描到的列。就像你在 json 标签中所做的那样，你可以在 db 标签中这样做：
type Order struct {
...
PickupTime *time.Time `json:"-" db:"-"`
}
type PgOrder struct {
Order
PickupTime pq.NullTime
}
英文:
If your field is a pointer to something, e.g. *time.Time, or *string you shouldn't need to use the NullXxx types. Those types are to be used when you have a non-nil field, e.g. time.Time, or string while it's corresponding column can be NULL.
If you want to ebmed your type anyway, to avoid potential shadowing already mentioned by @Dmitri Goldring, you can to tell sqlx to skip the field you don't want it to scan the column into. So just as you did with the json tag, you can do so with the db tag:
type Order struct {
...
PickupTime *time.Time `json:"-" db:"-"`
}
type PgOrder struct {
Order
PickupTime pq.NullTime
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论