Open Hetch3t opened 7 months ago
Since you aliased IncomeRecord
you need to alias model.IncomeRecord
field, not enclosing field.
type Record struct {
model.RecordMeta
Test *struct {
model.IncomeRecord `alias:"test.*"`
ExtraField *string `json:"xf,omitempty"`
}
}
Oh, okay. It's working now, thank you @houten11!
But do I get it right that it's not possible to "extend" model for scanning? E.g. I can't do something lile this:
type IncomeRecord struct {
model.IncomeRecord
Counterparty *model.Counterparty `json:"cp,omitempty" alias:"income_record.counterparty"`
Category *model.Category `json:"cg,omitempty" alias:"income_record.category"`
}
type Record struct {
model.RecordMeta
*IncomeRecord `alias: "income_record"`
}
...
var incomeRecordCounterparty = Counterparty.AS("income_record.counterparty")
I can only do this:
type IncomeRecord struct {
model.IncomeRecord // implicitly has alias: "income_record.*"
Counterparty *model.Counterparty `json:"cp,omitempty" alias:"income_record_counterparty"` // notice no dot - not a child of `income_record`
Category *model.Category `json:"cg,omitempty" alias:"income_record_category"` // notice no dot - not a child of `income_record`
}
type Record struct {
model.RecordMeta
*IncomeRecord
}
...
var incomeRecordCounterparty = Counterparty.AS("income_record_counterparty")
I'm not sure what you mean by "extend". The scan rule is simple, as long as the alias corresponds to model type name
.field name
scanning will work, regardless of the position of the type in your destination struct. If you mean, is it possible to specify the type path in your destination struct, no that's not possible.
If you are using generated model types, you would rarely need to alias anything. The only situation I can think of right now is when you need to join the same table twice(like self-join).
In your case, if you get rid of all aliases scanning should work.
@houten11 By extend I mean to have another named struct type, that has all the fields of models (via embedding the model struct) + some extras, e.g. joined models. Something like:
type IncomeRecord struct {
model.IncomeRecord
Counterparty *model.Counterparty `json:"cp,omitempty"`
Category *model.Category `json:"cg,omitempty"`
}
type ExpenseRecord struct {
model.ExpenseRecord
Counterparty *model.Counterparty `json:"cp,omitempty"`
Category *model.Category `json:"cg,omitempty"`
}
type Record struct {
model.RecordMeta
*IncomeRecord `alias: "income_record"`
*ExpenseRecord `alias: "expense_record"`
}
...
var incomeRecordCounterparty = Counterparty.AS("income_record.counterparty")
var expenseRecordCounterparty = Counterparty.AS("expense_record.counterparty")
So both Counterparty aliasing work. Currently I have the following working code:
type IncomeRecord struct {
model.IncomeRecord
Counterparty *model.Counterparty `json:"cp,omitempty" alias:"income_record_counterparty"`
Category *model.Category `json:"cg,omitempty"`
}
type ExpenseRecord struct {
model.ExpenseRecord
Counterparty *model.Counterparty `json:"cp,omitempty" alias:"expense_record_counterparty"`
Category *model.Category `json:"cg,omitempty"`
}
type Record struct {
model.RecordMeta
*IncomeRecord
*ExpenseRecord
}
...
var incomeRecordCounterparty = Counterparty.AS("income_record_counterparty")
var expenseRecordCounterparty = Counterparty.AS("expense_record_counterparty")
It's not clear to me how to have extended models multiple times. Copy over anonymous inline struct each time? What I mean is, imaging I have model.Counterparty
and ExtendedCounterparty
- previous example immediately stops working:
type ExtendedCounterparty {
model.Counterparty
}
type IncomeRecord struct {
model.IncomeRecord
Counterparty *ExtendedCounterparty `json:"cp,omitempty" alias:"income_record_counterparty"`
Category *model.Category `json:"cg,omitempty"`
}
type ExpenseRecord struct {
model.ExpenseRecord
Counterparty *ExtendedCounterparty `json:"cp,omitempty" alias:"expense_record_counterparty"`
Category *model.Category `json:"cg,omitempty"`
}
type Record struct {
model.RecordMeta
*IncomeRecord
*ExpenseRecord
}
...
var incomeRecordCounterparty = Counterparty.AS("income_record_counterparty")
var expenseRecordCounterparty = Counterparty.AS("expense_record_counterparty")
Aha, I see. Check my comment on this discussion - https://github.com/go-jet/jet/discussions/192. Seems related.
Basically at the moment, the only way to do it, in one query, is to have two Counterparty
structs.
type IncomeRecordCounterparty {
model.Counterparty `alias:"income_record_counterparty"`
}
type ExpenseRecordCounterparty {
model.Counterparty `alias:"expense_record_counterparty"`
}
Alternatively, you can do it in two queries, without model structs wrapping:
var record Record
stmt := SELECT(...).JOIN(RecordMeta, IncomeRecord, Category, Counterparty)
err := stmt.Query(db, &record) // this will fill model.RecordMeta and *IncomeRecord
stmt := SELECT(...).JOIN(IncomeRecord, Category, Counterparty)
err := stmt.Query(db, record.ExpenseRecord) // this will fill just *ExpenseRecord
Yeah, that's exactly what I've been looking for. Thank you! 🔥
_The issue is cause there is no sql:"primary_key"
tag for Record.ID
field. However, it's not clear how to adjust it since views can't have primary keys in PostgreSQL. Should I manually add sql:"primary_key"
? Or is there any recommended way?_
Maybe you can help me with question, related to joining tables - I caught weird behaviour, which I struggle to explain. I modified my app structure a bit, so now I have Record view and Counterparty table in my database (PostreSQL):
// Record view
type Record struct {
ID uuid.UUID `json:"id,omitempty"`
Note string `json:"nt,omitempty"`
AttachmentUrls tpn.StringArray `json:"atchs,omitempty"`
IsHidden bool `json:"ishd,omitempty"`
UserID *uuid.UUID `json:"-"`
CreatedAt *time.Time `json:"crtd,omitempty"`
UpdatedAt *time.Time `json:"uptd,omitempty"`
ExecutedAt *time.Time `json:"xqtd,omitempty"`
Type string `json:"t,omitempty"`
AmountSentWithBonuses *decimal.Decimal `json:"amsb,omitempty"`
CategoryID *uuid.UUID `json:"cgid,omitempty"`
CounterpartyID *uuid.UUID `json:"cpid,omitempty"`
AmountSent *decimal.Decimal `json:"ams,omitempty"`
AmountReceived *decimal.Decimal `json:"amr,omitempty"`
AccountFromID *uuid.UUID `json:"acfid,omitempty"`
AccountToID *uuid.UUID `json:"actid,omitempty"`
}
// Counterparty table
type Counterparty struct {
ID uuid.UUID `sql:"primary_key" json:"id,omitempty"`
Name string `json:"nm,omitempty"`
Color string `json:"cl,omitempty"`
Icon string `json:"ic,omitempty"`
Aliases tpn.StringArray `json:"als,omitempty"`
Logo string `json:"lg,omitempty"`
Note string `json:"nt,omitempty"`
UserID uuid.UUID `json:"-"`
CreatedAt *time.Time `json:"crtd,omitempty"`
UpdatedAt *time.Time `json:"uptd,omitempty"`
}
And I have the following destination struct:
type Record struct {
model.Record
Category *model.Category `json:"cg,omitempty"`
Counterparty *model.Counterparty `json:"cp,omitempty"`
}
So the weird behaviour is the following. This statement returns 1000 records, everything's perfect:
stmt := SELECT(Record.AllColumns).
FROM(Record).
WHERE(Record.UserID.EQ(UUID(userID))).
ORDER_BY(Record.ExecutedAt.DESC()).
LIMIT(1000)
But this statement (notice JOIN) returns only 8 records (8 records are always the same, but I wasn't able to find out what determines why these 8 records):
stmt := SELECT(Record.AllColumns, Counterparty.AllColumns).
FROM(Record.
LEFT_JOIN(Counterparty, Record.CounterpartyID.EQ(Counterparty.ID))).
WHERE(Record.UserID.EQ(UUID(userID))).
ORDER_BY(Record.ExecutedAt.DESC()).
LIMIT(1000)
The generated SQL query is below and it is perfectly fine - I've tested it and in psql the returned rows are correct. So the issue is not within query generator but within scanner to struct:
SELECT record.id AS "record.id",
record.note AS "record.note",
record.attachment_urls AS "record.attachment_urls",
record.is_hidden AS "record.is_hidden",
record.user_id AS "record.user_id",
record.created_at AS "record.created_at",
record.updated_at AS "record.updated_at",
record.executed_at AS "record.executed_at",
record.type AS "record.type",
record.amount_sent_with_bonuses AS "record.amount_sent_with_bonuses",
record.category_id AS "record.category_id",
record.counterparty_id AS "record.counterparty_id",
record.amount_sent AS "record.amount_sent",
record.amount_received AS "record.amount_received",
record.account_from_id AS "record.account_from_id",
record.account_to_id AS "record.account_to_id",
counterparty.id AS "counterparty.id",
counterparty.name AS "counterparty.name",
counterparty.color AS "counterparty.color",
counterparty.icon AS "counterparty.icon",
counterparty.aliases AS "counterparty.aliases",
counterparty.logo AS "counterparty.logo",
counterparty.note AS "counterparty.note",
counterparty.user_id AS "counterparty.user_id",
counterparty.created_at AS "counterparty.created_at",
counterparty.updated_at AS "counterparty.updated_at"
FROM public.record
LEFT JOIN public.counterparty ON (record.counterparty_id = counterparty.id)
WHERE record.user_id = '00000000-0000-0000-0000-000000000001'
ORDER BY record.executed_at DESC
LIMIT 1000;
However, as soon as I add Except
to Counterparty
's columns it starts to work as expected!
stmt := SELECT(Record.AllColumns, Counterparty.AllColumns.Except(Counterparty.ID)).
FROM(Record.
LEFT_JOIN(Counterparty, Record.CounterpartyID.EQ(Counterparty.ID))).
WHERE(Record.UserID.EQ(UUID(userID))).
ORDER_BY(Record.ExecutedAt.DESC()).
LIMIT(1000)
Yeah, you need to specify primary key on Record
struct. You can do it in two ways.
You can customize generator to add a primary key tag to the model type.
Or you can specify a primary key:
type Record struct {
model.Record `sql:"primary_key=ID"`
Category *model.Category `json:"cg,omitempty"`
Counterparty *model.Counterparty `json:"cp,omitempty"`
}
Describe the bug I'm not able to get
alias
tag to work.Environment (please complete the following information):
Code snippet I have this models, which are one-to-one relationship if it matters:
RecordMetaModel.go
```go type RecordMeta struct { ID uuid.UUID `sql:"primary_key" json:"id,omitempty"` Note string `json:"nt,omitempty"` AttachmentUrls tpn.StringArray `json:"atchs,omitempty"` IsHidden bool `json:"ishd,omitempty"` UserID uuid.UUID `json:"-"` CreatedAt *time.Time `json:"crtd,omitempty"` UpdatedAt *time.Time `json:"uptd,omitempty"` ExecutedAt time.Time `json:"xqtd,omitempty"` } ```IncomeRecordModel.go
```go type IncomeRecord struct { AmountReceived decimal.Decimal `json:"amr,omitempty"` CounterpartyID *uuid.UUID `json:"cpid,omitempty"` RecordMetaID uuid.UUID `sql:"primary_key" json:"-"` CategoryID *uuid.UUID `json:"cgid,omitempty"` AccountToID *uuid.UUID `json:"actid,omitempty"` } ```And this expected resulting struct:
Record.go
```go type Record struct { model.RecordMeta Test *struct { model.IncomeRecord ExtraField *string `json:"xf,omitempty"` } `alias:"test.*"` } ```And then I have this code:
This is example of generated SQL query
```sql SELECT record_meta.id AS "record_meta.id", record_meta.note AS "record_meta.note", record_meta.attachment_urls AS "record_meta.attachment_urls", record_meta.is_hidden AS "record_meta.is_hidden", record_meta.executed_at AS "record_meta.executed_at", test.amount_received AS "test.amount_received", test.counterparty_id AS "test.counterparty_id", test.record_meta_id AS "test.record_meta_id", test.category_id AS "test.category_id", test.account_to_id AS "test.account_to_id" FROM public.record_meta LEFT JOIN public.income_record AS test ON (test.record_meta_id = record_meta.id) WHERE ( (record_meta.id = '018e05ee-4db7-7268-a6e4-bc0ee98e7b8c') AND (record_meta.user_id = '00000000-0000-0000-0000-000000000001') ) LIMIT 1; ```And after running this
Test
is alwaysnil
. I tried:test.*
/test
/record.test
/record.test.*
etc.No result, though the record is connected to
IncomeRecord
(it shouldn't be empty)HOWEVER, if I modify
var test = IncomeRecord.AS("test")
tovar test = IncomeRecord.AS("income_record")
it starts working. So I assume the aliastest
in theRecord.go
file is completely ignored and the default one is used for fields inTest
sub-struct, which is completely weird.Expected behavior
Expect
Test
field to haveIncomeRecord
fields as well asExtraField = nil
usingtest
alias