Skip to content

FAQ

Jump to bottom
go-jet edited this page Mar 23, 2023 · 37 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?

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)

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