Skip to content

Expressions

Jump to bottom
go-jet edited this page Jul 30, 2021 · 40 revisions

Jet SQL builder supports following expression types:

  • Bool expressions
  • Integer expressions
  • Float expressions
  • String expressions
  • Date expressions
  • Time expressions
  • Timez expressions (Time with time zone)
  • Timestamp expressions
  • Timestampz expressions (Timestamp with time zone)
  • Interval expressions
  • Row expressions

*This list might be extended with feature Jet releases.

MySQL and MariaDB does not support Timez and Timestampz expression.

Literal Types

For every expression type there is a method to create one expression literal type.

Literal types examples:

Bool(true)  
Int(11), UInt(22), Int64(-10000), UInt64(200000), ....
Float(23.44), Decimal("11.20000345")      
String("John Doe"), UUID(uuid.MustParse("a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11"))   
Date(2010, 12, 3)  
Time(23, 6, 6, 1)   
Timez(23, 6, 6, 222, +200)    
Timestamp(2010, 10, 21, 15, 30, 12, 333)    
Timestampz(2010, 10, 21, 15, 30, 12, 444, 0)    
Bytea("byte array"), Bytea([]byte("byte array"))
Interval type:

PostgreSQL:

// INTERVAL creates new interval expression from the list of quantity-unit pairs.
INTERVAL(1, YEAR, 10, MONTH)
INTERVAL(1, YEAR, 10, MONTH, 20, DAY, 3, HOUR)

// INTERVALd creates interval expression from time.Duration
INTERVALd(2*time.Hour+3*time.Minute+4*time.Second+5*time.Microsecond)

MySQL and MariaDB:

// INTERVAL creates new temporal interval.
// In a case of MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR unit type
// value parameter should be number. For example: INTERVAL(1, DAY)
// In a case of other unit types, value should be string with appropriate format.
// For example: INTERVAL("10:08:50", HOUR_SECOND)
INTERVAL(15, SECOND)
INTERVAL("25:15:08.000100", HOUR_MICROSECOND)

// INTERVALd creates temporal interval from duration
INTERVALd(3*time.Minute+4*time.Second+5*time.Microsecond)

// INTERVALe creates new Interval type from expresion and unit type.
INTERVALe(Film.DurationInHours, HOUR)

There is also:

NULL
STAR (alias for *)

Column Types

Every sql builder table column belongs to one expression type. There are following column types:

ColumnBool
ColumnInteger
ColumnFloat
ColumnString
ColumnDate
ColumnTime
ColumnTimez
ColumnTimestamp
ColumnTimestampz

Columns and literals can form arbitrary expressions but have to follow valid SQL expression syntax. For instance valid expressions are:

Bool(true).AND(Bool(false)).IS_FALSE()
(table.Film.Length.GT(Int(100)).AND(table.Film.Length.LT(Int(200))).IS_TRUE()

Some of the invalid expressions. These expressions will cause go build to break.

Bool(true).ADD(Int(11))        // can't add bool and integer
Int(11).LIKE(Float(22.2))      // integer expressions doesn't have LIKE method

Comparision Operators

Jet supports following comparison operators for all expression types:

Method Example Generated sql
EQ Int(1).EQ(table.Film.Length) 1 = film.length
NOT_EQ Int(1).EQ(table.Film.Length) 1 != film.length
IS_DISTINCT_FROM Int(1).IS_DISTINCT_FROM(table.Film.Length) PostgreSQL: 1 IS DISTINCT FROM film.length, MySQL: 1 <=> film.length
IS_NOT_DISTINCT_FROM Int(1).IS_NOT_DISTINCT_FROM(table.Film.Length) PostgreSQL: 1 IS NOT DISTINCT FROM film.length MySQL: NOT(1 <=> film.length)
LT Int(1).LT(table.Film.Length) 1 < film.length
LT_EQ Int(1).LT_EQ(table.Film.Length) 1 <= film.length
GT Int(1).GT(table.Film.Length) 1 > film.length
GT_EQ Int(1).GT_EQ(table.Film.Length) 1 >= film.length

Left-hand side and right-hand side of operators have to be of the same type

Arithmetic Operators

Following arithmetic operators are supported for integer and float expressions. If the first argument is float expression, second argument can be integer or float expression. If the first argument is integer expression second argument can only be integer expression.

Method Example Generated sql
ADD Int(1).ADD(table.Film.Length) 1 + film.length
SUB Float(1.11).SUB(Int(1)) 1.11 + 1
MUL Int(1).MUL(table.Film.Length) 1 * film.length
DIV Float(1.11).DIV(Float(3.33) 1.11 / 3.33
MOD Int(10).MOD(table.Film.Length) 10 % film.length
POW Float(10.01).POW(table.Film.Length) POW(film.length, 10.01)

Bit Operators

Following operators are only available on integer expressions:

Method Example Generated sql
BIT_AND Int(11).BIT_AND(table.Film.Length) 11 & film.length
BIT_OR Int(11).BIT_OR(table.Film.Length) 11 | film.length
BIT_XOR Int(11).BIT_XOR(table.Film.Length) PostgreSQL: 11 # film.length, MySQL: 11 ^ film.length
BIT_NOT BIT_NOT(table.Film.Length) ~ film.length
BIT_SHIFT_LEFT Int(11).BIT_SHIFT_LEFT(table.Film.Length) 11 >> film.length
BIT_SHIFT_RIGHT Int(11).BIT_SHIFT_RIGHT(table.Film.Length) 11 >> film.length

Logical Operators

Following operators are only available on boolean expressions:

Method Example Generated sql
IS_TRUE table.Staff.Active.IS_TRUE() staff.active IS TRUE
IS_NOT_TRUE (table.Staff.Active.AND(Bool(true))).IS_NOT_TRUE() (staff.active AND true) IS NOT TRUE
IS_FALSE Bool(false).IS_FALSE() false IS FALSE
IS_NOT_FALSE Bool(true).IS_NOT_FALSE() true IS NOT FALSE
IS_UNKNOWN table.Staff.Active.IS_UNKNOWN() staff.active IS UNKNOWN
IS_NOT_UNKNOWN table.Staff.Active.IS_NOT_UNKNOWN() staff.active IS NOT UNKNOWN

String Operators

Following operators are only available on string expressions:

Method Example Generated sql
CONCAT table.Film.Name.CONCAT(table.Film.Description) film.name || film.description
LIKE table.Film.Name.LIKE(String("%Wind%")) film.name LIKE %Wind%
NOT_LIKE table.Film.Name.NOT_LIKE(String("%Wind%")) staff.active NOT LIKE %Wind%
REGEXP_LIKE table.Film.Name.REGEXP_LIKE(String("^Wind")) PostgreSQL: staff.active ~* ^Wind, MySQL: staff.active REGEXP ^Wind
NOT_REGEXP_LIKE table.Film.Name.NOT_REGEXP_LIKE(String("^Wind")) PostgreSQL: staff.active !~* ^Wind, MySQL: staff.active NOT REGEXP ^Wind

Row expression

A ROW constructor creates expression that builds a row value using values for its member fields.

SELECT(Actor.AllColumns).
FROM(Actor).
WHERE(
    ROW(Actor.ActorID, Actor.FirstName).IN(
                ROW(Int(1), String("Joe")),
                ROW(Int(2), String("Nick")),
            ),
)

SQL Cast Operators

Cast operators allow expressions to be casted to some other database type. SQL builder expression type changes accordingly to database type.

Method Example PostgreSQL generated sql
CAST(exp).AS_BOOL() CAST(table.Film.Description).AS_BOOL() film.description::boolean
CAST(exp).AS_SMALLINT() CAST(table.Film.Description).AS_SMALLINT() film.description::smallint
CAST(exp).AS_INTEGER() CAST(table.Film.Description).AS_INTEGER() film.description::integer
CAST(exp).AS_BIGINT() CAST(table.Film.Description).AS_BIGINT() film.description::bigint
CAST(exp).AS_NUMERIC() CAST(table.Film.Description).AS_NUMERIC(10, 6) film.description::numeric(10,6)
CAST(exp).AS_REAL() CAST(table.Film.Description).AS_REAL() film.description::real
CAST(exp).AS_DOUBLE() CAST(table.Film.Description).AS_DOUBLE() film.description::double
CAST(exp).AS_TEXT() CAST(table.Film.Description).AS_TEXT() film.description::text
CAST(exp).AS_DATE() CAST(table.Film.Description).AS_DATE() film.description::date
CAST(exp).AS_TIME() CAST(table.Film.Description).AS_TIME() film.description::time without time zone
CAST(exp).AS_TIMEZ() CAST(table.Film.Description).AS_TIMEZ() film.description::time with time zone
CAST(exp).AS_TIMESTAMP() CAST(table.Film.Description).AS_TIMESTAMP() film.description::timestamp without time zone
CAST(exp).AS_TIMESTAMPZ() CAST(table.Film.Description).AS_TIMESTAMPZ() film.description::timestamp with time zone

MySQL and MariaDB generates SQL in a form: CAST(exp AS integer)

SQL Builder Cast Wrapper

For some expressions sql builder can't deduce expression type directly. For instance scalar sub-query:

    ( SELECT(MAXf(Film.RentalRate)).
      FROM(Film)                    ).LT(Float(11.1))

This expression would not compile, because sub-query, although calculates one scalar float value, it is not a float expression. To fix this sub-query can be cast to some float type, or just wrapped as float expression:

    FloatExp( SELECT(MAXf(Film.RentalRate)).
              FROM(Film)                    ).LT(Float(11.1))

There are wrappers for all supported types:

 - BoolExp(exp)
 - IntExp(exp)
 - FloatExp(exp)
 - StringExp(exp)
 - DateExp(exp)
 - TimeExp(exp)
 - TimezExp(exp)
 - TimestampExp(exp)
 - TimestampzExp(exp)

Cast wrapper does NOT inject cast operator to generated SQL.

Raw Operator

Raw function can be used for any of the unsupported functions, operators or expressions. For example:

Raw("current_database()")
Raw("(#duration + film.duration) / $arg", RawArg{"#duration": 11, "$arg": 200})

RawArgs contains named arguments for a placeholders in raw statement query. Named arguments naming convention does not have to follow any format, it just have to match named arguments exactly in the raw query. It is recommended NOT to use ($1, $2, ...) for postgres queries.

Raw can be casted or wrapped to desired expression type, or helper function can be used RawInt, RawFloat, etc...

Clone this wiki locally