Support `WHERE "column" = ANY($1)`

[yz89122]

Is your feature request related to a problem? Please describe. A clear and concise description of what the problem is. Ex. I'm always frustrated when [...]

As title, support the following query

SELECT *
FROM "user"
WHERE "id" = ANY($1); -- $1 is array

Currently, I need to achieve this query by using postgres.RawBool().

var ids []uuid.UUID

postgres.
    SELECT(model.User.AllColumns).
    FROM(model.User).
    WHERE(eqAny(model.User.ID, ids))

func eqAny(column jet.Column, values any) postgres.BoolExpression {
    return postgres.RawBool(fmt.Sprintf(`"%s".%s = ANY(#values)`, column.TableName(), column.Name()), jet.RawArgs{"#values": values})
}

Although the above query can be achieved through IN ($1, $2, ...), for dynamic length array, it'll generate numbers of different queries for IN ($1), IN ($1, $2) and so on. For databases there're different queries, that's not a good thing for databases.

I.g.

SQL generated:

For len(ids) = 1:

SELECT *
FROM "user"
WHERE "id" IN ($1);

For len(ids) = 2:

SELECT *
FROM "user"
WHERE "id" IN ($1, $2);

For len(ids) = 3:

SELECT *
FROM "user"
WHERE "id" IN ($1, $2, $3);

And so on.

Describe the solution you'd like A clear and concise description of what you want to happen.

I hope there's a method .EQ_ANY() for columns.

I.g.

var ids []uuid.UUID

postgres.
    SELECT(model.User.AllColumns).
    FROM(model.User).
    WHERE(model.User.ID.EQ_ANY(ids))

[houten11]

Check the wiki: https://github.com/go-jet/jet/wiki/FAQ#how-to-use-innot_in-with-dynamic-list-of-values.

[yz89122]

Hi @houten11, sorry for the poor described issue. I've checked the wiki. It do achieve the WHERE IN. But like I mentioned, this method generate one queries for each length of array. I.g. your array input could have length between 1 to 1000, Table.Column.IN(array...) will generate 1000 different queries. Which means, that's 1000 different queries for database. The database cannot reuse SQL query compile caches.

For array with length 1 (with WHERE IN):

WHERE "id" IN ($1)

for 2:

WHERE "id" IN ($1, $2);

for 3:

WHERE "id" IN ($1, $2, $3);

And so on.

For array with arbitrary length (with = ANY()):

WHERE "id" = ANY($1);

[houten11]

Sorry, skimmed over the issue description on the first read. Yeah, ANY is not supported but you can add a custom support:

func ANY(expr Expression) Expression {
    return Func("ANY", expr)
}

This function now can be used as part of query:

User.ID.EQ(IntExp(ANY(
    Raw("#1", RawArgs{"#1": pq.Int32Array{1, 2, 3, 4}}), // arrays are not supported, so we have to use Raw
))),

or

User.ID.EQ(IntExp(ANY(
    SELECT(...)...
))),

[yz89122]

Hi @houten11, thanks for the solution. But is it possible to add a .EQ_ANY() (Or some sort of helper function) for each column type? IMHO, = ANY() is a common operation, it's often used with prepared statement.

With User.ID.EQ(IntExp(ANY())), I need to write XxExp() for each type, I.g. I need to write another one for string type Table1.StrID.EQ(StrExp(ANY())) and so on. I cannot just write Table2.AnyTypeOfID.EQ(ANY()).

[go-jet]

Hi @yz89122,

I cannot just write Table2.AnyTypeOfID.EQ(ANY()).

jet is a type safe library, so the types needs to match.

But is it possible to add a .EQ_ANY()

If we add EQ_ANY, we would also need to add, for each of the types, NOT_EQ_ANY, LT_ANY, LT_EQ_ANY, etc... This would lead to number of methods explosion. Also note that ANY can accept either array or subquery.

In some cases we can avoid writing XxxExp, because Go has generics now. But in the case of ANY, it might not be possible because ANY can accept subquery as well.

If you want to avoid XxxExp, you can add additional ANY methods for each type.

func ANYi(exp Expression) IntExpression {
    return IntExp(ANY(exp))
}

func ANYs(exp Expression) StringExpression {
    return StringExp(ANY(exp))
}

You can also wrap array constructor in a new method:

func ARRAY(elems ...any) Expression {
   return Raw("#1", RawArgs{"#1": pq.Array(elem)}),
}

Now you can write:

User.ID.EQ(ANYi(ARRAY(1, 2, 3, 4))

User.Name.EQ(ANYs(ARRAY("John", "Mike", "Tod")))

[yz89122]

Hi @go-jet, one additional information, ANY, SOME and ALL seem like they're operators, not function. https://www.postgresql.org/docs/current/functions-comparisons.html

One more question: Is it possible to add postgres.ANY() to this library? I.g.

postgres.
  SELECT(table.User.AllColumns).
  FROM(table.User).
  WHERE(table.User.ID.EQ(postgres.ANY(ids)))

Currently, the postgres.ColumnString contains un-exported methods and interfaces, so I cannot create my own universal ANY() function for all types of columns.

[go-jet]

ANY, SOME and ALL seem like they're operators

Actually, Strictly speaking, IN and ANY are Postgres "constructs" or "syntax elements", rather than "operators". In SQL they appear as function, so make sense to model as function.

One more question: Is it possible to add postgres.ANY() to this library?

Eventually it will be introduced as part of array support.