uptrace / bun

SQL-first Golang ORM
https://bun.uptrace.dev
BSD 2-Clause "Simplified" License
3.65k stars 221 forks source link

Has Many relation is simply not working #920

Closed e200 closed 11 months ago

e200 commented 11 months ago

These are the models:

type Answer struct {
    bun.BaseModel `bun:"table:posts"`
    ID            int        `json:"id" bun:",pk,autoincrement"`
    Body          *string    `json:"body"`
    PlainTextBody *string    `json:"plain_text_body"`
    TypeID        int        `json:"type_id"`
    UserID        int        `json:"user_id"`
    User          *user.User `json:"user" bun:"rel:belongs-to,join:user_id=id"`
    CreatedAt     *time.Time `json:"created_at"`
    UpdatedAt     *time.Time `json:"updated_at" bun:",scanonly"`
    DeletedAt     *time.Time `json:"deleted_at" bun:",scanonly"`

    ParentID int `json:"parent_id"`

    Replies []*Reply `json:"replies" bun:"rel:has-many,join:id=parent_id"`
}

type Reply struct {
    bun.BaseModel `bun:"table:posts"`

    ID            int        `json:"id" bun:",pk,autoincrement"`
    Body          *string    `json:"body"`
    PlainTextBody *string    `json:"plain_text_body"`
    TypeID        int        `json:"type_id"`
    UserID        int        `json:"user_id"`
    User          *user.User `json:"user" bun:"rel:belongs-to,join:user_id=id"`
    CreatedAt     *time.Time `json:"created_at"`
    UpdatedAt     *time.Time `json:"updated_at" bun:",scanonly"`
    DeletedAt     *time.Time `json:"deleted_at" bun:",scanonly"`

    ParentID int `json:"parent_id"`
}

This is the execution code:

query := s.db.
        NewSelect().
        Model(answers).
        Relation("User").
        Relation("Replies").
        Join("JOIN post_types AS pt").
        JoinOn("pt.slug = ?", "answer").
        Where("answer.parent_id = ?", postID).
        Where("answer.deleted_at IS ?", nil).
        Order("answer.created_at DESC")

if _, err := query.Exec(ctx, answers); err != nil {
         return err
}

This code results in the following SQL:

 SELECT "answer"."id", "answer"."body", "answer"."plain_text_body", "answer"."type_id", "answer"."user_id", "answer"."created_at", "answer"."parent_id", "user"."id" AS "user__id", "user"."first_name" AS "user__first_name", "user"."last_name" AS "user__last_name", "user"."username" AS "user__username", "user"."email" AS "user__email", "user"."phone" AS "user__phone", "user"."password" AS "user__password", "user"."created_at" AS "user__created_at" FROM "posts" AS "answer" LEFT JOIN "users" AS "user" ON ("user"."id" = "answer"."user_id") JOIN post_types AS pt ON (pt.slug = 'answer') WHERE (answer.parent_id = 7) AND (answer.deleted_at IS NULL) ORDER BY "answer"."created_at" DESC

There is no SQL generated from the relation Replies, it just completely ignores it resulting in the following output:

[{
            "id": 49,
            "body": "Ok",
            "plain_text_body": "ok",
            "type_id": 2,
            "user_id": 9,
            "user": {
                "id": 9,
                "first_name": "XXX",
                "last_name": "XXX",
                "username": "xxx",
                "email": "eleandro@gmail.com",
                "phone": null,
                "created_at": "2023-10-06T19:08:16.210299Z"
            },
            "created_at": "2023-10-15T12:51:01.601879Z",
            "updated_at": null,
            "deleted_at": null,
            "parent_id": 7,
            "replies": null
}]

where replies are always null while there should be the data from the database.

e200 commented 11 months ago

I just figured out the problem was that I was using Exec instead of Scan.