接上文，我们已经创建了基本的关系以及表实体，以下是通过图方式的查询
参考关系图
代码处理
- 创建图数据
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