Mapping result of query with multiple joins

[edosssa]

// Query to get business branch and associated menus
query := SELECT(
		BusinessBranches.ID,
		ExternalMenus.AllColumns,
		Menus.AllColumns,
	).
	FROM(BusinessBranches.
		INNER_JOIN(ExternalMenus, ExternalMenus.BusinessBranchID.EQ(BusinessBranches.ID)).
		INNER_JOIN(Menus, Menus.BusinessBranchID.EQ(BusinessBranches.ID))).
	WHERE(BusinessBranches.ID.EQ(String(props.BranchID))).
	ORDER_BY(Menus.CreatedAt.DESC())

// This doesn't work correctly, it scans only the last row of each joined table
var result struct {
	ExternalMenus []External `alias:"external_menus"`
	Menus         []Dynamic  `alias:"menus"`
}

// Works when I add model.BusinessBranch
var result struct {
	model.BusinessBranch
	ExternalMenus []External `alias:"external_menus"`
	Menus         []Dynamic  `alias:"menus"`
}

This seems odd to me. I would expect the first query to work properly without having to include the model.BusinessBranch struct. Is this expected behavior or am I doing something wrong @go-jet ?

[go-jet]

Hi @edosssa,

Yeah, it is expected behaviour. You'll need to include at least BusinessBranch.ID so that qrm knows how to group the result.

var result struct {
	ID int64  `sql:"primary_key" alias:"business_branch.id"`

	ExternalMenus []External `alias:"external_menus"`
	Menus         []Dynamic  `alias:"menus"`
}