Issue: Relations Not Preserved in Queries for Pagination

[mdsohelmia]

When using Bun ORM with SQLite (sqlitedialect) in a pagination implementation, relations specified in the parent query (e.g., .Relation("Products")) are not preserved in cloned queries, resulting in related data not being loaded. This issue occurs when cloning a SelectQuery and binding it to a generic slice (Model(&items)), which is common in pagination scenarios.
Expected Behavior
Cloning a SelectQuery with .Relation("Products") should preserve the relation in the cloned query, and the related data (e.g., Products for a User) should be populated when scanning the results.
Actual Behavior
The cloned query does not include the LEFT JOIN for the specified relation in the generated SQL, resulting in empty relation fields (e.g., User.Products is nil or empty).
Steps to Reproduce

Define User and Product models with a has-many relationship.
Create a pagination function that clones the parent query for counting and selecting paginated data.
Execute the paginated query with .Relation("Products") on the parent query.
Observe that the Products relation is not loaded in the results.

Minimal Reproducible Example

package main

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

	"github.com/gin-gonic/gin"
	"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 {
	bun.BaseModel `bun:"table:users"`
	ID            int64     `bun:"id,pk,autoincrement"`
	Name          string    `bun:"name"`
	Products      []Product `bun:"rel:has-many,join:id=user_id"`
}

type Product struct {
	bun.BaseModel `bun:"table:products"`
	ID            int64  `bun:"id,pk,autoincrement"`
	UserID        int64  `bun:"user_id"`
	Name          string `bun:"name"`
}

type Params struct {
	Page     int
	PageSize int
}

type Meta struct {
	Total      int `json:"total"`
	Page       int `json:"page"`
	PageSize   int `json:"page_size"`
	TotalPages int `json:"total_pages"`
}

type Links struct {
	First string `json:"first"`
	Prev  string `json:"prev,omitempty"`
	Next  string `json:"next,omitempty"`
	Last  string `json:"last"`
}

type Result[T any] struct {
	Data  []T
	Meta  Meta
	Links Links
}

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

	sqldb, err := sql.Open(sqliteshim.ShimName, "file::memory:?cache=shared")
	if err != nil {
		panic(err)
	}
	db := bun.NewDB(sqldb, sqlitedialect.New())
	db.AddQueryHook(bundebug.NewQueryHook(bundebug.WithVerbose(true)))

	// Create tables
	err = db.ResetModel(ctx, (*User)(nil), (*Product)(nil))
	if err != nil {
		log.Fatalf("Failed to create tables: %v", err)
	}

	// Insert sample data
	users := []User{
		{Name: "Alice", Products: []Product{{Name: "Product A"}, {Name: "Product B"}}},
		{Name: "Bob", Products: []Product{{Name: "Product C"}}},
	}
	_, err = db.NewInsert().Model(&users).Exec(ctx)
	if err != nil {
		log.Fatalf("Failed to insert sample data: %v", err)
	}

	// Initialize Gin router
	r := gin.Default()
	r.GET("/users", func(c *gin.Context) {
		query := db.NewSelect().Model((*User)(nil)).Relation("Products")
		result, err := paginateQuery[User](c, query)
		if err != nil {
			c.JSON(500, gin.H{"error": err.Error()})
			return
		}
		c.JSON(200, result)
	})

	// Start server
	if err := r.Run(":8080"); err != nil {
		log.Fatalf("Failed to start server: %v", err)
	}
}

func paginateQuery[T any](c *gin.Context, q *bun.SelectQuery) (Result[T], error) {
	var result Result[T]
	var items []T
	ctx := c.Request.Context()

	params := Params{Page: 1, PageSize: 10}

	// Count total
	countQuery := q.Clone().Limit(0).Offset(0)
	total, err := countQuery.Count(ctx)
	if err != nil {
		return result, fmt.Errorf("count query failed: %w", err)
	}

	// Build select query
	selectQuery := q.Model(&items).Limit(params.PageSize).Offset((params.Page - 1) * params.PageSize)

	// Log SQL
	sql := selectQuery.String()

	fmt.Println("Generated SQL for selectQuery:", sql)

	if err := selectQuery.Scan(ctx); err != nil {
		return result, fmt.Errorf("select query failed: %w", err)
	}

	result = Result[T]{
		Data: items,
		Meta: Meta{
			Total:      total,
			Page:       params.Page,
			PageSize:   params.PageSize,
			TotalPages: (total + params.PageSize - 1) / params.PageSize,
		},
		Links: Links{
			First: "/users?page=1&page_size=10",
			Last:  fmt.Sprintf("/users?page=%d&page_size=10", (total+params.PageSize-1)/params.PageSize),
		},
	}

	return result, nil
}

Environment

Bun Version: Run go list -m github.com/uptrace/bun to confirm (e.g., v1.1.x).
Database: SQLite (sqlitedialect).
Go Version: Run go version to confirm (e.g., go1.20).

Observed Output
When accessing http://localhost:8080/users, the Products field in each User is nil or empty, despite .Relation("Products") being specified in the parent query.
Generated SQL (example):
Generated SQL for selectQuery: SELECT "user"."id", "user"."name" FROM "users" LIMIT 10 OFFSET 0

Note: The LEFT JOIN products ON products.user_id = users.id is missing, indicating the relation is not preserved.
JSON Response (example):

{
  "data": [
    {"id": 1, "name": "Alice", "products": null},
    {"id": 2, "name": "Bob", "products": null}
  ],
  "meta": {
    "total": 2,
    "page": 1,
    "page_size": 10,
    "total_pages": 1
  },
  "links": {
    "first": "/users?page=1&page_size=10",
    "last": "/users?page=1&page_size=10"
  }
}

Expected Output
The Products field should be populated:

{
  "data": [
    {
      "id": 1,
      "name": "Alice",
      "products": [
        {"id": 1, "user_id": 1, "name": "Product A"},
        {"id": 2, "user_id": 1, "name": "Product B"}
      ]
    },
    {
      "id": 2,
      "name": "Bob",
      "products": [
        {"id": 3, "user_id": 2, "name": "Product C"}
      ]
    }
  ],
  "meta": {
    "total": 2,
    "page": 1,
    "page_size": 10,
    "total_pages": 1
  },
  "links": {
    "first": "/users?page=1&page_size=10",
    "last": "/users?page=1&page_size=10"
  }
}

Expected SQL:
SELECT "user"."id", "user"."name", "products"."id" AS "products__id", "products"."user_id" AS "products__user_id", "products"."name" AS "products__name"
FROM "users"
LEFT JOIN "products" ON "products"."user_id" = "user"."id"
LIMIT 10 OFFSET 0

Additional Notes

The issue appears when cloning the query (q.Clone()) and binding it to a generic slice (Model(&items)).
A standalone query (db.NewSelect().Model(&users).Relation("Products").Scan(ctx)) works correctly, loading the Products relation.
This suggests a bug in Bun’s query cloning or model binding with generics, possibly related to how relation metadata is handled in SQLite.

Workaround
Explicitly re-applying the relation in the pagination function works:
selectQuery := q.Clone().Model(&items).Relation("Products").Limit(params.PageSize).Offset((params.Page - 1) * params.PageSize)

However, this requires duplicating the relation specification, which is redundant and error-prone.
References

Bun Documentation: Table Relationships
Bun GitHub: uptrace/bun

Request
Please investigate why .Relation("Products") is not preserved in cloned queries with SQLite and provide a fix or guidance on avoiding this issue without duplicating relation specifications.

[oborchardt]

Hi,
I ran into the same problem of missing relations when using an interface (maybe for a slightly different reason since I didn't clone the query). I was able to work around the problem by not putting the queries directly into the paginate function, but instead passing functions that build the queries for a bun model. []T is the list I want to split into pages, countQuery and baseQuery are the relevant functions.

func paginate[T pagetokens.Paginable, S pagetokens.PageToken](
	ctx context.Context,
	countQuery func(*[]T) *bun.SelectQuery,
	baseQuery func(*[]T) *bun.SelectQuery,
	cursor string,
	pageSize int32,
	nilQueryUpdate func(*bun.SelectQuery) *bun.SelectQuery,
	forwardQueryUpdate func(*bun.SelectQuery, S) *bun.SelectQuery,
	backwardsQueryUpdate func(*bun.SelectQuery, S) *bun.SelectQuery,
) ([]T, int32, string, string, error) {
	var items []T
	var totalCount int

	totalCount, err := countQuery(&items).Count(ctx)
...

Maybe that can help someone while the investigation continues :)

[j2gg0s]

Bun doesn't use a single JOIN for has-many relationships by default.
When a User has N Products, this would return N rows of data if a JOIN were used.

Instead, it performs a separate query for the related items.
This is often referred to as an "N+1 query problem" in ORMs.

When you run the example/rel-has-many example, you can see the specific queries executed.

[bun]  10:51:13.667   CREATE TABLE          1.997ms  CREATE TABLE "users" ("id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "name" VARCHAR)
[bun]  10:51:13.667   CREATE TABLE            158µs  CREATE TABLE "profiles" ("id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "lang" VARCHAR, "active" BOOLEAN, "user_id" INTEGER)
[bun]  10:51:13.667   INSERT                   88µs  INSERT INTO "users" ("id", "name") VALUES (1, 'user 1'), (2, 'user 2')
[bun]  10:51:13.667   INSERT                   76µs  INSERT INTO "profiles" ("id", "lang", "active", "user_id") VALUES (1, 'en', TRUE, 1), (2, 'ru', TRUE, 1), (3, 'md', FALSE, 1)
[bun]  10:51:13.668   SELECT                   49µs  SELECT "user".* FROM "users" AS "user" ORDER BY user.id ASC LIMIT 1
[bun]  10:51:13.668   SELECT                   46µs  SELECT "profile"."id", "profile"."lang", "profile"."active", "profile"."user_id" FROM "profiles" AS "profile" WHERE ("profile"."user_id" IN (1)) AND (active IS TRUE)
1 user 1 &{1 en true 1} &{2 ru true 1}

For has-one, the Count method considers the relation. You can use the ScanAndCount method directly.
For has-many, you can manually write JOIN, like db.NewSelect(user).Join("JOIN products ON products.user_id = user.id")