Comments (7)
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.
from jet.
Check the wiki: https://github.com/go-jet/jet/wiki/FAQ#how-to-use-innot_in-with-dynamic-list-of-values.
from jet.
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);
from jet.
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(...)...
))),
from jet.
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())
.
from 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")))
from jet.
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.
from jet.
Related Issues (20)
- Add support for prepared statement HOT 11
- MySQL: A utility function for comparision of UUID fields HOT 2
- Issues with Query Result Mapper HOT 5
- Alias is broken or I'm doing something wrong HOT 7
- Multiple dynamic WHERE statements HOT 1
- Small typo in wiki HOT 1
- Postgis geography type shows as `postgres.ColumnString` HOT 3
- More convenient way to implement raw/custom operators HOT 5
- Should return value with return type HOT 1
- Support `WHERE()` on `INSERT ON CONFLICT DO UPDATE` HOT 2
- Support for SQLite FTS5? HOT 2
- Missing struct fields in SELECT due to Postgres truncation of identifiers HOT 4
- Unsupported sql column for the array in postgres HOT 2
- Incorrect Docs: Generator Customization references invalid interfaces HOT 5
- `Generate` function signature doesn't match documentation HOT 1
- Is there a way to make generic db functions for all models/tables? HOT 1
- Sqlite GENERATE columns, but being used in INSERT HOT 4
- Support for keyword arguments to functions HOT 3
- Support for filtered aggregations HOT 1
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from jet.