Hasura GraphQL Engine & SQL Functions: Unveiling User Similarities and Crafting Matchmaking/Recommendation Systems — A Comprehensive Tutorial

In today’s connected world, providing users with personalized experiences and recommendations tailored to their interests and preferences is essential. One powerful way to achieve this is by finding commonalities between users and leveraging these connections to deliver relevant content or suggestions.

In this tutorial, I will show you how to use the Hasura GraphQL engine, and SQL functions to create a versatile system that identifies user similarities and builds matchmaking or recommendation functionality. I will focus on a use case involving users with shared interests, but the approach can also be adapted to various other scenarios. E.g. common friends, mutual followers, similar tastes in media, common skills, matching travel destinations, etc.

I will show you now how to build the backend for the view in the following image where we can see users sorted by the number of skills that match my skills and display skills in bold & different colours.

Blue numbers indicate the id primary key in the users table and the purple number is the id in the interests table for easier testing.

Create tables, relations and sample data

Let’s start by creating the necessary tables in our Hasura project. We will have three tables: users, interests, and user_interests join the table so we can create an M2M (many-to-many) relationship between users and their interests.

Create the tables and relations with the structure seen in the following ER diagram manually or import via CLI from Hasura migrations and metadata.

Minimal required tables/columns

In the user_interests table add two foreign keys to establish the M2M relationship:

1. To reference table users: from user_id to id column.
2. To reference table interests: from interest_id to id column.

If you click on the public database now in the left menu then you can one-click track all the four untracked foreign-key relationships so they will be added also to your API.

Now, let’s add some test data to tables. Some users, some interests and some matching and non-matchin interests with user id=1, for example. Or import from the seeds here.

Creating the SQL Functions

We will need three SQL functions:

1. To find the amount of common interests with other users and sort the users according to that amount in descending order.
2. Get the common interests with each user.
3. Get the other interests (that are not common) for each user.

Let’s start with find_users_by_common_interests function:

-- Create a function named 'find_users_by_common_interests'
-- It takes an input parameter 'input_user_id' of type INTEGER
-- The function returns a set of rows from the 'users' table
CREATE OR REPLACE FUNCTION public.find_users_by_common_interests(input_user_id INTEGER)
RETURNS SETOF users
LANGUAGE sql
STABLE
AS $function$

SELECT
    u.* -- Select all columns from the 'users' table and give the table an alias 'u'
FROM
    users u
WHERE
    u.id <> input_user_id -- Filter out the user with the same ID as the input user
ORDER BY
    (
        -- Subquery to calculate the number of common interests between input user and other users
        SELECT
            COUNT(*) -- Count the number of common interests between the two users
        FROM
            user_interests ui1 -- Aliased 'user_interests' table for the input user
            JOIN user_interests ui2 ON ui1.interest_id = ui2.interest_id -- Inner join on 'user_interests' table to find matching interests between input user and other users
        WHERE
            ui1.user_id = input_user_id -- Filter the joined table to include only rows where 'ui1.user_id' matches the input user ID
            AND ui2.user_id = u.id -- Filter the joined table to include only rows where 'ui2.user_id' matches the ID of the user being compared
    ) DESC; -- Sort the result in descending order based on the number of common interests

$function$;

Open in the Hasura console from “Data” menu the “SQL” from the left bottom and copy the above code there and hit run. Then click in the left top on public database and add the function as root field:

Now, we get the users sorted by the amount of common interests. See the image.

Secondly, let’s create the common_interests function, that will return the common interests between the current user (given as an input) and other users:

-- Create a function named 'common_interests'
-- It takes two input parameters:
-- 'user_row' of type 'users' (a row from the 'users' table)
-- 'input' of type JSON, which should contain a key 'user_id' with a user ID value
CREATE OR REPLACE FUNCTION public.common_interests(user_row users, input JSON)
RETURNS SETOF interests -- Returns a set of rows from the 'interests' table
LANGUAGE sql
STABLE
AS $function$

SELECT
    i.* -- Select all columns from the 'interests' table and give the table an alias 'i'
FROM
    interests i
    -- Join the 'interests' table with 'user_interests' for the target user (input user)
    JOIN user_interests target_user_interests ON i.id = target_user_interests.interest_id
    -- Join the 'interests' table with 'user_interests' for the other user (user_row)
    JOIN user_interests other_user_interests ON i.id = other_user_interests.interest_id
WHERE
    -- Filter the joined tables to include only rows where 'target_user_interests.user_id' matches the input user ID
    target_user_interests.user_id = (input ->> 'user_id') :: INTEGER
    -- Filter the joined tables to include only rows where 'other_user_interests.user_id' matches the ID of the user being compared
    AND other_user_interests.user_id = user_row.id;

$function$;

This function we will add as a computed field in users table. Open the “Modify” tab in users table and in the “Computed Fields” section click “Add Computed Field”:

• Computed Field Name: common_interests
• Function Name (dropdown): common_interests
• Table Row Argument: user_row

Hit “Save” and finally let’s create the other_interests function to get all the interests of the user that are not common with the current user:

-- Create a function named 'other_interests'
-- It takes two input parameters:
-- 'user_row' of type 'users' (a row from the 'users' table)
-- 'input' of type JSON, which should contain a key 'user_id' with a user ID value
-- The function returns a set of rows from the 'interests' table
CREATE OR REPLACE FUNCTION public.other_interests(user_row users, input json)
RETURNS SETOF interests
LANGUAGE sql
STABLE
AS $function$

  SELECT i.* -- Select all columns from the 'interests' table and give the table an alias 'i'
  FROM interests i
  -- Join the 'interests' table with 'user_interests' for the other user (other_user_interests)
  JOIN user_interests other_user_interests ON i.id = other_user_interests.interest_id
  WHERE
    -- Filter the joined tables to include only rows where 'other_user_interests.user_id' matches the ID of the user being compared
    other_user_interests.user_id = user_row.id
    -- Exclude rows with interest IDs that are also present in the 'user_interests' table for the input user
    AND i.id NOT IN (
      SELECT interest_id
      FROM user_interests
      -- Filter the 'user_interests' table to include only rows where 'user_id' matches the input user ID
      WHERE user_id = (input->>'user_id')::INTEGER
    );

$function$;

And this function we will add the same way as a computed field in users table. Name it other_interests and also add the table row argument user_row. Hit save and we’re done!

Time to test

Let’s run the GraphQL query:

query GetUsersByCommonInterests($userId: Int!, $limit: Int, $offset: Int) {
  find_users_by_common_interests(
    args: { input_user_id: $userId }
    limit: $limit
    offset: $offset
  ) {
    id
    first_name
    common_interests: common_interests(args: { input: { user_id: $userId } }) {
      id
      name
    }
    other_interests: other_interests(args: { input: { user_id: $userId } }) {
      id
      name
    }
  }
}

And pass in the variables:

{
  "userId": 1,
  "limit": 4,
  "offset": 0
}

Note that the "limit": 4 and “offset”: 0 state that we’re having 4 items per page and we are in the first page in our pagination. For the second page we’ll just change the “offset”: 4. And here is the result that matches exactly to the wireframe image in the top.

Now, we can deliver this GraphQL query to our front-end dev or we can also create a REST endpoint for this. Click in the GraphiQL in the “REST” button:

Name it common_interests, for example, and add the URL and into the “Location” input enter: interests/common/:userId/:limit/:offset

So, we add it under interests, name it common and want three parameters: userID, limit and offset. Note that limit and offset are optional. For “Methods” check, for example, GET and POST and hit “Create” button.

Let’s try it also out, for example, with Thunder Client for VSC that is a great VSC extension so you don’t need to use any extra software like Postman, for example, and can test your APIs straight from VSC. First, we need to add into the header x-hasura-admin-secret to the header for authentication or just change the permissions in tables so you we can access publicly: https://matchmaking.hasura.app/api/rest/interests/common/1/4/0

Here is the whole repository with all the Hasura metadata (including public permissions), migrations, seeds, images, queries and links to other materials used: https://github.com/kasparpalgi/user-similarities-matchmaking

Next steps?

Have a look at the more advanced e-commerce product suggestions that take into consideration multiple aspects:

• Product views
• Product likes
• How recently the user has interacted with the product

Repository: https://github.com/kasparpalgi/smart-product-recommender

Also, join the Hasura Facebook and LinkedIn groups for more cool tutorials and subscribe to my Medium posts!