DEV Community

Sebastian Wessel
Sebastian Wessel

Posted on

SurrealDB - Query and combine data via relations

#database #tutorial #architecture #datastructures

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.

See: SELECT and RELATE

Query tenant(s)

To start, we'll keep it simple and begin by fetching all the tenants.

-- fetch all tenants
SELECT * from tenant;
Enter fullscreen mode Exit fullscreen mode

Result:

[
    {
        "id": "tenant:car",
        "name": "Car Enthusiasts"
    },
    {
        "id": "tenant:cat",
        "name": "Cat Owners"
    },
    {
        "id": "tenant:musician",
        "name": "Musicians"
    }
]
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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"
    }
]
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

Result:

{
    "availableRoles": [
        "role:admin",
        "role:reader",
        "role:author"
    ],
    "id": "tenant:car",
    "name": "Car Enthusiasts"
}
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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"
}
Enter fullscreen mode Exit fullscreen mode

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,
Enter fullscreen mode Exit fullscreen mode

to

(
 SELECT 
  (SELECT * FROM ONLY <-person) as person,
  (SELECT id FROM ->member_role->role).id as roles,
 FROM <-tenant_member
) as members
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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"
}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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"
  }
],
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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"
}
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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"
                }
            }
        ]
    }
]
Enter fullscreen mode Exit fullscreen mode

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'
Enter fullscreen mode Exit fullscreen mode

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"
    }
}
Enter fullscreen mode Exit fullscreen mode

Top comments (0)

Read next

jeremycmorgan profile image

How to Make a Retro 2D JavaScript Game Part 2

Jeremy Morgan -

usmanzahidcode profile image

The Justin Beiber database problem!

Usman Zahid -

codechappie profile image

Cómo crear un Wallpaper dinámico con la Hora y Fecha usando Python

Code Chappie -

akhil_mittal profile image

Designing a Production-Grade Database for High-Traffic Applications on AWS RDS MySQL

akhil mittal -