Golang: nested where clause generator

where_composer.go

/*Package wherecomposer builds potentially deeply nested WHERE statements.
Supplements GORM's .Where() method to address the following:
https://stackoverflow.com/questions/53213551/how-to-build-where-query-with-grouped-ors
https://github.com/jinzhu/gorm/issues/2059

Quick example, and how to build nested WHERE clauses and apply to a GORM query:

	where := build(constraints{
		{col:"user_id", val:1},
		{
			operator: "OR",
			groupAND: constraints{
				{col:"user_id", isNull:true},
				{col:"bundler_id", val:7},
				{col:"organization_id", val:9},
			},
		},
	})

	var r thing
	gorm.DB.Where(where.Query, where.Values...).Find(&r).Error
	// SELECT * FROM `things` WHERE (user_id = '1' OR (user_id IS NULL AND bundler_id = '7' AND organization_id = '9'))

See the corresponding tests for more in-depth examples on how to programmatically build (n)-nested
WHERE statements.
*/
package wherecomposer

type (
	// NOTICE: this is a recursive data structure; such that a constraint{} struct
	// has properties ("groupOR", "groupAND") that hold slices of constraint{}s
	constraint struct {
		col       string
		val       interface{}
		operator  string
		groupOR   constraints
		groupAND  constraints
		isNull    bool
		isNotNull bool
	}

	constraints []constraint

	// collection is the final result of the composed SQL query, using ? placeholders
	// where values should be substituted; and []values are the *correctly ordered*
	// list of things to substitute into the final query string
	collection struct {
		Query  string
		Values []interface{}
	}
)

func build(cs constraints) collection {
	coll := walk(cs, collection{}, "AND")
	coll.Query = fmt.Sprintf("(%s)", coll.Query)
	return coll
}

/*
Take a list of constraints and generates a SQL WHERE statement with it. Recursion
is a necessary evil here in order to accommodate the flexibility of programmatically
building (a potentially deeply nested) group of where statements.
*/
func walk(cc constraints, coll collection, whereOp string) collection {
	for index, c := range cc {
		hasNextOp := false
		nextOp := whereOp
		if len(cc) > index+1 {
			hasNextOp = true
			if len(cc[index+1].operator) > 0 {
				nextOp = cc[index+1].operator
			}
		}

		if len(c.groupOR) > 0 {
			step := walk(c.groupOR, collection{}, "OR")
			coll.Query += "(" + step.Query + ")"
			coll.Values = append(coll.Values, step.Values...)
			if hasNextOp {
				coll.Query += (" " + nextOp + " ")
			}
			continue
		}

		if len(c.groupAND) > 0 {
			step := walk(c.groupAND, collection{}, "AND")
			coll.Query += "(" + step.Query + ")"
			coll.Values = append(coll.Values, step.Values...)
			if hasNextOp {
				coll.Query += (" " + nextOp + " ")
			}
			continue
		}

		// This is where the key/value constraint (col = value) is actually
		// generated in the query string. Note, we special case IS NULL and
		// IS NOT NULL checks because GORM will generate (column = NULL) instead
		// of (column IS NULL), even if the VALUE that we pass is actually nil
		if c.isNull {
			coll.Query += (c.col + " IS NULL")
		} else if c.isNotNull {
			coll.Query += (c.col + " IS NOT NULL")
		} else {
			coll.Query += (c.col + " = ?")
			coll.Values = append(coll.Values, c.val)
		}

		if hasNextOp {
			coll.Query += (" " + nextOp + " ")
		}
	}
	return coll
}

//////////////////////////////////////////////////////////////
// TESTS: put these in a separate file if you're going to pull 
// this into your codebase.
//////////////////////////////////////////////////////////////
func TestSQLBuilder(t *testing.T) {
	t.Run("WithoutNesting", func(t *testing.T) {
		t.Run("DefaultOperatorUsesAnd", func(t *testing.T) {
			coll := build(constraints{
				{col: `alpha`, val: 1},
				{col: `bravo`, val: 2},
				{col: `charlie`, val: 3},
			})
			assert.Equal(t, `(alpha = ? AND bravo = ? AND charlie = ?)`, coll.Query)
			assert.Equal(t, []interface{}{1, 2, 3}, coll.Values)
		})

		t.Run("OrOperator", func(t *testing.T) {
			coll := build(constraints{
				{col: `alpha`, val: 1},
				{col: `bravo`, val: 2, operator: "OR"},
				{col: `charlie`, val: 3, operator: "OR"},
			})
			assert.Equal(t, `(alpha = ? OR bravo = ? OR charlie = ?)`, coll.Query)
			assert.Equal(t, []interface{}{1, 2, 3}, coll.Values)
		})
	})

	t.Run("SimpleNesting", func(t *testing.T) {
		t.Run("...AND(...OR...)", func(t *testing.T) {
			coll := build(constraints{
				{col: `alpha`, val: 1},
				{col: `bravo`, val: 2},
				{
					operator: "AND",
					groupOR: constraints{
						{col: `charlie`, val: 3},
						{col: `delta`, val: 4},
					},
				},
			})
			assert.Equal(t, `(alpha = ? AND bravo = ? AND (charlie = ? OR delta = ?))`, coll.Query)
			assert.Equal(t, []interface{}{1, 2, 3, 4}, coll.Values)
			// t.Logf(`SQL: %s | Values: %v`, coll.Query, coll.Values)
		})

		t.Run("...OR(...OR...)", func(t *testing.T) {
			coll := build(constraints{
				{col: `alpha`, val: 1},
				{col: `bravo`, val: 2},
				{
					operator: "OR",
					groupOR: constraints{
						{col: `charlie`, val: 3},
						{col: `delta`, val: 4},
					},
				},
			})
			assert.Equal(t, `(alpha = ? AND bravo = ? OR (charlie = ? OR delta = ?))`, coll.Query)
			assert.Equal(t, []interface{}{1, 2, 3, 4}, coll.Values)
			// t.Logf(`SQL: %s | Values: %v`, coll.Query, coll.Values)
		})

		t.Run("...OR(...AND...)", func(t *testing.T) {
			coll := build(constraints{
				{col: `alpha`, val: 1},
				{col: `bravo`, val: 2},
				{
					operator: "OR",
					groupAND: constraints{
						{col: `charlie`, val: 3},
						{col: `delta`, val: 4},
					},
				},
			})
			assert.Equal(t, `(alpha = ? AND bravo = ? OR (charlie = ? AND delta = ?))`, coll.Query)
			assert.Equal(t, []interface{}{1, 2, 3, 4}, coll.Values)
			// t.Logf(`SQL: %s | Values: %v`, coll.Query, coll.Values)
		})
	})

	t.Run("Complex", func(t *testing.T) {
		t.Run("...AND(...OR...)AND(...OR...)", func(t *testing.T) {
			coll := build(constraints{
				{col: `alpha`, val: 1},
				{col: `bravo`, val: 2},
				{
					operator: "AND",
					groupOR: constraints{
						{col: `charlie`, val: 3},
						{col: `delta`, val: 4},
					},
				},
				{
					operator: "AND",
					groupOR: constraints{
						{col: `echo`, val: `funk`},
						{col: `foxtrot`, val: `kingdom`},
					},
				},
			})
			assert.Equal(t, `(alpha = ? AND bravo = ? AND (charlie = ? OR delta = ?) AND (echo = ? OR foxtrot = ?))`, coll.Query)
			assert.Equal(t, []interface{}{1, 2, 3, 4, `funk`, `kingdom`}, coll.Values)
		})

		t.Run("...AND(...OR(...AND...))", func(t *testing.T) {
			coll := build(constraints{
				{col: `alpha`, val: 1},
				{col: `bravo`, val: 2},
				{
					operator: "AND",
					groupOR: constraints{
						{col: `charlie`, val: 3},
						{
							operator: "OR",
							groupAND: constraints{
								{col: `delta`, val: 4},
								{col: `echo`, val: `funk`},
							},
						},
					},
				},
			})
			assert.Equal(t, `(alpha = ? AND bravo = ? AND (charlie = ? OR (delta = ? AND echo = ?)))`, coll.Query)
			assert.Equal(t, []interface{}{1, 2, 3, 4, `funk`}, coll.Values)
		})

		t.Run("...OR(...AND(...OR...))", func(t *testing.T) {
			coll := build(constraints{
				{col: `alpha`, val: 1},
				{
					operator: "OR",
					groupOR: constraints{
						{col: `bravo`, val: 2},
						{
							operator: "AND",
							groupOR: constraints{
								{col: `charlie`, val: 3},
								{col: `delta`, val: 4},
							},
						},
					},
				},
			})
			assert.Equal(t, `(alpha = ? OR (bravo = ? AND (charlie = ? OR delta = ?)))`, coll.Query)
			assert.Equal(t, []interface{}{1, 2, 3, 4}, coll.Values)
		})
	})

	// Crucial thing this test looks for is that we don't try to add to the list of
	// []values when 'isNull' or 'isNotNull' fields are set, and *there are only the
	// same number of placeholders as there are values in the list*.
	t.Run("IS/IS_NOT NULL", func(t *testing.T) {
		coll := build(constraints{
			{col: `alpha`, val: 1},
			{col: `bravo`, val: 2},
			{
				operator: "OR",
				groupAND: constraints{
					{col: `charlie`, isNull: true},
					{col: `delta`, isNotNull: true},
					{col: `zulu`, val: 1},
				},
			},
		})
		assert.Equal(t, `(alpha = ? AND bravo = ? OR (charlie IS NULL AND delta IS NOT NULL AND zulu = ?))`, coll.Query)
		assert.Equal(t, []interface{}{1, 2, 1}, coll.Values)
		assert.Equal(t, 3, len(coll.Values))
		assert.Equal(t, 3, strings.Count(coll.Query, "?"))
	})
}