Query all collection documents and select data to query specific documents from another collection using Fauna and FQL

Question

I have a use case where I would like to query all connections from the connections collection, and at the same time find the right document using employerId and employeeId from their respective collections (employer and employee collection) using Fauna DB. So, if I get an employerId back from the query, I would like to get the data back from the document within the employer collection.

A document from the connections collection looks like this:

{
  "ref": Ref(Collection("connections"), "336420921043583169"),
  "ts": 1657094868435000,
  "data": {
    "employerId": "330616765804445892",
    "employeeId": "330616700633350340",
    "isEmployeeApproved": true,
    "isEmployerApproved": true,
    "connectionAcceptedOnDate": "2022-07-06T08:07:47.846Z"
  }
}

I tried to create my function as the following, where I'm getting the data from the connectionscollection but I do not get anything from the other collections mentioned using the employerId and employeeId:

const getConnections = async () => {
  return await faunaClient.query(
    q.Map(
      q.Paginate(q.Match(q.Index("connections"))),
      q.Lambda("connectionDoc", q.Get(q.Var("connectionDoc")))
    ),
    q.Ref(Collection("employer"), q.Select("employerId", q.Var("connectionDoc"))),
    q.Ref(
      Collection("employees"),
      q.Select("employeeId", q.Var("connectionDoc"))
    ),
    q.Get(q.Var("employerDoc")),
    q.Get(q.Var("employeeDoc"))
  )
}

The ref I'm getting seems to be the first document within the connections collection. This is what I'm getting if I console log it:

data: Array [ {…}, {…} ]

0: Object { ref: {…}, ts: 1656490913630000, data: {…} }

data: Object { employeeId: "330074643026149574", employerId: "331454006483222721", isEmployeeApproved: true, … }

ref: Object { "@ref": {…} }

"@ref": Object { id: "335787295330271425", collection: {…} }

collection: Object { "@ref": {…} }

"@ref": Object { id: "connections", collection: {…} }

collection: Object { "@ref": {…} }

"@ref": Object { id: "collections" }

id: "collections"

<prototype>: Object { … }

<prototype>: Object { … }

id: "connection_request"

<prototype>: Object { … }

<prototype>: Object { … }

id: "335787295330271425"

<prototype>: Object { … }

<prototype>: Object { … }

ts: 1656490913630000

<prototype>: Object { … }

How can I select the employerId and look up the right document from the employer collection? I would like to do the same for the employeeId and the employee collection in the same query.

Updates

A suggestion has been made:

const connections = async () => {
  return await faunaClient.query(
    q.Map(q.Paginate(q.Documents(Collection("connections")))),
    q.Lambda(
      "ref",
      q.Let(
        {
          ref: q.Ref(Collection("connections"), id),
          connectionDoc: q.Get(q.Var("ref")),
          employerId: q.Select(["data", "employerId"], q.Var("connectionDoc")),
          employeeId: q.Select(
            ["data", "employeeId"],
            q.Var("connectionDoc")
          ),
          employerRef: q.Ref(Collection("employers"), q.Var("employerId")),
          employeeRef: q.Ref(
            Collection("employees"),
            q.Var("employeeId")
          ),
          employerDoc: q.Get(q.Var("employerRef")),
          employeeDoc: q.Get(q.Var("employeeRef")),
        },
        {
          ref: q.Var("ref"),
          ts: q.Select(["ts"], q.Var("connectionDoc")),
          data: {
            employee: q.Var("employeeDoc"),
            employer: q.Var("employerDoc"),
            isEmployeeApproved: q.Select(
              ["data", "isEmployeeApproved"],
              q.Var("connectionDoc")
            ),
            isEmployerApproved: q.Select(
              ["data", "isEmployerApproved"],
              q.Var("connectionDoc")
            ),
            connectionAcceptedOnDate: q.Select(
              ["data", "connectionAcceptedOnDate"],
              q.Var("connectionDoc")
            ),
          },
        }
      )
    )
  )
}

I receive the following error message when trying to use the function above:

InvalidArity: Map function requires 2 argument(s) but 1 were given
For more info, see the docs: https://docs.fauna.com/fauna/current/api/fql/functions/map

eskwayrd · Accepted Answer · 2022-07-13T21:17:45.353

Your query is malformed: the connectionDoc variable is only valid within the Lambda function, yet you depend on it outside of the Lambda in multiple locations.

Also, you appear to store only the document ID of the connection document's employerId and employeeId fields, which requires you to reconstruct references every time you want to use those values.

Before talking about performing the desired lookups in bulk, let's look at performing the lookup for a single connection document.

When you need to fetch a document and the details of related documents, Let is your friend: it allows you to capture intermediate values, and compose an appropriate result. For example, to work with your example connection document, we could do this:

Let(
  {
    ref: Ref(Collection("connections"), "336420921043583169"),
    connectionDoc: Get(Var("ref"))
  },
  Var("connectionDoc")
)

When this query is run, the result is:

{
  ref: Ref(Collection("connections"), "336420921043583169"),
  ts: 1657556091310000,
  data: {
    employerId: '330616765804445892',
    employeeId: '330616700633350340',
    isEmployeeApproved: true,
    isEmployerApproved: true,
    connectionAcceptedOnDate: '2022-07-06T08:07:47.846Z'
  }
}

Note: My document has a different timestamp, because I just created my own copy.

So, Let allowed us to define a variable called ref, which was used to define the reference to the connection document that we want to work on, and then we defined the variable connectionDoc to contain the fetched document with Get. Finally, the result is just the value of the connectionDoc variable. So far, so good.

Now, we want to replace the employerId field's value with the values from the document that this document ID represents. Same with the employeeId field. To do that, there are two changes to the query that must be made:

We have to fetch the associated documents, and to do that, we have to compose references based on the available Id values.
We have to expand the response from Let to specify what values to return. Since the documents referred to by the employerId and employeeId field values aren't in the connection document, we have to compose a new object that looks like a fully-populated connection document.

Let(
  {
    ref: Ref(Collection("connections"), "336420921043583169"),
    connectionDoc: Get(Var("ref")),
    employerId: Select(["data", "employerId"], Var("connectionDoc")),
    employeeId: Select(["data", "employeeId"], Var("connectionDoc")),
    employerRef: Ref(Collection("employers"), Var("employerId")),
    employeeRef: Ref(Collection("employees"), Var("employeeId")),
    employerDoc: Get(Var("employerRef")),
    employeeDoc: Get(Var("employeeRef")),
  },
  {
    ref: Var("ref"),
    ts: Select("ts", Var("connectionDoc")),
    data: {
      employer: Var("employerDoc"),
      employee: Var("employeeDoc"),
      isEmployeeApproved: Select(["data", "isEmployeeApproved"], Var("connectionDoc")),
      isEmployerApproved: Select(["data", "isEmployerApproved"], Var("connectionDoc")),
      connectionAcceptedOnDate: Select(["data", "connectionAcceptedOnDate"], Var("connectionDoc"))
    }
  }
)

Note that, since you did not provide example employer or employee documents, I've created very basic documents with the same document IDs that you provided.

The result of this query is:

{
  ref: Ref(Collection("connections"), "336420921043583169"),
  ts: 1657556091310000,
  data: {
    employer: {
      ref: Ref(Collection("employers"), "330616765804445892"),
      ts: 1657556658720000,
      data: { name: 'FooBar Co.' }
    },
    employee: {
      ref: Ref(Collection("employees"), "330616700633350340"),
      ts: 1657556709680000,
      data: { name: 'Alice Lidell' }
    },
    isEmployeeApproved: true,
    isEmployerApproved: true,
    connectionAcceptedOnDate: '2022-07-06T08:07:47.846Z'
  }
}

Since the query has the entire employer and employee documents included, it is not necessary to include the employerId and employeeId fields in the result, since the references that include these values is already available. However, you could easily include those if you really want them.

Now that the query work for a single connection document reference, let's extend it slightly.

You didn't provide the definition for the connections index, but since your query uses it with no terms, and it appears that the index only returns references, it's a collection index, and is equivalent to using the Documents function.

Map(
  Paginate(Documents(Collection("connections"))),
  Lambda(
    "ref",
    Let(
      {
        connectionDoc: Get(Var("ref")),
        employerId: Select(["data", "employerId"], Var("connectionDoc")),
        employeeId: Select(["data", "employeeId"], Var("connectionDoc")),
        employerRef: Ref(Collection("employers"), Var("employerId")),
        employeeRef: Ref(Collection("employees"), Var("employeeId")),
        employerDoc: Get(Var("employerRef")),
        employeeDoc: Get(Var("employeeRef")),
      },
      {
        ref: Var("ref"),
        ts: Select("ts", Var("connectionDoc")),
        data: {
          employer: Var("employerDoc"),
          employee: Var("employeeDoc"),
          isEmployeeApproved: Select(["data", "isEmployeeApproved"], Var("connectionDoc")),
          isEmployerApproved: Select(["data", "isEmployerApproved"], Var("connectionDoc")),
          connectionAcceptedOnDate: Select(["data", "connectionAcceptedOnDate"], Var("connectionDoc"))
        }
      }
    )
  )
)

The only significant difference between this query and the previous one, is that the Let expression is embedded within the Lambda for the Map function.

Since I only have one connection document, the result is:

{
  data: [
    {
      ref: Ref(Collection("connections"), "336420921043583169"),
      ts: 1657556091310000,
      data: {
        employer: {
          ref: Ref(Collection("employers"), "330616765804445892"),
          ts: 1657556658720000,
          data: { name: 'FooBar Co.' }
        },
        employee: {
          ref: Ref(Collection("employees"), "330616700633350340"),
          ts: 1657556709680000,
          data: { name: 'Alice Lidell' }
        },
        isEmployeeApproved: true,
        isEmployerApproved: true,
        connectionAcceptedOnDate: '2022-07-06T08:07:47.846Z'
      }
    }
  ]
}

If you have more connection documents, there would be more entries in the outermost data array.

That is some really good information! It seems like the 7th line has a hardcoded id map function. How can I retrieve that value? — Mathias Riis Sorensen, Jul 13 '22 at 10:14
I'm still a bit noob, and I tried following along, but I don't seem to be able to create a function that works. — Mathias Riis Sorensen, Jul 13 '22 at 10:15
I've updated my description with the function I tried based on your information. Can you spot what I do wrong? — Mathias Riis Sorensen, Jul 13 '22 at 10:38
The problem, as indicated by the error message, is that `Map` takes two arguments: a set/array and a `Lambda` that can be applied to every entry in the set/array. You closed your `Map` call before including the `Lambda`. Check the brackets. — eskwayrd, Jul 13 '22 at 21:16
Apologies for the hard-coded ref. It's not supposed to be there. :-) — eskwayrd, Jul 13 '22 at 21:17
Hm, I tried my best but I cannot seem to figure how to do it right. Would you mind providing the correct function? I really want to learn this. :-) — Mathias Riis Sorensen, Jul 16 '22 at 19:04
I believe the hard-coded ref triggers me. When I delete the id it provides the following error message: "code": "invalid expression", "description": "Ref expected, Object provided." — Mathias Riis Sorensen, Jul 16 '22 at 19:37

Query all collection documents and select data to query specific documents from another collection using Fauna and FQL

1 Answers1