Skip to content

FAQ

Jump to bottom
Karl Blomster edited this page Apr 2, 2024 · 32 revisions

Contents

How to execute jet statement in SQL transaction?

tx, err := db.Begin()
...
stmt := SELECT(...)

var dest Dest
err = stmt.QueryContext(ctx, tx, &dest) // or stmt.ExecContext(ctx, tx) 
...
tx.Commit()

More information about statement execution can be found here.

How to construct dynamic projection list?

var request struct {
	ColumnsToSelect []string
	ShowFullName    bool
}
// ...

var projectionList ProjectionList

for _, columnName := range request.ColumnsToSelect {
	switch columnName {
	case Customer.CustomerID.Name():
		projectionList = append(projectionList, Customer.CustomerID)
	case Customer.Email.Name():
		projectionList = append(projectionList, Customer.Email)
	case Customer.CreateDate.Name():
		projectionList = append(projectionList, Customer.CreateDate)
	}
}

if request.ShowFullName {
	projectionList = append(projectionList, Customer.FirstName.CONCAT(Customer.LastName))
}

stmt := SELECT(projectionList).
	FROM(Customer).
	LIMIT(3)

How to construct dynamic condition?

var request struct {
	CustomerID *int64
	Email      *string
	Active     *bool
}

// ....

condition := Bool(true)

if request.CustomerID != nil {
	condition = condition.AND(Customer.CustomerID.EQ(Int(*request.CustomerID)))
}
if request.Email != nil {
	condition = condition.AND(Customer.Email.EQ(String(*request.Email)))
}
if request.Active != nil {
	condition = condition.AND(Customer.Activebool.EQ(Bool(*request.Active)))
}

stmt := SELECT(Customer.AllColumns).
	FROM(Customer).
	WHERE(condition)

How to use jet in multi-tenant environment?

SQL Builder default targeted schema can be changed using FromSchema method:

multiTenant1 :=
        SELECT(Artist.AllColumns).
	FROM(Artists)                    // default schema/database "chinook"
	ORDER_BY(Artist.ArtistId).
	LIMIT(10)

Artist2 := Artist.FromSchema("chinook2") // the same generated SQL builder type used for different schema/database

multiTenant2 := 
	SELECT(Artist2.AllColumns).
	FROM(Artist2).
	ORDER_BY(Artist2.ArtistId).
	LIMIT(10)

Alternatively, each table and view default schema can be changed using global UseSchema method:

table.UseSchema("chinook2")
view.UseSchema("chinook2")

How to change model field type?

Generator customization

Generator will by default represents, exact decimal types (DECIMAL and NUMERIC) as float64 fields in the model types. This can lead to loss of precision during query result mapping.

To overcome this issue, we need to create a new type to store decimal values, and then instruct generator to use this new type instead of default float64.

New type has to implement sql.Serializer and sql.Valuer interface.

type MoneyType int64  // or some other representation 

func (m *MoneyType) Scan(value interface{}) error {   // value is string 
 ...  add implementation
}

func (m MoneyType) Value() (driver.Value, error) {
 ...  add implementation
}

Wrap generated types

Similar behavior can be achieved without customizing a generator. For instance, assuming table name is my_table, and my_table has a column money of the type NUMERIC.

We can create a new custom model type, by wrapping generated MyTable type.

type MyTable struct {
    model.MyTable         // MyTable.Money will contain float64 value
    Money MoneyType       // will contains exact decimal value
}

New MyTable type can be used in any place model.MyTable is used, as a QueryContext destination or as a model for INSERT or UPDATE statements.

It is also possible to use some of the existing third party decimal libraries:

import "github.com/shopspring/decimal"

type MyTable struct {
    model.MyTable
    Money decimal.Decimal  
}

How to call custom or currently unsupported SQL function?

Lets say our database contains following SQL function:

create function get_film_count(len_from int, len_to int)
...

Developer can define utility function:

func GET_FILM_COUNT(lenFrom, lenTo IntegerExpression) IntegerExpression { //or (lenFrom, lenTo int) if there is no need to pass a column as a parameter
	return IntExp(Func("dvds.get_film_count", lenFrom, lenTo))
}

And now, this new function can be called directly from the SQL query:

stmt := SELECT(
	GET_FILM_COUNT(Int(100), Int(120)).AS("film_count"),
)

The same affect can be achieved using Raw expression method:

stmt2 := SELECT(
	Raw("dvds.get_film_count(#1, #2)", RawArgs{"#1": 100, "#2": 120}).AS("film_count"),
)

With the loss of all jet benefits, the entire statement can also be written as a raw query, using RawStatement method:

stmt3 := RawStatement(`
	SELECT dvds.get_film_count(#1, #2) AS "film_count";`, RawArgs{"#1": 100, "#2": 120},
)

How to use custom or currently unsupported SQL operators?

Much like custom or unsupported functions, binary operators (that is, operators that take two operands) can be defined as utility functions using BinaryOperator. For example, say our DBMS has a case-insensitive LIKE operator called ILIKE. Then we can define a utility function like this:

func ILIKE(lhs, rhs StringExpression) BoolExpression {
	return BoolExp(BinaryOperator(lhs, rhs, "ILIKE"))
}

And use it like so:

stmt := SELECT(
	ILIKE(String("foo%"), String("FOOFoo")).AS("starts_with_foo"))

At the moment, there is no public API for wrapping unary or ternary operators in this manner, but feel free to submit a feature request issue. In the meantime, you can always fall back on Raw or RawStatement.

How to use IN/NOT_IN with dynamic list of values?

import (
	. "myapp/table"
	. "github.com/go-jet/jet/v2/sqlite"
)

func demo() {
	var userIDs = []int64{1, 2, 3}         // dynamic list, could be user provided
	var sqlIDs []Expression                // !!! sqlite.Expression list !!! 

	for _, userID := range userIDs {
		sqlIDs = append(sqlIDs, Int(userID))
	}

	SELECT(
		Users.AllColumns,
	).FROM(
		Users,
	).WHERE(
		Users.UserID.IN(sqlIDs...),
	)

	...
}

For tuple comparison use ROW method.

ROW(Users.UserID, Users.Name).IN(
    ROW(Int(1), String("John")),
    ROW(Int(2), String("Mike")),
)

Note that sqlIDs must be of type []Expression or the compilation will fail.
This is a bug, and it will be fixed in version V3. IN/NOT_IN right argument should match the type of the left argument.

Scan stopped working after naming a destination type

Usually developers start with a query that looks something like this:

stmt := SELECT(
	Payment.PaymentID,                    // no need to alias, "payment.payment_id" alias is added automatically
	MAX(Payment.Amount).AS("max_amount"), // alias equivalent to ".max_amount"
).FROM(
	Payment,
).WHERE(
	Payment.CustomerID.EQ(Int(101)),
).GROUP_BY(
	Payment.PaymentID,
)

var dest struct {
	model.Payment
	MaxAmount int
}

err := stmt.QueryContext(ctx, db, &dest)

This scan will work for MaxAmount field, because there is a valid mapping between alias and destination struct field.
Alias(.max_amount) => Field(.MaxAmount). Note that destination is anonymous type.

After naming the destination type, this mapping is broken.

type MyStruct struct {
	model.Payment
	MaxAmount int
}

var dest MyStruct

Alias(.max_amount) =| Field(MyStruct.MaxAmount)

Now, the scan will not work. To fix it we need to update query alias:

SELECT(
	Payment.PaymentID,                               // still no need to alias
	MAX(Payment.Amount).AS("my_struct.max_amount"),  // ".max_amount" -> "my_struct.max_amount"
).FROM(
 	Payment,
...

The same logic would apply if dest is slice of anonymous types.

Clone this wiki locally