QRM and "wrapped" complex types (structs and slices) | Custom scanner for complex types?

[josephbuchma]

We are using ogen OpenAPI code generator, and it generates types like

type OptBillOfMaterialHdr struct {
	Value BillOfMaterialHdr
	Set   bool
}

where BillOfMaterialHdr is another struct. So when you have an optional BillOfMaterialHdr in request or response payload, it will use OptBillOfMaterialHdr (as opposed to more common approach of *BillOfMaterialHdr with omitempty json tag option).

In essense, I'd like to be able to store query results in a struct like this:

var queryResult struct{
   Bom OptBillOfMaterialHdr
}

And when Bom.Value is populated, it should also set Bom.Set to true.

As far as I can tell, it is not possible to implement something like sql.Scanner for complex types at the moment, isn't it?
I'd love to contribute a PR for this feature, and would appreciate any guidance on how to get this done.

[go-jet]

QRM is the most complex part of the library and I wouldn't like to add additional features unless they are really necessary. I can't give you any guidance right now, because I'd need more time to think about what this change will require.
As a workaround you can add another column to your query:

SELECT(
    ...
    BillOfMaterialHdr.ID.IS_NOT_NULL.AS("set"),
)

Now if BillOfMaterialHdr.ID(or some other mandatory column) exist Set field will be set to true.

[josephbuchma]

Wow, this is great actually. I'm still adjusting to the idea that jet does not care where destination struct is located in the target data structure.

I made a thin wrapper around ProjectionList that allows to write queries like this:

	var dest api.ManufacturingRun

	mr := ftable.ManufacturingRun.AS("mr")
	bomHdr := ctable.BillOfMaterialHdr.AS("bom")

	q := mysql.SELECT(
		jetutil.ProjectionListFor(dest).AddColumns(
			mr.ManufacturingRunID,
			mr.Name,
		),
		// NOTE: `dest.BomHdr` is `api.OptBillOfMaterialHdr`
		jetutil.ProjectionListFor(dest.BomHdr).AddColumns(
			bomHdr.AllColumns,
		),
	).FROM(
		mr.LEFT_JOIN(bomHdr, bomHdr.BomHdrPk.EQ(mr.BomHdrPk)),
	)

ProjectionListFor takes any value as argument and will use type name of the value as table alias (i.e. will pass it to ProjectionList.As call). I like this API because it allows to be upfront and explicit about results mapping, and makes it less error prone to work with custom types.

This wrapper also allowed me to add custom logic to handle Opt* and Nil* types mapping behind the scenes. Not a universal solution, but works great in my particular case, here's the code for anyone interested:

package jetutil

import (
	"fmt"
	"database/sql"
	"strings"

	"github.com/go-jet/jet/v2/mysql"
)

// AliasedProjectionListBuilder is a helper for building aliased mysql.ProjectionList{},
// and contains logic that allows to use `api.Opt*` and `api.Nil*` structs as query destinations.
type AliasedProjectionListBuilder struct {
	alias string

	// apiOptNil is set to "Opt" or "Nil" if destination type is api.Opt* or api.Nil*
	apiOptNil apiOptNil

	mysql.ProjectionList
}

type apiOptNil string

const (
	apiOpt apiOptNil = "Opt"
	apiNil apiOptNil = "Nil"
)

// ProjectionListFor returns new AliasedProjectionListBuilder for given query result destination.
// DestinationAlias(dest) is used as table alias for all selects added using `Select` method.
func ProjectionListFor(dest any) *AliasedProjectionListBuilder {
	alias, isApiPkg := typeName(dest)
	b := &AliasedProjectionListBuilder{
		alias: alias,
	}

	if isApiPkg && !isSqlScanner(dest) {
		// Handle api.Opt* types
		if strings.HasPrefix(b.alias, string(apiOpt)) {
			b.apiOptNil = apiOpt
			b.alias = strings.TrimLeft(b.alias, string(apiOpt))
		}

		// Handle api.Nil* types
		if strings.HasPrefix(b.alias, string(apiNil)) {
			b.apiOptNil = apiNil
			b.alias = strings.TrimLeft(b.alias, string(apiNil))
		}
	}

	return b
}

func (plb AliasedProjectionListBuilder) isApiOptNil() bool {
	return plb.apiOptNil != ""
}

// AddColumns appends to ProjectionList with `.As(DestinationAlias(dest))`.
// Mutates ProjectionListBuilder.
func (plb *AliasedProjectionListBuilder) AddColumns(cols ...mysql.Projection) *AliasedProjectionListBuilder {
	if plb.isApiOptNil() && len(plb.ProjectionList) == 0 {
		plb.handleApiOptNil(cols)
	}

	plb.ProjectionList = append(plb.ProjectionList, mysql.ProjectionList(cols).As(plb.alias))
	return plb
}

func (plb *AliasedProjectionListBuilder) handleApiOptNil(cols []mysql.Projection) {
	exprs := make([]mysql.Expression, 0, len(cols))
	for _, c := range cols {
		switch cc := c.(type) {
		case mysql.Column:
			exprs = append(exprs, cc)
		case mysql.ColumnList:
			for _, ccc := range cc {
				exprs = append(exprs, ccc)
			}
		}
	}
	if len(exprs) == 0 {
		return
	}
	coalesce := mysql.COALESCE(exprs[0], exprs[1:]...)
	switch plb.apiOptNil {
	case apiOpt:
		plb.ProjectionList = append(plb.ProjectionList, coalesce.IS_NOT_NULL().AS("Opt"+plb.alias+".Set"))
	case apiNil:
		plb.ProjectionList = append(plb.ProjectionList, coalesce.IS_NULL().AS("Nil"+plb.alias+".Null"))
	}
}

func isSqlScanner(v any) bool {
	_, ok := v.(sql.Scanner)
	return ok
}

// typeName returns name of a type without any prefixes (namespace prefix, `*` prefix, `[]` prefix...),
// or it returns actual string value if typ is a string.
//
//	typeName(model.User{}) // "User"
//	typeName(&model.User{}) // "User"
//	typeName([]model.User{}) // "User"
//	typeName(&[]model.User{}) // "User"
func typeName(v any) (typ string, isApiPkg bool) {
	n := fmt.Sprintf("%T", v)
	if n == "string" {
		return v.(string), false
	}
	isApiPkg = strings.Contains(n, "api.")
	for i, r := range n {
		if r == '.' {
			return n[i+1:], isApiPkg
		}
	}
	return n, isApiPkg
}