Giter VIP home page Giter VIP logo

Comments (7)

houten11 avatar houten11 commented on August 11, 2024 1

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

from jet.

houten11 avatar houten11 commented on August 11, 2024

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

from jet.

Hetch3t avatar Hetch3t commented on August 11, 2024

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

from jet.

houten11 avatar houten11 commented on August 11, 2024

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.

from jet.

Hetch3t avatar Hetch3t commented on August 11, 2024

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

from jet.

Hetch3t avatar Hetch3t commented on August 11, 2024

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)

from jet.

houten11 avatar houten11 commented on August 11, 2024

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

from jet.

Related Issues (20)

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.