Can I query SurrealDB graph table but limit results based on overlap?

51 Views Asked by At

I have a database made up of "User" and "Track". Users can own tracks and tracks can be owned by users.

Here is a trivial setup:

CREATE user:0;
CREATE user:1;
CREATE user:2;
CREATE track:0;
CREATE track:1;
CREATE track:2;
CREATE track:3;
CREATE track:10;
CREATE track:11;
RELATE user:0->owns->track:0;
RELATE user:0->owns->track:1;
RELATE user:0->owns->track:2;
RELATE user:0->owns->track:3;
RELATE user:1->owns->track:0;
RELATE user:1->owns->track:10;
RELATE user:2->owns->track:0;
RELATE user:2->owns->track:1;
RELATE user:2->owns->track:10;
RELATE user:2->owns->track:11;

I'm looking for a way to find all the users who own the same tracks as a specific user, like this:

SELECT array::distinct(->owns->track<-owns<-user) as collection FROM user:0;

However, for the purposes of my application I am looking for two things.

  1. a way to have each user's tracks grouped with the user that owns them, and;

e.g.

[
    {
        "collection": [
            "track:0",
            "track:10",
        ],
        "id": "user:1"
    },
    {
        "collection": [
            "track:0",
            "track:1",
            "track:10"
            "track:11"
        ],
        "id": "user:2"
    },
]
  1. a way to limit the returned users based on the overlap of the tracks they own. i.e. if user:0 and user:1 share at least 3 tracks, return the above for user:2
0

There are 0 best solutions below