在本系列的上一篇文章中，我们设置了基础并添加了一些示例数据。
现在，我们将潜入检索和合并数据。
我强烈鼓励您探索SorteAldB文档。这将使理解即将到来的示例更加顺畅。
查询租户
开始，我们将保持简单，然后从获取所有租户开始。
-- fetch all tenants
SELECT * from tenant;
结果：
[
{
"id": "tenant:car",
"name": "Car Enthusiasts"
},
{
"id": "tenant:cat",
"name": "Cat Owners"
},
{
"id": "tenant:musician",
"name": "Musicians"
}
]
添加可用的角色
我们的下一步是找出通常在特定租户的上下文中访问哪些角色。
此信息存储在tenant_role关系中。
现在，我们可以扩展我们的查询，如下所示。
-- select the tenant car enthusisast
-- fetch the related roles and return it as availableRoles
SELECT *,
(SELECT * FROM <-tenant_role<-role) as availableRoles
FROM tenant:car;
结果：
[
{
"availableRoles": [
{
"id": "role:admin",
"name": "Administrator"
},
{
"id": "role:reader",
"name": "Content Reader"
},
{
"id": "role:author",
"name": "Content Author"
}
],
"id": "tenant:car",
"name": "Car Enthusiasts"
}
]
现在，让我们整理输出。由于我们知道我们只获取一个租户，因此我们可以使用和tenant:car之间的koude3语句直接获得单个结果对象。此外，我们将限制输出仅显示角色ID。
-- return only id´s of roles and return as single object
SELECT *,
(SELECT id FROM <-tenant_role<-role).id as availableRoles
FROM ONLY tenant:car;
结果：
{
"availableRoles": [
"role:admin",
"role:reader",
"role:author"
],
"id": "tenant:car",
"name": "Car Enthusiasts"
}
添加租户成员
在下一步中，我们还将退还用户，这些用户是汽车爱好者。
-- add members of tenant car
SELECT *,
(SELECT * FROM <-tenant_member<-user) as members,
(SELECT id FROM <-tenant_role<-role).id as availableRoles
FROM ONLY tenant:car;
结果：
{
"availableRoles": [
"role:admin",
"role:reader",
"role:author"
],
"id": "tenant:car",
"members": [
{
"email": "john.doe@example.com",
"firstName": "John",
"id": "user:1",
"lastName": "Doe",
"name": "John Doe"
}
],
"name": "Car Enthusiasts"
}
添加租户会员角色
现在，我们将向给定房客的成员中的每个用户添加角色信息。
我们将更改
(SELECT * FROM <-tenant_member<-user) as members,
(
SELECT
(SELECT * FROM ONLY <-person) as person,
(SELECT id FROM ->member_role->role).id as roles,
FROM <-tenant_member
) as members
您可以观察到，我们已经改变了解决数据的方式。现在，我们不会通过<-tenant_member <-user一直到用户。相反，我们与<-tenant_member一起停止关系条目。
回想我们的数据模式。我们已经建立了用户和租户之间的关系。反过来，这种关系与一个甚至多个角色都有自己的联系。
将其视为十字路口。如果直接向前前进，您将发现用户信息。但是，如果您向左或向右转动，您将偶然发现在特定租户中分配给该用户的角色。
完整查询：
SELECT *, (
SELECT
(SELECT * FROM ONLY <-user) as user,
(SELECT id FROM ->member_role->role).id as roles
FROM <-tenant_member
) as members,
(SELECT id FROM <-tenant_role<-role).id as availableRoles
FROM ONLY tenant:car;
结果：
{
"availableRoles": [
"role:admin",
"role:reader",
"role:author"
],
"id": "tenant:car",
"members": [
{
"roles": [
"role:author"
],
"user": {
"email": "john.doe@example.com",
"firstName": "John",
"id": "user:1",
"lastName": "Doe",
"name": "John Doe"
}
}
],
"name": "Car Enthusiasts"
}
添加成员角色的租户权限
在大多数情况下，在此阶段向租户内的每个用户显示权限可能不切实际。
但是，为了学习和演示，我将说明SurreAldB的令人印象深刻的能力。
我们需要添加类似的东西：
(SELECT * FROM ->member_role->role->role_permission->permission) as permissions
将添加类似的东西：
"permissions": [
{
"id": "permission:update",
"name": "update content"
},
{
"id": "permission:read",
"name": "read content"
},
{
"id": "permission:create",
"name": "create content"
}
],
在我们的示例中，一切显得整洁而直接。
但是，我们的用户目前只有一个角色。
当用户担任多个角色时，他们可能会通过不同的角色获得相同的许可。这可能会导致我们的结果中重复的条目。
幸运的是，SurreLDB提供了一个set of handy helper functions。我们将利用array::group从我们的结果中消除这些重复。
完整查询：
SELECT *, (
SELECT
(SELECT * FROM ONLY <-user) as user,
(SELECT id FROM ->member_role->role).id as roles,
array::group((SELECT * FROM ->member_role->role->role_permission->permission)) as permissions
FROM <-tenant_member
) as members,
(SELECT id FROM <-tenant_role<-role).id as availableRoles
FROM ONLY tenant:car;
结果：
{
"availableRoles": [
"role:admin",
"role:reader",
"role:author"
],
"id": "tenant:car",
"members": [
{
"permissions": [
{
"id": "permission:update",
"name": "update content"
},
{
"id": "permission:read",
"name": "read content"
},
{
"id": "permission:create",
"name": "create content"
}
],
"roles": [
"role:author"
],
"user": {
"email": "john.doe@example.com",
"firstName": "John",
"id": "user:1",
"lastName": "Doe",
"name": "John Doe"
}
}
],
"name": "Car Enthusiasts"
}
最终查询ð
让我们删除权限和ONLY语句。
我们的查询（以获取单人或多个租户获取）是：
SELECT *, (
SELECT
(SELECT * FROM ONLY <-user) as user,
(SELECT id FROM ->member_role->role).id as roles
FROM <-tenant_member
) as members,
(SELECT id FROM <-tenant_role<-role).id as availableRoles
FROM tenant;
查询用户
当我们针对用户启动查询时，我们的目标是检索用户的详细信息。
此外，我们旨在确定用户属于哪些租户并了解与这些租户成员中的每一个相关的角色。
我们将利用与租户查询相同的关系。这里唯一的区别在于我们的方法：我们将在反向方向上穿越大多数图形关系。
因此，我将跳过详细的解释。
我们的用户查询：
-- query user(s) and return related tenants, roles and permissions
SELECT *, (
SELECT
(SELECT * FROM ONLY ->tenant) as tenant,
(SELECT id FROM ->member_role->role).id as roles,
array::group((SELECT * FROM ->member_role->role->role_permission->permission)) as permissions
FROM ->tenant_member
) as tenants
FROM user;
结果：
[
{
"email": "john.doe@example.com",
"firstName": "John",
"id": "user:1",
"lastName": "Doe",
"name": "John Doe",
"tenants": [
{
"permissions": [
{
"id": "permission:update",
"name": "update content"
},
{
"id": "permission:read",
"name": "read content"
},
{
"id": "permission:create",
"name": "create content"
}
],
"roles": [
"role:author"
],
"tenant": {
"id": "tenant:car",
"name": "Car Enthusiasts"
}
}
]
}
]
查询特定租户中特定用户的信息
作为先前查询的补充，我将提供一个查询，在现实世界中您可能需要类似的方式。
-- query the information for a specific user within a particular tenant and return user information, tenant information, roles and permissions
SELECT
(SELECT * FROM ONLY <-user) as user,
(SELECT *, (SELECT id FROM <-tenant_role<-role).id as availableRoles FROM ONLY ->tenant) as tenant,
(SELECT id FROM ->member_role->role).id as roles,
array::group((SELECT * FROM ->member_role->role->role_permission->permission)) as permissions
FROM ONLY tenant_member
WHERE in='user:1' and out='tenant:car'
结果：
{
"permissions": [
{
"id": "permission:update",
"name": "update content"
},
{
"id": "permission:read",
"name": "read content"
},
{
"id": "permission:create",
"name": "create content"
}
],
"roles": [
"role:author"
],
"tenant": {
"availableRoles": [
"role:admin",
"role:reader",
"role:author"
],
"id": "tenant:car",
"name": "Car Enthusiasts"
},
"user": {
"email": "john.doe@example.com",
"firstName": "John",
"id": "user:1",
"lastName": "Doe",
"name": "John Doe"
}
}