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

述語

フィールドの述語

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

上記のコードは、次の SQL クエリを生成します:

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" 車を持つすべてのユーザーを取得

以下のようなentのクエリを考えてみましょう:

users := client.User.Query().
Where(user.HasCarWith(car.Model("Tesla"))).
AllX(ctx)

このクエリは3つの異なる形式で繰り返すことができます:IN, EXISTS, 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%'

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