ent 基本使用四 图遍历查询

接上文，我们已经创建了基本的关系以及表实体，以下是通过图方式的查询

参考关系图

 

 

 

代码处理

  • 创建图数据
func CreateGraph(ctx context.Context, client *ent.Client) error {
 // first, create the users.
 a8m, err := client.User.
  Create().
  SetAge(30).
  SetName("Ariel").
  Save(ctx)
 if err != nil {
  return err
 }
 neta, err := client.User.
  Create().
  SetAge(28).
  SetName("Neta").
  Save(ctx)
 if err != nil {
  return err
 }
 // then, create the cars, and attach them to the users in the creation.
 _, err = client.Car.
  Create().
  SetModel("Tesla").
  SetRegisteredAt(time.Now()). // ignore the time in the graph.
  SetOwner(a8m). // attach this graph to Ariel.
  Save(ctx)
 if err != nil {
  return err
 }
 _, err = client.Car.
  Create().
  SetModel("Mazda").
  SetRegisteredAt(time.Now()). // ignore the time in the graph.
  SetOwner(a8m). // attach this graph to Ariel.
  Save(ctx)
 if err != nil {
  return err
 }
 _, err = client.Car.
  Create().
  SetModel("Ford").
  SetRegisteredAt(time.Now()). // ignore the time in the graph.
  SetOwner(neta). // attach this graph to Neta.
  Save(ctx)
 if err != nil {
  return err
 }
 // create the groups, and add their users in the creation.
 _, err = client.Group.
  Create().
  SetName("GitLab").
  AddUsers(neta, a8m).
  Save(ctx)
 if err != nil {
  return err
 }
 _, err = client.Group.
  Create().
  SetName("GitHub").
  AddUsers(a8m).
  Save(ctx)
 if err != nil {
  return err
 }
 log.Println("The graph was created successfully")
 return nil
}
 
  • 查询组名称为github 的用户以及汽车
func QueryGithub(ctx context.Context, client *ent.Client) error {
 cars, err := client.Group.
  Query().
  Where(group.Name("GitHub")). // (Group(Name=GitHub),)
  QueryUsers(). // (User(Name=Ariel, Age=30),)
  QueryCars(). // (Car(Model=Tesla, RegisteredAt=<Time>), Car(Model=Mazda, RegisteredAt=<Time>),)
  All(ctx)
 if err != nil {
  return fmt.Errorf("failed getting cars: %v", err)
 }
 log.Println("cars returned:", cars)
 // Output: (Car(Model=Tesla, RegisteredAt=<Time>), Car(Model=Mazda, RegisteredAt=<Time>),)
 return nil
}
  • 查询用户Ariel 的车
func QueryArielCars(ctx context.Context, client *ent.Client) error {
 // Get "Ariel" from previous steps.
 a8m := client.User.
  Query().
  Where(
   user.HasCars(),
   user.Name("Ariel"),
  ).
  OnlyX(ctx)
 cars, err := a8m. // Get the groups, that a8m is connected to:
    QueryGroups(). // (Group(Name=GitHub), Group(Name=GitLab),)
    QueryUsers(). // (User(Name=Ariel, Age=30), User(Name=Neta, Age=28),)
    QueryCars(). //
    Where( //
   car.Not( // Get Neta and Ariel cars, but filter out
    car.ModelEQ("Mazda"), // those who named "Mazda"
   ), //
  ). //
  All(ctx)
 if err != nil {
  return fmt.Errorf("failed getting cars: %v", err)
 }
 log.Println("cars returned:", cars)
 // Output: (Car(Model=Tesla, RegisteredAt=<Time>), Car(Model=Ford, RegisteredAt=<Time>),)
 return nil
}
  • 查询包含用户的组
func QueryGroupWithUsers(ctx context.Context, client *ent.Client) error {
 groups, err := client.Group.
  Query().
  Where(group.HasUsers()).
  All(ctx)
 if err != nil {
  return fmt.Errorf("failed getting groups: %v", err)
 }
 log.Println("groups returned:", groups)
 // Output: (Group(Name=GitHub), Group(Name=GitLab),)
 return nil
}
​

查询sql

为了查看生成的sql，我启用了慢查询处理

set global long_query_time=0;
set global slow_query_log=1;
  • 生成的sql
    以下是一个查询的sql,我进行了格式化
 
SELECT
    DISTINCT `cars`.`id`,
    `cars`.`model`,
    `cars`.`registered_at`
FROM
    `cars`
    JOIN (
        SELECT
            `users`.`id`
        FROM
            `users`
            JOIN (
                SELECT
                    `group_users`.`user_id`
                FROM
                    `group_users`
                    JOIN (
                        SELECT
                            `groups`.`id`
                        FROM
                            `groups`
                            JOIN (
                                SELECT
                                    `group_users`.`group_id`
                                FROM
                                    `group_users`
                                    JOIN `users` AS `t0` ON `group_users`.`user_id` = `t0`.`id`
                                WHERE
                                    `t0`.`id` = 10
                            ) AS `t1` ON `groups`.`id` = `t1`.`group_id`
                    ) AS `t1` ON `group_users`.`group_id` = `t1`.`id`
            ) AS `t1` ON `users`.`id` = `t1`.`user_id`
    ) AS `t1` ON `cars`.`owner_id` = `t1`.`id`
WHERE
    NOT (`cars`.`model` = 'Mazda');
 
 

查看查询计划

EXPLAIN SELECT DISTINCT `cars`.`id`, `cars`.`model`, `cars`.`registered_at` FROM `cars` JOIN (SELECT `users`.`id` FROM `users` JOIN (SELECT `group_users`.`user_id` FROM `group_users` JOIN (SELECT `groups`.`id` FROM `groups` JOIN (SELECT `group_users`.`group_id` FROM `group_users` JOIN `users` AS `t0` ON `group_users`.`user_id` = `t0`.`id` WHERE `t0`.`id` = 10) AS `t1` ON `groups`.`id` = `t1`.`group_id`) AS `t1` ON `group_users`.`group_id` = `t1`.`id`) AS `t1` ON `users`.`id` = `t1`.`user_id`) AS `t1` ON `cars`.`owner_id` = `t1`.`id` WHERE NOT (`cars`.`model` = 'Mazda');
 

 

 


说明：
从上边可以看出通过索引进行了优化，还是比较高效的

参考资料

https://entgo.io/docs/getting-started/
https://github.com/rongfengliang/ent-demo

posted on 2019-10-14 20:45  荣锋亮  阅读(565)  评论(0)  编辑  收藏  举报

导航