In the previous article of this series, we set up the foundation and added some sample data.
Now, we'll dive into retrieving and merging that data.
I strongly encourage you to explore the SurrealDB documentation. It will make understanding the upcoming examples much smoother.
Query tenant(s)
To start, we'll keep it simple and begin by fetching all the tenants.
-- fetch all tenants
SELECT * from tenant;
Result:
[
{
"id": "tenant:car",
"name": "Car Enthusiasts"
},
{
"id": "tenant:cat",
"name": "Cat Owners"
},
{
"id": "tenant:musician",
"name": "Musicians"
}
]
Add available roles
Our next step is to find out which roles are generally accessible within the context of a particular tenant.
This information is stored in the tenant_role relation.
We can now extend our query as shown below.
-- 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;
Result:
[
{
"availableRoles": [
{
"id": "role:admin",
"name": "Administrator"
},
{
"id": "role:reader",
"name": "Content Reader"
},
{
"id": "role:author",
"name": "Content Author"
}
],
"id": "tenant:car",
"name": "Car Enthusiasts"
}
]
Now, let's tidy up the output. Since we're aware that we're fetching just one tenant, we can use the ONLY statement between FROM and tenant:car to directly obtain a single result object. Additionally, we'll limit the output to only display the role IDs.
-- 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;
Result:
{
"availableRoles": [
"role:admin",
"role:reader",
"role:author"
],
"id": "tenant:car",
"name": "Car Enthusiasts"
}
Add tenant members
In the next step, we will also return the users, which are 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;
Result:
{
"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"
}
Add tenant member roles
We will now add the role information to each user who is a member of the given tenant.
We will change
(SELECT * FROM <-tenant_member<-user) as members,
to
(
SELECT
(SELECT * FROM ONLY <-person) as person,
(SELECT id FROM ->member_role->role).id as roles,
FROM <-tenant_member
) as members
As you can observe, we've made a change in how we resolve data. Now, we don't go all the way up to the user through <-tenant_member<-user. Instead, we stop at the relation entry itself with <-tenant_member.
Recall our data schema. We've established a relationship between users and tenants. This relationship, in turn, has its own connections to one or even multiple roles.
Think of it as a crossroads. If you head straight ahead, you'll discover user information. But if you veer left or right, you'll stumble upon the roles assigned to that user within a specific tenant.
The full query:
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;
Result:
{
"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"
}
Add tenant permissions for roles of members
In most scenarios, it may not be practical to display the permissions for every user within a tenant at this stage.
However, for the sake of learning and demonstration, I'll illustrate the impressive capabilities of SurrealDB.
We will need to add something like this:
(SELECT * FROM ->member_role->role->role_permission->permission) as permissions
Which will add something like this:
"permissions": [
{
"id": "permission:update",
"name": "update content"
},
{
"id": "permission:read",
"name": "read content"
},
{
"id": "permission:create",
"name": "create content"
}
],
In our example, everything appears neat and straightforward.
However, our user currently has just one role assigned.
When a user holds multiple roles, they might end up with the same permission through different roles. This could lead to duplicate entries in our results.
Fortunately, SurrealDB offers a set of handy helper functions. We'll employ array::group to eliminate these duplicates from our results.
The complete query:
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;
Result:
{
"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"
}
Final query 🎉
Let's remove the permissions and the ONLY Statement.
Our query, for fetching single or multiple tenants, is:
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)
When we initiate a query for a user, our goal is to retrieve the user's details.
Additionally, we aim to determine which tenants the user belongs to and understand the roles associated with each of these tenant memberships.
We will utilize the same relationships as we did for the tenant query. The only distinction here lies in our approach: we will traverse most of the graph relationships in the reverse direction.
Because of this, I will skip the detailed explanation.
Our user query:
-- 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;
Result:
[
{
"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 information for a specific user within a particular tenant
As an addition to the previous queries, I will provide a query, which you might need in similar way in real world scenarios.
-- 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'
Result:
{
"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"
}
}
Top comments (0)