Nested Relations

[comsma]

I'm trying to build a query that goes two objects deep using

s.DB.NewSelect().Model(report).
		Relation("InventoryReceiptItems").
		Relation("InventoryReceiptItems.OrderTransactions ").
		Where("receipt_number = ?", request.GetReceiptNumber()).
		Scan(ctx)

I am able to get the results for the InventoryReceiptItems but I can't get the nested OrderTransactions from my InventoryReceiptLine result. I'm expecting my result to look like

{
    "items": [
        {
            "order_transactions": [
               {
                  "document_no": 12345,
                 " qty_allocated": 3
               },
               {
                  "document_no": 22345,
                  "qty_allocated": 4
               },
            ],
            "quantity": 44,
            "unit_of_measure": "EA",
            "inv_mast_id": 24811,
            "inventory_master": null
        },
        {
            "order_transactions": [
               {
                  "document_no": 12345,
                  "qty_allocated": 8
               },
               {
                  "document_no": 22345,
                  "qty_allocated": 1
               },
            ],
            "quantity": 21,
            "unit_of_measure": "EA",
            "inv_mast_id": 24823,
            "inventory_master": null
        }
    ],
    "receipt_number": 5000012
}

Would I be better off running an addition query for each element of the array to get the nested order_transactions? or can bun do nested relations?

InventoryReceipt Model

type InventoryReceipt struct {
	bun.BaseModel `bun:"table:inventory_receipts_hdr"`

	ReceiptNumber float64 `bun:"receipt_number,pk"`

	InventoryReceiptItems []InventoryReceiptLine `bun:"rel:has-many,join:receipt_number=receipt_number"`
}

InventoryReceiptLine Model

type InventoryReceiptLine struct {
	bun.BaseModel `bun:"table:inventory_receipts_line"`

	ReceiptNumber float64 `bun:"receipt_number,pk"`
	LineNumber    int32   `bun:"line_number,pk"`
	InvMastUid    int32   `bun:"inv_mast_uid"`
	UnitQuantity  float64 `bun:"unit_quantity"`
	UnitOfMeasure string  `bun:"unit_of_measure"`

	OrderTransactions []InvTran `bun:"rel:has-many,join:receipt_number=sub_document_no,join_on:inv_mast_uid=inv_mast_uid"`
	InvMast           InvMast   `bun:"rel:has-one,join:inv_mast_uid=inv_mast_uid"`
}

InvTran Model

type InvTran struct {
	bun.BaseModel `bun:"table:inv_tran"`

	TransactionNumber float64 `bun:"transaction_number,pk"`
	SubDocumentNo     float64 `bun:"sub_document_no"`
	InvMastUid        int     `bun:"inv_mast_uid"`
	QtyAllocated      float64 `bun:"qty_allocated"`
	DocumentNo        float64 `bun:"document_no"`
}

[trippleflp]

Would I be better off running an addition query for each element of the array to get the nested order_transactions? or can bun do nested relations?

Bun is definitely capable of nested relations. At least it worked for me.
Simple example:

type FunctionGroupToUserRolePair struct {
	Id              int64 `bun:"id,pk,autoincrement"`
	Role            model.UserRole
	UserId          string         `bun:"type:uuid"`
	FunctionGroup   *FunctionGroup `bun:"rel:belongs-to,join:function_group_id=id"`
	User            *User          `bun:"rel:belongs-to,join:user_id=id"`
	FunctionGroupId string         `bun:"type:uuid"`
}

type User struct {
	Id             string                         `bun:"type:uuid,pk"`
	FunctionGroups []*FunctionGroupToUserRolePair `bun:"rel:has-many"`
}

type FunctionGroup struct {
	Id          string    `bun:"type:uuid,pk"`
	Name        string    `bun:",unique"`
	functionIds []*string `bun:",array"`
	Users []*FunctionGroupToUserRolePair `bun:"rel:has-many"`
}

And the query:

user := new(User)
err = db.NewSelect().
        Model(user).
        Relation("FunctionGroups").
        Relation("FunctionGroups.FunctionGroup").
        Limit(1).
        Scan(context.Background())