Alias is broken or I'm doing something wrong

[Hetch3t]

Describe the bug
I'm not able to get alias tag to work.

Environment (please complete the following information):

• OS: Mac OSX
• Database: postgres
• Database driver: pgx
• Jet version 2.11.0

Code snippet
I have this models, which are one-to-one relationship if it matters:

RecordMetaModel.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

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

type Record struct {
	model.RecordMeta

	Test *struct {
		model.IncomeRecord

		ExtraField *string `json:"xf,omitempty"`
	} `alias:"test.*"`
}

And then I have this code:

var test = IncomeRecord.AS("test")

stmt := SELECT(
		RecordMeta.AllColumns.Except(RecordMeta.CreatedAt, RecordMeta.UpdatedAt, RecordMeta.UserID),
		test.AllColumns,
	).FROM(
		RecordMeta.LEFT_JOIN(test, test.RecordMetaID.EQ(RecordMeta.ID)),
	).WHERE(AND(
		RecordMeta.ID.EQ(UUID(id)),
		RecordMeta.UserID.EQ(UUID(userId)),
	)).LIMIT(1)

var dest = mdl.Record{}
err := stmt.Query(r.DB, &dest)

This is example of generated SQL query

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 always nil. I tried:

• having anonymous struct instead of named
• all the combinations of 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") to var test = IncomeRecord.AS("income_record") it starts working. So I assume the alias test in the Record.go file is completely ignored and the default one is used for fields in Test sub-struct, which is completely weird.

Expected behavior

Expect Test field to have IncomeRecord fields as well as ExtraField = nil using test alias

[houten11]

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"`
	} 
}

[Hetch3t]

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")

[houten11]

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.

[Hetch3t]

@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")

[houten11]

Aha, I see. Check my comment on this discussion - #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

[Hetch3t]

Yeah, that's exactly what I've been looking for. Thank you! 🔥

---

UPD

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)

[houten11]

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"`
}