Unable to use `.IN` and `.NOT_IN` with dynamic values

[philippta]

Describe the bug

• .IN(...) and .NOT_IN(...) can not be used in conjuction with a slice of values (Int, Float, String, etc).
• The Expression type as well as Int, Float, String, etc. are living within the internal package, thus creating a slice of e.g. jet.IntegerExpression is not possible.

Environment (please complete the following information):

• OS: macosx
• Database: sqlite
  • Database driver: github.com/mattn/go-sqlite3
• Jet version: 2.8.0

Code snippet

import (
    . "myapp/table"

    "github.com/go-jet/jet/v2/internal/jet" // for jet.IntegerExpression, fails to compile
    . "github.com/go-jet/jet/v2/sqlite"
)

func demo() {
    var userIDs = []int64{1, 2, 3} // could be user provided
    var sqlIDs []jet.IntegerExpression

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

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

Compilation error

use of internal package github.com/go-jet/jet/v2/internal/jet not allowed

Expected behavior Defining a slice of jet.IntegerExpression should not result in a compilation error.

[go-jet]

Hi @philippta . All the internal types of interest are exported through dialect package(for instance). There is no need to use internal package. In this case you should only import github.com/go-jet/jet/v2/sqlite. IN/NOT_IN method does not accepts list of IntegerExpression, but list of Expression objects. This is a bug, but it can't be fixed before V3, because it is a part of the public API.

import (
    . "myapp/table"

    . "github.com/go-jet/jet/v2/sqlite"  // !!! Always import only the dialect package !!!
)

func demo() {
    var userIDs = []int64{1, 2, 3}         // 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...))
}

[philippta]

Thanks for clarification. This definitely helps solving my problem. Maybe it‘s worth adding this example to the wiki.

Issue can be closed then.

[go-jet]

wiki

[josephbuchma]

Some generic helpers:

// Strings converts slice of strings into slice of mysql.Expression,
// useful for IN queries.
func Strings[T ~string](s []T) []mysql.Expression {
    ret := make([]mysql.Expression, 0, len(s))
    for _, v := range s {
        ret = append(ret, mysql.Expression(mysql.String(string(v))))
    }
    return ret
}

// Integers converts slice of integers into slice of mysql.Expression,
// useful for IN queries.
func Integers[T constraints.Signed](s []T) []mysql.Expression {
    ret := make([]mysql.Expression, 0, len(s))
    for _, v := range s {
        ret = append(ret, mysql.Expression(mysql.Int(int64(v))))
    }
    return ret
}

Example usage:

import (
    . "myapp/table"

    . "github.com/go-jet/jet/v2/mysql"  // !!! Always import only the dialect package !!!
)

func demo() {
    var userIDs = []int64{1, 2, 3}         // could be user provided
    SELECT(Users.AllColumns).FROM(Users).WHERE(Users.UserID.IN(Integers(userIDs)...))
}

@go-jet do you think something like that can be a part of jet API?