Bug : functions in Relation statements are not applied on select queries terminated with Count

[enzalito]

Hello,

I found a weird bug with the apply function that can be passed to the Relation method of a SelectQuery when it is coupled with the Count query termination.

With the following models.

type A struct {
   id   int64 `bun:",pk,autoincrement"`
   name string 
}

type B struct {
   id  int64 `bun:",pk,autoincrement"`
   aId int64
   a   *A    `bun:"rel:belongs-to,join:aId=id"`
}

The WHERE clause in the query bellow will be ignored.

count, err := db.NewSelect().
   Model(&[]*B{}).
   Relation("A", func(q *bun.SelectQuery) *bun.SelectQuery {
      return q.Where("name = ?", "someName")
   }).
   Count(ctx)

However this query works as expected.

err := db.NewSelect().
   Model(&[]*B{}).
   Relation("A", func(q *bun.SelectQuery) *bun.SelectQuery {
      return q.Where("name = ?", "someName")
   }).
   Scan(ctx)

[elliotcourant]

What is the actual SQL query generated from

count, err := db.NewSelect().
   Model(&[]*B{}).
   Relation("A", func(q *bun.SelectQuery) *bun.SelectQuery {
      return q.Where("name = ?", "someName")
   }).
   Count(ctx)

If you are able to provide it?

---

I have a theory but I'd have to tinker with it, but aggregate queries (counts, sums, avg, etc) are built slightly differently than regular queries that just yield the rows based on the predicate. I might be wrong but I think that's what might be happening.

[vmihailenco]

@elliotcourant count queries are generated with the same function that accepts count bool arg - https://github.com/uptrace/bun/blob/master/query_select.go#L484-L659

@TonDar0n this should work for belongs-to and has-one relations, but not for has-many and m2m. Please provide a reproducer - https://stackoverflow.com/help/minimal-reproducible-example

[enzalito]

Thank you for your answers. Here is a reproducer :

package main

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

	embeddedpostgres "github.com/fergusstrange/embedded-postgres"
	"github.com/uptrace/bun"
	"github.com/uptrace/bun/dialect/pgdialect"
	"github.com/uptrace/bun/driver/pgdriver"
)

type Foo struct {
	Id   int64 `bun:",pk,autoincrement"`
	Name string
}

type Bar struct {
	Id    int64 `bun:",pk,autoincrement"`
	FooId int64
	Foo   *Foo `bun:"rel:belongs-to,join:foo_id=id"`
}

func main() {
	pgServer := embeddedpostgres.NewDatabase(
		embeddedpostgres.DefaultConfig().Logger(nil),
	)
	err := pgServer.Start()
	if err != nil {
		panic(err)
	}
	defer pgServer.Stop()

	ctx := context.Background()
	dsn := "postgres://postgres:postgres@localhost:5432/postgres?sslmode=disable"
	sqlDb := sql.OpenDB(pgdriver.NewConnector(pgdriver.WithDSN(dsn)))
	db := bun.NewDB(sqlDb, pgdialect.New())

	_, err = db.NewCreateTable().
		Model((*Foo)(nil)).
		Exec(ctx)
	if err != nil {
		panic(err)
	}
	_, err = db.NewCreateTable().
		Model((*Bar)(nil)).
		Exec(ctx)
	if err != nil {
		panic(err)
	}

	_, err = db.NewInsert().
		Model(&[]*Foo{
			{Name: "first"},
			{Name: "second"},
		}).
		Exec(ctx)
	if err != nil {
		panic(err)
	}
	_, err = db.NewInsert().
		Model(&[]*Bar{
			{FooId: 1},
			{FooId: 2},
		}).
		Exec(ctx)
	if err != nil {
		panic(err)
	}

	count, err := db.NewSelect().
		Model(&[]*Bar{}).
		Relation("Foo", func(q *bun.SelectQuery) *bun.SelectQuery {
			return q.Where("name = 'first'")
		}).
		Count(ctx)
	if err != nil {
		panic(err)
	}
	fmt.Println("Result with Count:", count)

	count, err = db.NewSelect().
		Model(&[]*Bar{}).
		Relation("Foo", func(q *bun.SelectQuery) *bun.SelectQuery {
			return q.Where("name = 'first'")
		}).
		ScanAndCount(ctx)
	if err != nil {
		panic(err)
	}
	fmt.Println("Result with ScanAndCount:", count)
}

[atikahe]

Hi, is there any follow up to this issue? I'm having the same problem but with has-many.

[github-actions]

This issue has been automatically marked as stale because it has not had activity in the last 30 days. If there is no update within the next 7 days, this issue will be closed.