Getting more useful data from a lookup on a many-to-many relationship in Fauna

Question

I have a users collection and an invitations collection. Users can invite multiple users. Records in the invitations collection document these invites. Each record in invitations is shaped like this:

"data": {
  "sponsor": Ref(Collection("users"), "344418396214919370"),
  "sponsee": Ref(Collection("users"), "345390249407414474")
}

The sponsor is the inviter and the sponsee is the invitee. Both reference a user in the users collection.

I'd like to know the most efficient way in FQL (minimum reads) to get the list of users that a particular user has invited, and some basic information about them.

The indexes I have setup are user_by_email and sponsee_by_sponsor. I also have a Function which gets a user by email called (you guessed) getUserByEmail.

What I have tried so far is:

Paginate(
  Match(
    Index('sponsee_by_sponsor'),
    Call(Fn('getUserByEmail'), '[email protected]')
  ),
)

Which gets me what I want but only in user Refs:

data: [
    Ref(Collection("users"), "345390249407414474"),
    Ref(Collection("users"), "345390805049934027")
  ]

Above is the result of the query, which are all the users that have been invited by [email protected]. But they are refs and I would like more information about each of these users, without having to make another read. I'm very new to FQL, so any help would be welcome.

Update

As per a request from the comment, here are the definitions of my indexes and Function:

A typical User document is shaped like this:

{
  "ref": Ref(Collection("users"), "344418396214919370"),
  "ts": 1665703164070000,
  "data": {
    "email": "[email protected]",
    "name": "Sponsor Name",
  }
}

Being able to get the email and name of the sponsees (rather than the Refs) is what is desired in as few reads as possible.

Answer 1

Indexes are the most efficient way to find documents in collections. But sometimes, the simplicity of indexes makes isolating documents hard, and that's where Filter or Reduce can help. Applying these functions is easy, but applying them efficiently requires a good understanding of the workflow and desired results. For example, you could Filter an entire collection, but that uses the "table scan" strategy, which is slow. But you can apply Filter to matches from a Match or the items from Paginate.

Efficiency improvement

Your getUserByEmail UDF is less efficient than it could be. It calls Get to fetch the reference and then selects the reference from the fetched document. The index provides that reference directly.

Since Match returns a set, which can contain 0 or more entries, you were likely using Get to materialize the first match. However, you can do this:

Select(["data", 0], Paginate(Match(Index("user_by_email"), "<email>")))

That query only reads the matching index entries. Since the index has unique: true, the set can only contain one entry. That means the Select call only needs to fetch item 0.

Fetch sponsee details

To return sponsee details, you must compose the response appropriately. The Let function is your friend for this task since it allows you to capture intermediate values and act on them.

For example:

Let(
  {
    email: "<email>",
    user_ref: Select(
      ["data", 0],
      Paginate(Match(Index("user_by_email"), Var("email"))),
    ),
    sponsees: Match(Index("sponsee_by_sponsor"), Var("user_ref")),
  },
  Map(
    Paginate(Var("sponsees")),
    Lambda(
      "sponsee_ref",
      Let(
        {
          sponsee: Get(Var("sponsee_ref")),
        },
        {
          name: Select(["data", "name"], Var("sponsee")),
          email: Select(["data", "email"], Var("sponsee")),
        }
      )
    )
  )
)

The first argument to the first Let lets us build up the values needed for the success of the query.

Since the sponsees value is a set, the second argument in the first Let is the expression that gives us the desired result. The sponsees set gets paginated, and Map applies the Lambda function to each entry in the page (the sponsee references). The Lambda accepts each sponsee reference, fetches the full sponsee document, and then composes an object containing fields for only the sponsee's name and email values.

Once you see how this all comes together, you should find it reasonably easy to modify the query to include/exclude fields or perform other operations.