Reusable conditional where query

[unbearables]

Hello,

I want to create reusable conditional query for pagination (count query + offset query).

I haven't found any official reusable solution. So far I have following solution:

import (
  "strings"

  sqrl "github.com/Masterminds/squirrel"
)
...
var whereConds []string
var whereArgs []interface{}

if condition {
  sqlQuery, args, _ := sqrl.Eq{"user_id": userId}.ToSql()
  whereConds = append(whereConds, sqlQuery)
  whereArgs = append(whereArgs, args...)
}
// more conditional queries

var count uint
err := sqrl.Select("COUNT(*)").From("users").
  Where(strings.Join(whereConds, " AND "), whereArgs...).
  RunWith(db).Scan(&count)
if err != nil {
  panic(err)
}

users := []UserEntity{}
if count > 0 {
  offset := (pageNumber - 1) * (pageSize + 1)

  sql, args, _ := sqrl.Select("*").From("users").
    Where(strings.Join(whereConds, " AND "), whereArgs...).
    Limit(uint64(pageSize)).Offset(uint64(offset)).
    ToSql()
  err := db.Select(&users, sql, args...)
  if err != nil {
    panic(err)
  }
}

Is there any better solution for reusing a where condition?

[ericedem]

I was just messing around with this for a very similar problem, and I think you can just use squirrel.And (which is the default).

This is a snippet of what I came up with:

filterPredicate := squirrel.And{}

if len(query.Status) > 0 {
	filterPredicate = append(filterPredicate, squirrel.Eq{"status": query.Status})
}

if !query.CreatedBefore.IsZero() {
	filterPredicate = append(filterPredicate, squirrel.LtOrEq{"created": query.CreatedBefore})
}

if !query.CreatedAfter.IsZero() {
	filterPredicate = append(filterPredicate, squirrel.GtOrEq{"created": query.CreatedAfter})
}

resultsSql = resultsSql.Where(filterPredicate)
totalSql = totalSql.Where(filterPredicate)

This seems to work ok. The part where I got stumped was when I needed to have custom dialect queries like: .Where("? = ANY(name)", query.Name), so if anyone knows how to do that using squirrel I'd love to know.

[jsonMark]

it don not support this :

filterPredicate = append(filterPredicate, squirrel.Eq{"title LIKE ?": fmt.Sprint("%", data.Title, "%")})