Join and return multiple models issues #1116
Assignees
Comments
|
Can I understand it as: ModelA corresponds to zero or more ModelB? Case with Postgresql: package main
import (
"context"
"database/sql"
"fmt"
"github.com/uptrace/bun"
"github.com/uptrace/bun/dialect/pgdialect"
"github.com/uptrace/bun/driver/pgdriver"
"github.com/uptrace/bun/extra/bundebug"
)
type Profile struct {
ID string `bun:",pk"`
}
type User struct {
Name string `bun:",pk"`
ProfileID string
Profile *Profile `bun:"rel:belongs-to,join:profile_id=id"`
}
func main() {
ctx := context.Background()
sqldb := sql.OpenDB(pgdriver.NewConnector(pgdriver.WithDSN("postgres://bun:bun@j2gg0s@localhost:5432/test?sslmode=disable")))
db := bun.NewDB(sqldb, pgdialect.New())
db.AddQueryHook(bundebug.NewQueryHook(
bundebug.WithVerbose(true),
bundebug.FromEnv("BUNDEBUG"),
))
err := db.ResetModel(ctx, (*Profile)(nil), (*User)(nil))
if err != nil {
panic(err)
}
if _, err := db.NewInsert().Model(&Profile{ID: "p1"}).Exec(ctx); err != nil {
panic(err)
}
user := User{
Name: "foo",
ProfileID: "p1",
// PetsAsText: []*Pet{{Name: "luna"}},
}
if _, err := db.NewInsert().Model(&user).Exec(ctx); err != nil {
panic(err)
}
users := []User{}
if err := db.NewSelect().
Model((*User)(nil)).
Relation("Profile").
Scan(ctx, &users); err != nil {
panic(err)
}
fmt.Println(users[0].ProfileID, users[0].Profile)
}Log: [bun] 15:24:05.135 DROP TABLE 1.059248s DROP TABLE IF EXISTS "profiles" CASCADE
[bun] 15:24:05.154 CREATE TABLE 17.888ms CREATE TABLE "profiles" ("id" VARCHAR NOT NULL, PRIMARY KEY ("id"))
[bun] 15:24:05.166 DROP TABLE 11.083ms DROP TABLE IF EXISTS "users" CASCADE
[bun] 15:24:05.180 CREATE TABLE 14.125ms CREATE TABLE "users" ("name" VARCHAR NOT NULL, "profile_id" VARCHAR, PRIMARY KEY ("name"))
[bun] 15:24:05.191 INSERT 10.965ms INSERT INTO "profiles" ("id") VALUES ('p1')
[bun] 15:24:05.201 INSERT 10.067ms INSERT INTO "users" ("name", "profile_id") VALUES ('foo', 'p1')
[bun] 15:24:05.208 SELECT 7.108ms SELECT "user"."name", "user"."profile_id", "profile"."id" AS "profile__id" FROM "users" AS "user" LEFT JOIN "profiles" AS "profile" ON ("profile"."id" = "user"."profile_id")
p1 &{p1}If I misunderstood, could you provide a runnable example or SQL? |
|
Here is a functional example. I've noted that in the expected examples, package main
import (
"context"
"database/sql"
"fmt"
"github.com/uptrace/bun"
"github.com/uptrace/bun/dialect/pgdialect"
"github.com/uptrace/bun/driver/pgdriver"
"github.com/uptrace/bun/extra/bundebug"
)
type User struct {
UserID int64 `bun:",pk,autoincrement"`
Name string
Posts []*Post `bun:"rel:has-many,join:user_id=user_id"`
}
type Post struct {
PostID int64 `bun:",pk,autoincrement"`
UserID int64
Message string
Tags map[string]string `bun:",jsonb"`
}
type PostUser struct {
Post
User
}
func main() {
ctx := context.Background()
sqldb := sql.OpenDB(pgdriver.NewConnector(pgdriver.WithDSN("postgres://postgres:postgres@localhost:5432/postgres?sslmode=disable")))
db := bun.NewDB(sqldb, pgdialect.New())
db.AddQueryHook(bundebug.NewQueryHook(
bundebug.WithVerbose(true),
bundebug.FromEnv("BUNDEBUG"),
))
err := db.ResetModel(ctx, (*Post)(nil), (*User)(nil))
if err != nil {
panic(err)
}
err = initializeUsersAndPosts(db)
if err != nil {
panic(err)
}
postsWithUser := make([]PostUser, 0)
joinQuery := db.NewSelect().Model((*Post)(nil)).
Join("JOIN ? as ?", bun.Ident("users"), bun.Ident("user")).
JoinOn("?.? = ?.?", bun.Ident("user"), bun.Ident("user_id"),
bun.Ident("post"), bun.Ident("user_id"))
err = joinQuery.Scan(ctx, &postsWithUser)
// returns no posts and no users for the PostUser struct
fmt.Println("Number of Posts with PostUser struct: ", len(postsWithUser))
userPosts := make([]User, 0)
err = joinQuery.Scan(ctx, &userPosts)
// returns no posts and no users for the PostUser struct
fmt.Println("Number of Posts with User struct: ", len(userPosts))
users := []User{}
err = db.NewSelect().
Model(&users).
Relation("Posts").
Scan(ctx)
// Returns Users without Posts
if err != nil {
panic(err)
}
for _, user := range users {
fmt.Printf("User: %s, number Posts: %d\n",
user.Name, len(user.Posts))
}
posts := []Post{}
err = db.NewSelect().Model((*Post)(nil)).Scan(ctx, &posts)
// Ensure that all posts are indeed saved to Postgres
fmt.Println("Number of Total Posts saved: ", len(posts))
}
func initializeUsersAndPosts(db *bun.DB) error {
ctx := context.Background()
for j := 0; j < 10; j++ {
user := &User{
Name: fmt.Sprintf("user %d", j),
}
_, err := db.NewInsert().Model(user).Exec(ctx)
if err != nil {
return err
}
for i := 0; i < 20; i++ {
post := &Post{
Message: fmt.Sprintf("message %d", i),
Tags: map[string]string{
"tag1": fmt.Sprintf("tag1 %d", i),
"tag2": fmt.Sprintf("tag2 %d", i),
},
}
_, err := db.NewInsert().Model(post).Exec(ctx)
if err != nil {
return err
}
}
}
return nil
}Log Output: |
|
When inserting a post, should we set the userID to associate it with the user? func initializeUsersAndPosts(db *bun.DB) error {
ctx := context.Background()
for j := 0; j < 10; j++ {
user := &User{
Name: fmt.Sprintf("user %d", j),
}
_, err := db.NewInsert().Model(user).Returning("user_id").Exec(ctx, user)
if err != nil {
return err
}
for i := 0; i < 20; i++ {
post := &Post{
Message: fmt.Sprintf("message %d", i),
Tags: map[string]string{
"tag1": fmt.Sprintf("tag1 %d", i),
"tag2": fmt.Sprintf("tag2 %d", i),
},
UserID: user.UserID,
}
_, err := db.NewInsert().Model(post).Exec(ctx)
if err != nil {
return err
}
}
}
return nil
} |
|
Ah, yes, so making that change I see the first and third methods work. However, for the 2nd method, I get the following: output: I'm assuming that the query has to have the Thanks for the assistance, I thought I was going crazy |
|
@j2gg0s is there any way to still get the Join behavior without the struct tag for relations? I'd rather not have to pollute the main User Model just to express relations on Post |
If you want to discard unknown column, you can use option like this
What does 'the join behavior' mean? |
|
Hi @j2gg0s, I finally figured out what's happening. My behavior comment came from not fully understanding the error I was seeing earlier which I managed to debug to uncover what I think is the root. This might be more of a go-ism than a bun problem but perhaps you may have the answer. It seems that when having identically named member variables for the two embedded structs with underlying type I'm not really sure how to solve for that as it seems specific to byte arrays but perhaps you have an idea. Image in VSCode for intellisense (notice 
output: package main
import (
"context"
"database/sql"
"fmt"
"github.com/google/uuid"
"github.com/uptrace/bun"
"github.com/uptrace/bun/dialect/pgdialect"
"github.com/uptrace/bun/driver/pgdriver"
"github.com/uptrace/bun/extra/bundebug"
)
type User struct {
UserID uuid.UUID `bun:"type:uuid,notnull,default:gen_random_uuid()"`
Name string
// Posts []*Post `bun:"rel:has-many,join:user_id=user_id"`
}
type Post struct {
PostID uuid.UUID `bun:"type:uuid,notnull,default:gen_random_uuid()"`
UserID uuid.UUID `bun:"type:uuid,notnull"`
Message string
Tags map[string]string `bun:",jsonb"`
}
type PostUser struct {
Post
User
}
func main() {
ctx := context.Background()
sqldb := sql.OpenDB(pgdriver.NewConnector(pgdriver.WithDSN("postgres://postgres:postgres@localhost:5432/postgres?sslmode=disable")))
db := bun.NewDB(sqldb, pgdialect.New())
db.AddQueryHook(bundebug.NewQueryHook(
bundebug.WithEnabled(true),
))
_, err := db.NewRaw(`CREATE EXTENSION IF NOT EXISTS "uuid-ossp";`).Exec(ctx)
if err != nil {
panic(err)
}
err = db.ResetModel(ctx, (*Post)(nil), (*User)(nil))
if err != nil {
panic(err)
}
err = initializeUsersAndPosts(db)
if err != nil {
panic(err)
}
postsWithUser := make([]PostUser, 0)
joinQuery := db.NewSelect().Model((*Post)(nil)).
Join("JOIN ? as ?", bun.Ident("users"), bun.Ident("user")).
JoinOn("?.? = ?.?", bun.Ident("user"), bun.Ident("user_id"),
bun.Ident("post"), bun.Ident("user_id")).
ColumnExpr("?.*", bun.Ident("post")).ColumnExpr("?.*", bun.Ident("user"))
fmt.Println(joinQuery.String())
err = joinQuery.Scan(ctx, &postsWithUser)
// returns no posts and no users for the PostUser struct
if err != nil {
fmt.Println("error: ", err)
} else {
fmt.Println("Number of Posts with PostUser struct: ", len(postsWithUser))
}
posts := []Post{}
err = db.NewSelect().Model((*Post)(nil)).Scan(ctx, &posts)
// Ensure that all posts are indeed saved to Postgres
fmt.Println("Number of Total Posts saved: ", len(posts))
}
func initializeUsersAndPosts(db *bun.DB) error {
ctx := context.Background()
for j := 0; j < 10; j++ {
user := &User{
Name: fmt.Sprintf("user %d", j),
}
_, err := db.NewInsert().Model(user).Exec(ctx)
if err != nil {
return err
}
for i := 0; i < 20; i++ {
post := &Post{
Message: fmt.Sprintf("message %d", i),
Tags: map[string]string{
"tag1": fmt.Sprintf("tag1 %d", i),
"tag2": fmt.Sprintf("tag2 %d", i),
},
UserID: user.UserID,
}
_, err := db.NewInsert().Model(post).Exec(ctx)
if err != nil {
return err
}
}
}
return nil
} |
|
@genslein When we process structs, processFields is called. for _, f := range embedded {
ambiguousNames[f.prefix+f.subfield.Name]++
if !f.subfield.Tag.IsZero() {
ambiguousTags[f.prefix+f.subfield.Name]++
}
}
for _, embfield := range embedded {
subfield := embfield.subfield.Clone()
if ambiguousNames[subfield.Name] > 1 &&
!(!subfield.Tag.IsZero() && ambiguousTags[subfield.Name] == 1) {
continue // ambiguous embedded field
}So, when we scan into PostUser, Post.UserID and User.UserID is be ignored. |
|
Oh, interesting. Is there a way to make it explicit if there's detection for existing known table model structs? such as using |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Hey folks,
I'm having trouble with dealing with the
Joinaspect of Bun. I have two models and I wanted to be able to return composite model records. I've tried a few variations all yielding roughly the same behavior and I was hoping someone could point out what I missed in the docs or library code.For simplicity, I have an example that illustrates this:
The result I get is consistently something like:
"sql: Scan error on column index 22, name \"sub_component\": bun: ModelA does not have column \"sub_component\""It seems as though no matter the mix of things I've tried, the deserialization will not work wiith a
whereBaseQuerydue to the Model constraint.Additional options I've tried:
bun:"rel:has-many,join:model_a_id=model_a_id" json:"-"bun:"rel:belongs-to,join:model_a_id=model_a_id" json:"-"ModelA `bun:",extend"`I'm starting to feel like I'm going crazy but any help is appreciated.
The text was updated successfully, but these errors were encountered: