How to join collections based on data from a document

Help
1

Hi,

My goal is to display relevant user information from different collections based on their IDs (employeeId or employerId). I would like to show a list of those connected users and get the data from the specific documents.

If you as a user have the role “employer” you will save all your information in a document within the “employer” collection. The same goes for “employees” if you have the role “employee” your data will be stored within the “employee” collection.

I then have a collection called “connections” with documents created in the moment an employer and an employee decide to connect with each other. A document from that 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"
  }
}

You will notice that we have employerId and employeeId. I would like to show the specific document from the other collections if they contain the specific IDs.

I’ve tried using Join but it seems that I have to provide the employerId or employeeId as an argument before I can join with another collection. Below is what I tried:

const getConnections = async () => {
  return await faunaClient.query(
    q.Map(
      q.Paginate(
        q.Join(
          q.Match(
            q.Index("connections"),
            employerId, 
            employerId,
          ),
          q.Lambda(
            "employerId",
            q.Match(q.Index("employers"), q.Var("employerId"))
          ),
          q.Lambda(
            "employeeId",
            q.Match(q.Index("employees"), q.Var("employees"))
          )
        )
      ),
      q.Lambda("ref", q.Get(q.Var("ref")))
    )
  )
}

Here is the connection index I’m using:

Source collection:
connections

terms:

  • data.employerId
  • data. employeeId

Here is the employers index I’m using:

Source collection:
employers

terms:

  • data.employerId

Here is the employee index I’m using:

Source collection:
employees

terms:

  • data. employeeId

How can I get the documents from the collections employers and employees based on the IDs from the connections collection?

2

Hi @mathiassio,

You’re on the right track using a join collection like this, but what you’re trying to do can be simplified significantly by making sure you’re using right the right indexes, and making sure that the employerId and employeeId fields have the same values as the references for those documents.

For instance, say you have the following entries in the employers and employees collections, respectively:

{
  "ref": Ref(Collection("employers"), "337004376263491655"),
  "ts": 1657651402120000,
  "data": {
    "employerName": "Foo Inc"
  }
}

{
  "ref": Ref(Collection("employees"), "337004404555120711"),
  "ts": 1657651394000000,
  "data": {
    "name": "Alice"
  }
}

So then, in your connections collection, they would be joined like this:

{
  "ref": Ref(Collection("connections"), "337004423811170375"),
  "ts": 1657653925980000,
  "data": {
    "employerId": "337004376263491655",
    "employeeId": "337004404555120711",
    "isEmployeeApproved": true,
    "isEmployerApproved": true,
    "connectionAcceptedOnDate": "2022-07-06T08:07:47.846Z"
  }
}

Note that we’re using the internal reference number for the ID fields. This makes it easier to look up those documents later.

The index you use should then return those same fields, along with a reference to the connection itself:

{
  name: "e_e_connections",
  unique: false,
  serialized: true,
  source: "connections",
  terms: [
    {
      field: ["data", "employerId"]
    },
    {
      field: ["data", "employeeId"]
    }
  ],
  values: [
    {
      field: ["data", "employerId"]
    },
    {
      field: ["data", "employeeId"]
    },
    {
      field: ["ref"]
    }
  ]
}

So now to query for the existing connection you just have to run that one index, pipe the results to a lambda function, and return them directly:

Map(
  Paginate(
  Match(
    Index("e_e_connections"),
          "337004376263491655", 
          "337004404555120711"
  )),
  Lambda(
    ['employerId','employeeId','ref'],
    [Get(Ref(Collection("employers"),Var("employerId"))),
     Get(Ref(Collection("employees"),Var("employeeId")))]
  )
)

The results will look like this:

{
  data: [
    [
      {
        ref: Ref(Collection("employers"), "337004376263491655"),
        ts: 1657651402120000,
        data: {
          employerName: "Foo Inc"
        }
      },
      {
        ref: Ref(Collection("employees"), "337004404555120711"),
        ts: 1657651394000000,
        data: {
          name: "Alice"
        }
      }
    ]
  ]
}