Using Raw SQL in queries

[nikhrom]

Your Question

Sometimes there is a need to use functions that are not in code generation (e.g. UPPER). For example, I would like to get the following request:

SELECT * 
FROM some_table
WHERE UPPER(status) = "SOME_STATUS" AND some_field = "some value"
LIMIT 1

I know that it's available using gorm.DB, like this:

q := Query().SomeTable
do := q.Where(
  q.SomeField.Eq("some value"),
)
do.UnderlyingDB().Where("UPPER(status) = 'SOME_STATUS'")
some, err := do.First()

But this solution doesn't seem clean to me.

I would like something like the following:

q := Query().SomeTable
do := q.Where(
  q.SomeField.Eq("some value"),
  q.Raw("UPPER(status) = 'SOME_STATUS'"),
).First()

Is there a similar solution?

The document you expected this should be explained

Expected answer

[nikhrom]

For the future, I solved it like this:

import (
	"gorm.io/gen"
	"gorm.io/gen/field"
	"gorm.io/gorm/clause"
)

type Tabler interface {
	Alias() string
	TableName() string
}

type Field struct {
	Field field.Expr
	Table Tabler
}

type rawCond struct {
	field.Field
	sql  string
	args []interface{}
}

func (m rawCond) BeCond() interface{} {
	args := []interface{}{}
	for _, v := range m.args {
		switch arg := v.(type) {
		case Field:
			column := clause.Column{
				Name: arg.Field.ColumnName().String(),
				Raw:  false,
			}
			if arg.Table != nil {
				if arg.Table.Alias() != "" {
					column.Table = arg.Table.Alias()
				} else {
					column.Table = arg.Table.TableName()
				}
			}
			args = append(args, column)
		case field.Expr:
			column := clause.Column{
				Name: arg.ColumnName().String(),
				Raw:  false,
			}
			args = append(args, column)
		default:
			args = append(args, v)
		}
	}

	expr := clause.NamedExpr{SQL: m.sql}
	expr.Vars = append(expr.Vars, args...)
	return expr
}

func (rawCond) CondError() error { return nil }

func RawCond(sql string, args ...interface{}) gen.Condition {
	return &rawCond{
		sql:  sql,
		args: args,
	}
}

//==================================
     HOW TO USE
//==================================

q := Query().SomeTable

----- First way ------
q.Where(
  q.SomeField.Eq("some value"),
  RawCond("UPPER(status) = 'SOME_STATUS'"),
).First()
// SELECT * FROM "some_table" WHERE "some_table"."some_field" = 'some value' AND UPPER(status) = 'SOME_STATUS'

----- Second way ------
q.Where(
  q.SomeField.Eq("some value"),
  RawCond("UPPER(?) = ?", q.Status, "SOME_STATUS"),
).First()
// SELECT * FROM "some_table" WHERE "some_table"."some_field"= 'some value' AND UPPER("status") = 'SOME_STATUS'


----- Third way ------
q.Where(
  q.SomeField.Eq("some value"),
  RawCond("UPPER(?) = ?", Field{Field: q.Status, Table: &q} , "SOME_STATUS"),
).First()
// SELECT * FROM "some_table" WHERE "some_table"."some_field" = 'some value' AND UPPER("some_table"."status") = 'SOME_STATUS'

[tr1v3r]

Raw SQL may lead to some unexpected SQL Injection vulnerabilities. So we are very cautious about the use of raw SQL

[garlicPasta]

Just to add my 2 cents. I am currently having the problem, that i need to filter strings in a DB case insensitive. For solving that problem is there a way around rawSql ? Probably not... Therefore i would appreciate a feature like the one proposed here. Maybe with a comment in the docs that it is unsafe.

[cusspvz]

Would be good to have this ability within the gen API Querier interface, by allowing raw SQL to be built in case the interface method returns the gen.DAO