ent 基本使用十五 一个图遍历的例子

以下是来自官方的一个user group pet 的查询demo

参考关系图

 

 

 

环境准备

  • docker-compose mysql 环境
version: "3"
services:
  mysql:
    image: mysql:5.7.16
    ports:
      - 3306:3306
    command: --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci
    environment:
      MYSQL_ROOT_PASSWORD: dalongrong
      MYSQL_DATABASE: gogs
      MYSQL_USER: gogs
      MYSQL_PASSWORD: dalongrong
      TZ: Asia/Shanghai
 
 
  • golang mod 项目
go mod init github.com/rongfengliang/ent-pet-user-group

生成实体模型

  • 生成schema
entc init Pet User Group
  • 配置schema 字段以及边
    pets
package schema
​
import (
  "github.com/facebookincubator/ent"
  "github.com/facebookincubator/ent/schema/edge"
  "github.com/facebookincubator/ent/schema/field"
)
​
// Pet holds the schema definition for the Pet entity.
type Pet struct {
  ent.Schema
}
​
// Fields of the Pet.
func (Pet) Fields() []ent.Field {
  return []ent.Field{
    field.String("name"),
  }
}
​
// Edges of the Pet.
func (Pet) Edges() []ent.Edge {
  return []ent.Edge{
    edge.To("friends", Pet.Type),
    edge.From("owner", User.Type).
      Ref("pets").
      Unique(),
  }
}
 
 

user

package schema
​
import (
  "github.com/facebookincubator/ent"
  "github.com/facebookincubator/ent/schema/edge"
  "github.com/facebookincubator/ent/schema/field"
)
​
// User holds the schema definition for the User entity.
type User struct {
  ent.Schema
}
​
// Fields of the User.
func (User) Fields() []ent.Field {
  return []ent.Field{
    field.Int("age"),
    field.String("name"),
  }
}
​
// Edges of the User.
func (User) Edges() []ent.Edge {
  return []ent.Edge{
    edge.To("pets", Pet.Type),
    edge.To("friends", User.Type),
    edge.From("groups", Group.Type).
      Ref("users"),
    edge.From("manage", Group.Type).
      Ref("admin"),
  }
}
​
 
 

group

package schema
​
import (
  "github.com/facebookincubator/ent"
  "github.com/facebookincubator/ent/schema/edge"
  "github.com/facebookincubator/ent/schema/field"
)
​
// Group holds the schema definition for the Group entity.
type Group struct {
  ent.Schema
}
​
// Fields of the Group.
func (Group) Fields() []ent.Field {
  return []ent.Field{
    field.String("name"),
  }
}
​
// Edges of the Group.
func (Group) Edges() []ent.Edge {
  return []ent.Edge{
    edge.To("users", User.Type),
    edge.To("admin", User.Type).
      Unique(),
  }
}
 

生成边以及定点数据

package main
​
import (
  "context"
  "fmt"
  "log"
​
  _ "github.com/go-sql-driver/mysql"
​
  "github.com/rongfengliang/ent-pet-user-group/ent"
)
​
func main() {
  client, err := ent.Open("mysql", "root:dalongrong@tcp(127.0.0.1)/gogs")
  if err != nil {
    log.Fatalf("failed opening connection to sqlite: %v", err)
  }
  defer client.Close()
  ctx := context.Background()
  Gen(ctx, client.Debug())
}
​
// Gen for generate demo datas
func Gen(ctx context.Context, client *ent.Client) error {
  hub, err := client.Group.
    Create().
    SetName("Github").
    Save(ctx)
  if err != nil {
    return fmt.Errorf("failed creating the group: %v", err)
  }
  // Create the admin of the group.
  // Unlike `Save`, `SaveX` panics if an error occurs.
  dan := client.User.
    Create().
    SetAge(29).
    SetName("Dan").
    AddManage(hub).
    SaveX(ctx)
​
  // Create "Ariel" and its pets.
  a8m := client.User.
    Create().
    SetAge(30).
    SetName("Ariel").
    AddGroups(hub).
    AddFriends(dan).
    SaveX(ctx)
  pedro := client.Pet.
    Create().
    SetName("Pedro").
    SetOwner(a8m).
    SaveX(ctx)
  xabi := client.Pet.
    Create().
    SetName("Xabi").
    SetOwner(a8m).
    SaveX(ctx)
​
  // Create "Alex" and its pets.
  alex := client.User.
    Create().
    SetAge(37).
    SetName("Alex").
    SaveX(ctx)
  coco := client.Pet.
    Create().
    SetName("Coco").
    SetOwner(alex).
    AddFriends(pedro).
    SaveX(ctx)
​
  fmt.Println("Pets created:", pedro, xabi, coco)
  // Output:
  // Pets created: Pet(id=1, name=Pedro) Pet(id=2, name=Xabi) Pet(id=3, name=Coco)
  return nil
}
 

一些查询

为了方便查看生成的sql 我使用了debug，同时也可以开启慢查询

set global slow_query_log=1;
set global long_query_time=0;
  • 一个demo
    上面的遍历从一个Group实体开始，继续到其admin（边），继续到其friends（边），获取其pets（边），获取每个宠物的
    friends（边），并请求其所有者
    参考图

     

     

     

    代码：
 
func Traverse(ctx context.Context, client *ent.Client) error {
    owner, err := client.Group. // GroupClient.
        Query(). // Query builder.
        Where(group.Name("Github")). // Filter only Github group (only 1).
        QueryAdmin(). // Getting Dan.
        QueryFriends(). // Getting Dan's friends: [Ariel].
        QueryPets(). // Their pets: [Pedro, Xabi].
        QueryFriends(). // Pedro's friends: [Coco], Xabi's friends: [].
        QueryOwner(). // Coco's owner: Alex.
        Only(ctx) // Expect only one entity to return in the query.
    if err != nil {
        return fmt.Errorf("failed querying the owner: %v", err)
    }
    fmt.Println(owner)
    // Output:
    // User(id=3, age=37, name=Alex)
    return nil
}
 

效果：

2019/10/15 13:10:12 driver.Query: query=SELECT DISTINCT `users`.`id`, `users`.`age`, `users`.`name` FROM `users` JOIN (SELECT `pets`.`owner_id` FROM
 `pets` JOIN (SELECT `pet_friends`.`friend_id` FROM `pet_friends` JOIN (SELECT `pets`.`id` FROM `pets` JOIN (SELECT `users`.`id` FROM `users` JOIN (
SELECT `user_friends`.`friend_id` FROM `user_friends` JOIN (SELECT `users`.`id` FROM `users` JOIN (SELECT `groups`.`admin_id` FROM `groups` WHERE `g
roups`.`name` = ?) AS `t1` ON `users`.`id` = `t1`.`admin_id`) AS `t1` ON `user_friends`.`user_id` = `t1`.`id`) AS `t1` ON `users`.`id` = `t1`.`frien
d_id`) AS `t1` ON `pets`.`owner_id` = `t1`.`id`) AS `t1` ON `pet_friends`.`pet_id` = `t1`.`id`) AS `t1` ON `pets`.`id` = `t1`.`friend_id`) AS `t1` O
N `users`.`id` = `t1`.`owner_id` LIMIT ? args=[Github 2]
User(id=3, age=37, name=Alex)
 
 

sql 查询计划

EXPLAIN SELECT
    DISTINCT `users`.`id`,
    `users`.`age`,
    `users`.`name`
FROM
    `users`
    JOIN (
        SELECT
            `pets`.`owner_id`
        FROM
            `pets`
            JOIN (
                SELECT
                    `pet_friends`.`friend_id`
                FROM
                    `pet_friends`
                    JOIN (
                        SELECT
                            `pets`.`id`
                        FROM
                            `pets`
                            JOIN (
                                SELECT
                                    `users`.`id`
                                FROM
                                    `users`
                                    JOIN (
                                        SELECT
                                            `user_friends`.`friend_id`
                                        FROM
                                            `user_friends`
                                            JOIN (
                                                SELECT
                                                    `users`.`id`
                                                FROM
                                                    `users`
                                                    JOIN (
                                                        SELECT
                                                            `groups`.`admin_id`
                                                        FROM
                                                            `groups`
                                                        WHERE
                                                            `groups`.`name` = 'Github'
                                                    ) AS `t1` ON `users`.`id` = `t1`.`admin_id`
                                            ) AS `t1` ON `user_friends`.`user_id` = `t1`.`id`
                                    ) AS `t1` ON `users`.`id` = `t1`.`friend_id`
                            ) AS `t1` ON `pets`.`owner_id` = `t1`.`id`
                    ) AS `t1` ON `pet_friends`.`pet_id` = `t1`.`id`
            ) AS `t1` ON `pets`.`id` = `t1`.`friend_id`
    ) AS `t1` ON `users`.`id` = `t1`.`owner_id`
 
 

效果：

 

 

 

  • 另外一个demo
    我们要获取所有owner（edge）是friend 某个组admin（边）的宠物（实体）。 

     

     

func Traverse(ctx context.Context, client *ent.Client) error {
    pets, err := client.Pet.
        Query().
        Where(
            pet.HasOwnerWith(
                user.HasFriendsWith(
                    user.HasManage(),
                ),
            ),
        ).
        All(ctx)
    if err != nil {
        return fmt.Errorf("failed querying the pets: %v", err)
    }
    fmt.Println(pets)
    // Output:
    // [Pet(id=1, name=Pedro) Pet(id=2, name=Xabi)]
    return nil
}
 
 

效果

go run cmd/query2/main.go 
2019/10/15 13:18:47 driver.Query: query=SELECT DISTINCT `pets`.`id`, `pets`.`name` FROM `pets` WHERE `pets`.`owner_id` IN (SELECT `id` FROM `users` 
WHERE `users`.`id` IN (SELECT `user_friends`.`user_id` FROM `user_friends` JOIN `users` AS `t0` ON `user_friends`.`friend_id` = `t0`.`id` WHERE `t0`
.`id` IN (SELECT `admin_id` FROM `groups` WHERE `admin_id` IS NOT NULL))) args=[]
[Pet(id=1, name=Pedro) Pet(id=2, name=Xabi)]
 

查询计划

EXPLAIN SELECT
    DISTINCT `pets`.`id`,
    `pets`.`name`
FROM
    `pets`
WHERE
    `pets`.`owner_id` IN (
        SELECT
            `id`
        FROM
            `users`
        WHERE
            `users`.`id` IN (
                SELECT
                    `user_friends`.`user_id`
                FROM
                    `user_friends`
                    JOIN `users` AS `t0` ON `user_friends`.`friend_id` = `t0`.`id`
                WHERE
                    `t0`.`id` IN (
                        SELECT
                            `admin_id`
                        FROM
                            `groups`
                        WHERE
                            `admin_id` IS NOT NULL
                    )
            )
    )
 
 

效果

 

 

参考资料

https://entgo.io/docs/traversals/
https://github.com/rongfengliang/ent-pet-user-group

posted on 2019-10-15 13:33  荣锋亮  阅读(770)  评论(0)  编辑  收藏  举报

导航