Optional where clause

[absolutejam]

Hey!

I've just discovered bob and I have to say, I'm so impressed. I've tried sqlx, slc and GORM, but bob is the right amount of type-safe (via. generation) an flexibility (via. query builder) ❤️

I'm just wondering if there's an 'idiomatic' way to add an optional WHERE mod?

Here's a simplified example, that uses optional criteria via. pointers):

type GetAccountOpts struct {
        AccountID *accounts.AccountID
        Email     *string
}

func (s *AccountStore) GetAccount(ctx context.Context, opts GetAccountOpts) (*Account, error) {
	return models.Accounts.Query(
		db.OptionalWhere(models.SelectWhere.Account.PublicID.EQ, opts.ID),
		db.OptionalWhere(models.SelectWhere.Account.Email.EQ, opts.Email),
	).One(ctx, s.ex)
}

If I was doing it in pure SQL (sqlite), I'd use something like:

WHERE
(accounts.id = ?1 OR (?1 IS NULL)) OR ...

and my OptionalWhere currently looks like this:

func OptionalWhere[V any](
  where func(val V) mods.Where[*dialect.SelectQuery], 
  value *V,
) mods.Where[*dialect.SelectQuery] {
	if value != nil {
		return where(*value)
	}

	return sm.Where(sqlite.RawQuery("NULL is NULL"))
}

but I was wondering if there's simply a way to return a no-op WHERE mod so that the resulting query only includes the fields that are actually supplied?

[stephenafamo]

First, I really like that you're using query mods beyond just what is generated by default.
The power of mods are that you can create your own to do whatever you want.

In your example, you're pretty close. The thing is... the mod doesn't HAVE TO do anything. Try this:

func OptionalWhere[V any](
	where func(val V) mods.Where[*dialect.SelectQuery],
	value *V,
) bob.Mod[*dialect.SelectQuery] {
	if value != nil {
		return where(*value)
	}

	return bob.ModFunc[*dialect.SelectQuery](func(*dialect.SelectQuery) {})
}

[absolutejam]

That's awesome! I could see that the mod was just a function but I was struggling to get the right combination of generic types.

Yeah, I have a few more that I'll probably throw together for re-use - eg. pagination via. LIMIT and SKIP.

Awesome work on bob, and thanks for your help!

[absolutejam]

My initial attempt (untested as of yet!) is:

type Pagination struct {
	Limit int `query:"limit"`
	Page  int `query:"page"`
}

type paginate[Q interface {
	SetLimit(limit any)
	SetOffset(offset any)
}] struct {
	Pagination
}

const (
	PaginateLimitDefault = 25
	PaginateLimitMax     = 100
)

func (f paginate[Q]) Apply(q Q) {
	if f.Limit <= 0 {
		f.Limit = PaginateLimitDefault
	}

	q.SetLimit(min(f.Limit, PaginateLimitMax))

	if page := min(f.Page, 1); page > 1 {
		q.SetOffset(f.Limit * (page - 1))
	}


func Paginate(p Pagination) bob.Mod[*dialect.SelectQuery] {
	return paginate[*dialect.SelectQuery]{
		Pagination: p,
	}
}

Pagination is used both in the API (Huma), and in my repository 'Opts' types, eg.

type ListAccountOpts struct {
        db.Pagination
        // etc.
}

Usage:

return models.Accounts.Query(db.Paginate(opts.Pagination)).All(ctx, ex)

[stephenafamo]

Nice 🎉