メインコンテンツへスキップする

述語

フィールドの述語

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