Skip to content

Expressions

Jump to bottom
go-jet edited this page Mar 17, 2024 · 38 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(tuple) expressions
  • Raw 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"))
Json(`{"firstName": "John", "lastName": "Doe"}`)
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(Actor.IsActive).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

Comparison Operators

Jet supports the following comparison operators for all expression types:

Method Example Debug 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) postgres: 1 IS DISTINCT FROM film.length, mysql: 1 <=> film.length
IS_NOT_DISTINCT_FROM Int(1).IS_NOT_DISTINCT_FROM(table.Film.Length) postgres: 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
BETWEEN Film.Length.BETWEEN(Int(100), Int(200)) film.length BETWEEN 100 AND 200
NOT_BETWEEN Film.Length.NOT_BETWEEN(Int(50), Int(250)) film.length NOT BETWEEN 100 AND 200

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 Debug 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 with integer expressions:

Method Example Debug 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 with boolean expressions:

Method Example Debug 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 Staff.Active.IS_UNKNOWN() staff.active IS UNKNOWN
IS_NOT_UNKNOWN Staff.Active.IS_NOT_UNKNOWN() staff.active IS NOT UNKNOWN
AND Staff.Active.AND(Account.Active) staff.active AND account.active
OR Staff.Active.OR(Account.Active) staff.active OR account.active

There are also global functions AND and OR, which can be used for better indentation of a complex condition in the Go code and in the generated SQL.

String Operators

Following operators are only available with string expressions:

Method Example Debug 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:

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

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:

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

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.

Row(tuple) expressions

ROW constructor can be used to build a row value (or a tuple) using expressions provided as parameters.

ROW(Actor.ActorID, Actor.FirstName, Int(0)) //--sql--> ROW(actor.actor_id, actor.first_name, 0);

Raw Expression

Raw expression 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