Setting Where condition of SelectQuery,UpdateQuery centrally

[derkan]

Hi All,

I wanted to filter SelectQuery, UpdateQuery, DeleteQuery typed queries in a single place. I've tried interfaces&switch type but always had boilerplate code. Later tried generics in go 1.18, but again got boilerplate code.

Is there a way to get rid of boilerplate code at userFilter function in the following example:

package main

import (
	"context"
	"database/sql"
	"github.com/google/uuid"
	"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,alias:u"`
	ID             uuid.UUID `bun:",pk,type=uuid" json:"id"`
	OrganizationID uuid.UUID `json:"organization_id,omitempty"`
	Email          string    `json:"email"`
	IsSysadm       bool      `json:"is_sysadm"`
}

type Query interface {
	~*bun.SelectQuery | ~*bun.UpdateQuery | ~*bun.DeleteQuery
}

// boilerplate code in this func, calling WhereGroup for each type
func userFilter[T Query](oID uuid.UUID, qI T) T {
	switch q := interface{}(qI).(type) {
	case *bun.SelectQuery:
		q.WhereGroup("AND", func(query *bun.SelectQuery) *bun.SelectQuery {
			return query.Where("u.organization_id = ?", oID)
		})
	case *bun.UpdateQuery:
		q.WhereGroup("AND", func(query *bun.UpdateQuery) *bun.UpdateQuery {
			return query.Where("u.organization_id = ?", oID)
		})
		q.ExcludeColumn("organization_id", "is_sysadm")
	case *bun.DeleteQuery:
		q.WhereGroup("AND", func(query *bun.DeleteQuery) *bun.DeleteQuery {
			return query.Where("u.organization_id = ?", oID)
		})
	}
	return qI
}

func main() {
	ctx := context.Background()
	sqlite, err := sql.Open(sqliteshim.ShimName, "file::memory:?cache=shared")
	if err != nil {
		panic(err)
	}
	sqlite.SetMaxOpenConns(1)
	db := bun.NewDB(sqlite, sqlitedialect.New())
	db.AddQueryHook(bundebug.NewQueryHook(
		bundebug.WithVerbose(true),
		bundebug.FromEnv("BUNDEBUG"),
	))
	if err = db.ResetModel(ctx, (*User)(nil)); err != nil {
		panic(err)
	}

	// Usage examples below:
	oID := uuid.New()
	// SelectQuery
	users := make([]User, 0)
	if err = userFilter(oID, db.NewSelect().Model(&users)).
		OrderExpr("id ASC").Scan(ctx); err != nil {
		panic(err)
	}
	// UpdateQuery
	usr := &User{ID: uuid.New(), IsSysadm: false}
	if _, err = userFilter(oID, db.NewUpdate().Model(usr)).
		Where("id = ?", usr.ID).Exec(ctx); err != nil {
		panic(err)
	}
	// DeleteQuery
	if _, err = userFilter(oID, db.NewDelete().Model((*User)(nil))).
		Where("id = ?", uuid.New()).Exec(ctx); err != nil {
		panic(err)
	}
}

[vmihailenco]

We could add something like this:

type Query interface {
    // note how WhereGroup works with Query instead of SelectQuery/UpdateQuery etc
    WhereGroup(string, func(Query) Query) Query
}

q := db.NewSelect().Query()
// q has type Query
q = q.WhereGroup("AND", func(query Query) Query {
	return query.Where("u.organization_id = ?", oID)
})

This would require writing adapters for SelectQuery/UpdateQuery/etc that implement common Query interface.

[derkan]

Thanks, that will be enough.

This will be a breaking change, it will be better to add a new function with name WhereQ and depreciate WhereGroup.

I can send a PR if it is ok?

[vmihailenco]

I suggest to add new API so it should be backwards compatible, e.g.

func (q *SelectQuery) Query() Query {
    return &selectQueryAdapter{q: q}
}

type selectQueryAdapter struct {
    q *SelectQuery
}

func (q *selectQueryAdapter) WhereGroup(sep string, fn func(Query) Query) Query {
    // Call the original WhereGroup
    return q.q.WhereGroup(sep, func(q *SelectQuery) *SelectQuery {
        return fn(q).(*SelectQuery)
    })
}

@derkan does that make sense?

[derkan]

This will be insufficient, because for example in userFilter(oID uuid.UUID, q bun.Query) bun.Query where called like userFilter(.., q.Query()), we can not access to methods Where, WhereOr, WhereGroup.....

What about adding this wrapper and Condition interface:

type Condition interface {
	Query
	Where(query string, args ...interface{}) Condition
	WhereGroup(sep string, fn func(Condition) Condition) Condition
	WhereOr(query string, args ...interface{}) Condition
	WhereDeleted() Condition
	WhereAllWithDeleted() Condition
	WherePK(cols ...string) Condition
	Query() interface{}
}

type selectCondition struct {
	*SelectQuery
}

func (q *selectCondition) WhereGroup(sep string, fn func(Condition) Condition) Condition {
	q.SelectQuery.WhereGroup(sep, func(qs *SelectQuery) *SelectQuery {
		return fn(&selectCondition{qs}).(*selectCondition).SelectQuery
	})
	return q
}

func (q *selectCondition) Where(query string, args ...interface{}) Condition {
	q.SelectQuery.Where(query, args...)
	return q
}

func (q *selectCondition) WhereOr(query string, args ...interface{}) Condition {
	q.SelectQuery.WhereOr(query, args...)
	return q
}

func (q *selectCondition) WhereDeleted() Condition {
	q.SelectQuery.WhereDeleted()
	return q
}

func (q *selectCondition) WhereAllWithDeleted() Condition {
	q.SelectQuery.WhereAllWithDeleted()
	return q
}

func (q *selectCondition) WherePK(cols ...string) Condition {
	q.SelectQuery.WherePK(cols...)
	return q
}

func (q *selectCondition) Query() interface{} {
	return q.SelectQuery
}

func (q *SelectQuery) Query() Condition {
	return &selectCondition{q}
}

Adapters for UpdateQuery and DeleteQuery will be added in same style.

Usage will be like:

func orgFilter(oID uuid.UUID, q bun.Condition) bun.Condition {
	q.WhereGroup("AND", func(query bun.Condition) bun.Condition {
		return query.Where("u.organization_id = ?", oID)
	})
	return q
}

q := db.NewSelect().Model(&users)
orgFilter(oID, q.Query())
if _, err = q.Where("id = ?", uuid.New()).Exec(ctx); err != nil {
    panic(err)
}

I added Query() interface{} to be able to continue chaining like :

orgFilter(oID, db.NewSelect().Model(&users).Query()).Query().(*bun.SelectQuery).
Where("id = ?", uuid.New()).
Exec(ctx)...

[vmihailenco]

Let's:

• rename Condition to QueryBuilder
• and change Q() interface{} to Unwrap() Query

Otherwise, looks good 👍