述語
フィールドの述語
- Bool:
- =, !=
- Numeric:
- =, !=, >, <, >=, <=,
- IN, NOT IN
- Time:
- =, !=, >, <, >=, <=
- IN, NOT IN
- String:
- =, !=, >, <, >=, <=
- IN, NOT IN
- Contains, HasPrefix, HasSuffix
- ContainsFold, EqualFold (SQL 固有)
- JSON
- =, !=
- =, !=, >, <, >=, <= on nested values (JSON path).
- Contains (ネストしたJSON)
- HasKey, Len<P>
null
checks for nested values (JSON path).
- Optional fields:
- IsNil, NotNil
エッジ述語
HasEdge. たとえば、
pet
型が持つowner
というエッジでは、以下のように用います:client.Pet.
Query().
Where(pet.HasOwner()).
All(ctx)HasEdgeWith. エッジのフィールドに述語のリストを追加します。
client.Pet.
Query().
Where(pet.HasOwnerWith(user.Name("a8m"))).
All(ctx)
論理否定 (NOT)
client.Pet.
Query().
Where(pet.Not(pet.NameHasPrefix("Ari"))).
All(ctx)
論理和 (OR)
client.Pet.
Query().
Where(
pet.Or(
pet.HasOwner(),
pet.Not(pet.HasFriends()),
)
).
All(ctx)
論理積 (AND)
client.Pet.
Query().
Where(
pet.And(
pet.HasOwner(),
pet.Not(pet.HasFriends()),
)
).
All(ctx)
カスタム述語
独自の方言固有のロジックを書く場合や、実行されるクエリを制御する場合に、カスタム述語は役に立ちます。
Idが1、2、3のペットを全て取得しましょう
pets := client.Pet.
Query().
Where(func(s *sql.Selector) {
s.Where(sql.InInts(pet.FieldOwnerID, 1, 2, 3))
}).
AllX(ctx)
The above code will produce the following SQL query:
SELECT DISTINCT `pets`.`id`, `pets`.`owner_id` FROM `pets` WHERE `owner_id` IN (1, 2, 3)
URL
という名前の JSON フィールドに Scheme
キーが含まれているユーザーを数えます。
count := client.User.
Query().
Where(func(s *sql.Selector) {
s.Where(sqljson.HasKey(user.FieldURL, sqljson.Path("Scheme")))
}).
CountX(ctx)
上記のコードは、次の SQL クエリを生成します:
-- PostgreSQL
SELECT COUNT(DISTINCT "users"."id") FROM "users" WHERE "url"->'Scheme' IS NOT NULL
-- SQLite and MySQL
SELECT COUNT(DISTINCT `users`.`id`) FROM `users` WHERE JSON_EXTRACT(`url`, "$.Scheme") IS NOT NULL
"Tesla"
車を持つすべてのユーザーを取得
Consider an ent query such as:
users := client.User.Query().
Where(user.HasCarWith(car.Model("Tesla"))).
AllX(ctx)
This query can be rephrased in 3 different forms: IN
, EXISTS
and JOIN
.
// `IN` version.
users := client.User.Query().
Where(func(s *sql.Selector) {
t := sql.Table(car.Table)
s.Where(
sql.In(
s.C(user.FieldID),
sql.Select(t.C(user.FieldID)).From(t).Where(sql.EQ(t.C(car.FieldModel), "Tesla")),
),
)
}).
AllX(ctx)
// `JOIN` version.
users := client.User.Query().
Where(func(s *sql.Selector) {
t := sql.Table(car.Table)
s.Join(t).On(s.C(user.FieldID), t.C(car.FieldOwnerID))
s.Where(sql.EQ(t.C(car.FieldModel), "Tesla"))
}).
AllX(ctx)
// `EXISTS` version.
users := client.User.Query().
Where(func(s *sql.Selector) {
t := sql.Table(car.Table)
p := sql.And(
sql.EQ(t.C(car.FieldModel), "Tesla"),
sql.ColumnsEQ(s.C(user.FieldID), t.C(car.FieldOwnerID)),
)
s.Where(sql.Exists(sql.Select().From(t).Where(p)))
}).
AllX(ctx)
上記のコードは、次の SQL クエリを生成します:
-- `IN` version.
SELECT DISTINCT `users`.`id`, `users`.`age`, `users`.`name` FROM `users` WHERE `users`.`id` IN (SELECT `cars`.`id` FROM `cars` WHERE `cars`.`model` = 'Tesla')
-- `JOIN` version.
SELECT DISTINCT `users`.`id`, `users`.`age`, `users`.`name` FROM `users` JOIN `cars` ON `users`.`id` = `cars`.`owner_id` WHERE `cars`.`model` = 'Tesla'
-- `EXISTS` version.
SELECT DISTINCT `users`.`id`, `users`.`age`, `users`.`name` FROM `users` WHERE EXISTS (SELECT * FROM `cars` WHERE `cars`.`model` = 'Tesla' AND `users`.`id` = `cars`.`owner_id`)
Get all pets where pet name contains a specific pattern
The generated code provides the HasPrefix
, HasSuffix
, Contains
, and ContainsFold
predicates for pattern matching. However, in order to use the LIKE
operator with a custom pattern, use the following example.
pets := client.Pet.Query().
Where(func(s *sql.Selector){
s.Where(sql.Like(pet.Name,"_B%"))
}).
AllX(ctx)
The above code will produce the following SQL query:
SELECT DISTINCT `pets`.`id`, `pets`.`owner_id`, `pets`.`name`, `pets`.`age`, `pets`.`species` FROM `pets` WHERE `name` LIKE '_B%'
Custom SQL functions
In order to use built-in SQL functions such as DATE()
, use one of the following options:
1. Pass a dialect-aware predicate function using the sql.P
option:
users := client.User.Query().
Select(user.FieldID).
Where(func(s *sql.Selector) {
s.Where(sql.P(func(b *sql.Builder) {
b.WriteString("DATE(").Ident("last_login_at").WriteByte(')').WriteOp(OpGTE).Arg(value)
}))
}).
AllX(ctx)
The above code will produce the following SQL query:
SELECT `id` FROM `users` WHERE DATE(`last_login_at`) >= ?
2. Inline a predicate expression using the ExprP()
option:
users := client.User.Query().
Select(user.FieldID).
Where(func(s *sql.Selector) {
s.Where(sql.ExprP("DATE(last_login_at) >= ?", value))
}).
AllX(ctx)
The above code will produce the same SQL query:
SELECT `id` FROM `users` WHERE DATE(`last_login_at`) >= ?
JSON predicates
JSON predicates are not generated by default as part of the code generation. However, ent provides an official package named sqljson
for applying predicates on JSON columns using the custom predicates option.
Compare a JSON value
sqljson.ValueEQ(user.FieldData, data)
sqljson.ValueEQ(user.FieldURL, "https", sqljson.Path("Scheme"))
sqljson.ValueNEQ(user.FieldData, content, sqljson.DotPath("attributes[1].body.content"))
sqljson.ValueGTE(user.FieldData, status.StatusBadRequest, sqljson.Path("response", "status"))
Check for the presence of a JSON key
sqljson.HasKey(user.FieldData, sqljson.Path("attributes", "[1]", "body"))
sqljson.HasKey(user.FieldData, sqljson.DotPath("attributes[1].body"))
Note that, a key with the null
literal as a value also matches this operation.
Check JSON null
literals
sqljson.ValueIsNull(user.FieldData)
sqljson.ValueIsNull(user.FieldData, sqljson.Path("attributes"))
sqljson.ValueIsNull(user.FieldData, sqljson.DotPath("attributes[1].body"))
Note that, the ValueIsNull
returns true if the value is JSON null
, but not database NULL
.
Compare the length of a JSON array
sqljson.LenEQ(user.FieldAttrs, 2)
sql.Or(
sqljson.LenGT(user.FieldData, 10, sqljson.Path("attributes")),
sqljson.LenLT(user.FieldData, 20, sqljson.Path("attributes")),
)
Check if a JSON value contains another value
sqljson.ValueContains(user.FieldData, data)
sqljson.ValueContains(user.FieldData, attrs, sqljson.Path("attributes"))
sqljson.ValueContains(user.FieldData, code, sqljson.DotPath("attributes[0].status_code"))
Check if a JSON string value contains a given substring or has a given suffix or prefix
sqljson.StringContains(user.FieldURL, "github", sqljson.Path("host"))
sqljson.StringHasSuffix(user.FieldURL, ".com", sqljson.Path("host"))
sqljson.StringHasPrefix(user.FieldData, "20", sqljson.DotPath("attributes[0].status_code"))
Check if a JSON value is equal to any of the values in a list
sqljson.ValueIn(user.FieldURL, []any{"https", "ftp"}, sqljson.Path("Scheme"))
sqljson.ValueNotIn(user.FieldURL, []any{"github", "gitlab"}, sqljson.Path("Host"))
Comparing Fields
The dialect/sql
package provides a set of comparison functions that can be used to compare fields in a query.
client.Order.Query().
Where(
sql.FieldsEQ(order.FieldTotal, order.FieldTax),
sql.FieldsNEQ(order.FieldTotal, order.FieldDiscount),
).
All(ctx)
client.Order.Query().
Where(
order.Or(
sql.FieldsGT(order.FieldTotal, order.FieldTax),
sql.FieldsLT(order.FieldTotal, order.FieldDiscount),
),
).
All(ctx)