断言

字段断言

• 布尔类型：
  • \=, !=
• 数字类型：
  • \=, !=, >, <, >=, <=,
  • IN, NOT IN
• 时间类型：
  • \=, !=, >, <, >=, <=
  • IN, NOT IN
• 字符类型：
  • \=, !=, >, <, >=, <=
  • IN, NOT IN
  • Contains, HasPrefix, HasSuffix
  • ContainsFold, EqualFold （只能用于 SQL 语句）
• JSON类型：
  • \=, !=
  • \=, !=, >, <, >=, <= on nested values (JSON path).
  • Contains （只能用于包含嵌套的 JSON path 表达式）
  • HasKey, Len<P>
• 可选字段:
  • IsNil, NotNil

Edge 断言

• HasEdge. 例如，对于一个 Pet 类型的 edge 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)

自定义断言

Custom predicates can be useful if you want to write your own dialect-specific logic or to control the executed queries.

Get all pets of users 1, 2 and 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)

Count the number of users whose JSON field named URL contains the Scheme key

count := client.User.
    Query().
    Where(func(s *sql.Selector) {
        s.Where(sqljson.HasKey(user.FieldURL, sqljson.Path("Scheme")))
    }).
    CountX(ctx)

The above code will produce the following SQL query:

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

Get all users with a "Tesla" car

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)

The above code will produce the following SQL query:

-- `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%'