Error `has-many relation ... check join conditions` on select with relation via *string

[evt]

trafficstars

Error: *errors.errorString: bun: has-many relation=Books does not have base model=User with id=[%!q(*string=0xc00019efe0)] (check join conditions)

Works fine in go-pg.

If UserID *string is replaced with any not nullable type, it works fine.

Test err example:

package main

import (
	"context"
	"database/sql"
	"fmt"

	"github.com/uptrace/bun"
	"github.com/uptrace/bun/dialect/sqlitedialect"
	"github.com/uptrace/bun/driver/sqliteshim"
	"github.com/uptrace/bun/extra/bundebug"
)

type User struct {
	ID    string  `bun:",pk"`
	Books []*Book `bun:"rel:has-many,join:id=user_id"`
}

type Book struct {
	ID     int64   `bun:",pk,autoincrement"`
	UserID *string `bun:"type:varchar(64)"` // No err if UserID is `string`
}

func main() {
	ctx := context.Background()

	sqldb, err := sql.Open(sqliteshim.ShimName, "file::memory:?cache=shared")
	if err != nil {
		panic(err)
	}

	fmt.Println("Setting up DB connection...")
	db := bun.NewDB(sqldb, sqlitedialect.New())
	defer db.Close()
	db.AddQueryHook(bundebug.NewQueryHook(bundebug.WithVerbose(true)))

	fmt.Println("Creating tables...")
	if err := createSchema(ctx, db); err != nil {
		panic(err)
	}

	fmt.Println("Getting object from DB:")
	user := new(User)
	if err := db.NewSelect().
		Model(user).
		Column("user.*").
		Relation("Books").
		OrderExpr("user.id ASC").
		Limit(1).
		Scan(ctx); err != nil {
		panic(err)
	}
	fmt.Println(user.ID, user.Books[0], user.Books[1])

	fmt.Println("Test completed successfully.")
}

func createSchema(ctx context.Context, db *bun.DB) error {
	_, err := db.Exec(`
   CREATE TABLE users
   (
      id   varchar(64) PRIMARY KEY
   );

   CREATE TABLE books
   (
      id      int PRIMARY KEY,
      user_id varchar(64)
   );
`)
	if err != nil {
		return fmt.Errorf("failed to create tables: %w", err)
	}

	userIDA := "1"
	userIDB := "2"

	testUsers := []*User{
		{ID: userIDA},
		{ID: userIDB},
	}
	if _, err := db.NewInsert().Model(&testUsers).Exec(ctx); err != nil {
		return err
	}

	book := []*Book{
		{ID: 1, UserID: &userIDA},
		{ID: 2, UserID: &userIDA},
		{ID: 3, UserID: &userIDB},
	}
	if _, err := db.NewInsert().Model(&book).Exec(ctx); err != nil {
		return err
	}

	return nil
}

[mannyOE]

@evt can you or anyone help me take a look real quick?

type Event struct { bun.BaseModel bun:"table:events"`

Id                             string `bun:"id,pk,notnull,unique"`
UserId                     string `bun:"user_id,notnull"`
User		       *User  `bun:"rel:belongs-to,join:user_id=id"`	

}

type User struct { bun.BaseModel bun:"table:users"

Id                string   `bun:"id,pk,notnull,unique"`
Username          string   `bun:"username,notnull,nullzero,unique"`	

}`

But the join operation keeps returning 

`ERROR: column reference "id" is ambiguous (SQLSTATE=42702)`

I've been stressing about it for a while.

[Egbert-Jan]

I have the same problem but with *int. Would expect it work so you can have optional/nullable foreign keys or should this be done in another way?

[mannyOE]

I have found a fix for this. The issue wasnt with the model, but with the way we are calling the relations

I initially had something like err := da.db.NewSelect().Model(&conversations).Relation("LastMessage").Relation("Member").Relation("Creator").WhereGroup(" AND ", func(q *bun.SelectQuery) *bun.SelectQuery { return q.Where("member_id = ?", req.UserId).WhereOr("creator_id = ?", req.UserId) }).

This kept failing until I switched to using dot notation in the Where queries

err := da.db.NewSelect().Model(&conversations).Relation("LastMessage").Relation("Member").Relation("Creator").WhereGroup(" AND ", func(q *bun.SelectQuery) *bun.SelectQuery { return q.Where("conversation.member_id = ?", req.UserId).WhereOr("conversation.creator_id = ?", req.UserId) }).

[yansal]

We have the same problem with sql.Null* types.

This is the code that make it work in go-pg :

https://github.com/go-pg/pg/blob/v10.10.7/orm/model_table_many.go#L50-L51

There is a map from foreign key values to loaded children models. In go-pg, this map is named manyModel.dstValues and has the type map[string][]reflect.Value. When the map is written and read, we always "dereference" values. So for example we can set keys of type int64, and query with keys of type *int64, and we can find the children models that were loaded.

This is the same code that doesn't work in bun :

https://github.com/uptrace/bun/blob/v1.1.8/model_table_has_many.go#L106

The structure is similar, but the map is named hasManyModel.baseValues and the type is map[internal.MapKey][]reflect.Value, with

type MapKey struct {
	iface interface{}
}

The problem is that the map key is no longer a string, but it is an internal type that is related to types of foreign keys. If the map is set with keys of type int64, we can no longer query it with keys of type *int64.

Questions for @vmihailenco :

1. What is the reasoning behind this change between go-pg and bun ?
2. Would you accept a PR reverting back bun to the go-pg behavior ?

(We need this issue to be fixed to consider bun for our project.)

Thanks.

[mdncv]

I got two models:

type Region struct {
	bun.BaseModel   `bun:"table:regions"`
	ID              *uuid.UUID      `json:"id" bun:"id,pk,type:uuid,default:gen_random_uuid()"`
	Organizations   []*Organization `bun:"rel:has-many,join:id=region_id"`
}

type Organization struct {
	bun.BaseModel        `bun:"table:organizations"`
	ID                   *uint64          `json:"id" bun:"id,pk,autoincrement"`
	RegionID             *uuid.UUID       `json:"region_id" bun:"region_id"`
}

I use them like this:

var items []Region
err := db.NewSelect().Model(&items).Relation("Organizations").Scan(ctx)

And gain this error:

bun: has-many relation=Organizations does not have base model=Region with id=["7436cc71-2b6d-4eb9-a04e-b7b2f45a0db5"] (check join conditions)

But if I make Region.ID and Organization.RegionID non-pointers - everything works fine. I consider there is something wrong with reflection in https://github.com/uptrace/bun/blob/master/model_table_has_many.go

[lazharichir]

Exact same issue for me.

I join on a *string rather than string, and it errors. I cannot use a string here, so I have to figure out a workaround.

type Individual struct {
	bun.BaseModel  `bun:"individuals,alias:ind"`
	ID             string             `json:"ID" required:"true" bun:",pk"`
	WorkspaceID    string             `json:"WorkspaceID" required:"true"`
	HubspotObjects []*HubspotObject   `json:"HubspotObjects" bun:"rel:has-many,join:id=individual_id"`
}

type HubspotObject struct {
	bun.BaseModel  `bun:"hubspot_objects,alias:hso"`
	ID             string          `json:"ID" required:"true" bun:",pk"`
	WorkspaceID    string          `json:"WorkspaceID" required:"true"`
	IndividualID   *string         `json:"IndividualID"` // can be null
	OrganizationID *string         `json:"OrganizationID"` // can be null
	SyncedAt       *time.Time      `json:"SyncedAt"`
	UpdatedAt      time.Time       `json:"UpdatedAt" required:"true"`
	CreatedAt      time.Time       `json:"CreatedAt" required:"true"`
}

func (h *Handlers) SearchIndividuals() {
	ids := []string{"a", "z"}

	// load the individuals
	individuals := []entity.Individual{}
	if err := h.bun.NewSelect().
		Model(&individuals).
		Relation("HubspotObjects").
		Where("ind.id IN (?)", bun.In(ids)).
		Scan(ctx); err != nil {
		return nil, apperrors.SqlError(err, "search individuals")
	}
}

And it errors: errors.errorString: bun: has-many relation=HubspotObjects does not have base model=Individual with id=[%!q(*string=0x1400003d2b0)] (check join conditions)

@vmihailenco any thoughts on how we can make it work?