How to Get Data from two collection in faunadb

Question

I am new to faunadb. I am having trouble with querying data from two collections. I have a user collection with the following data.

    {
       "ref": Ref(Collection("users"), "286520131377445052"),
       "ts": 1609505740440000,
       "data": {
          "userId": "f35fdc8d-6197-4d19-bf27-256bd41b3751",
          "name": "PRANTA Dutta",
          "email": "pranta@email.com",
          "role": "borrower",
          "password": "somepassword"
       }
    }

Then I have a loan collection with the following data

    {
       "ref": Ref(Collection("loans"), "287038065061397005"),
       "ts": 1609999680495000,
       "data": {
          "monthlyInstallment": 473.33,
          "loanDuration": 6,
          "interestRate": 7,
          "amount": 2000,
          "modifiedMonthlyInstallment": 513.33,
          "mode": "processing",
          "userId": "f35fdc8d-6197-4d19-bf27-256bd41b3751"
      }
}

Important: Loans and Users Collection have a one to many relations and a user can request multiple loans.

Now I want to write a query to fetch the loan data with matching userId at once so I can show all the data altogether.

The Data I want is the following:

   "data": {
       "monthlyInstallment": 473.33,
       "loanDuration": 6,
       "interestRate": 7,
       "amount": 2000,
       "modifiedMonthlyInstallment": 513.33,
       "mode": "processing",
       "userId": "f35fdc8d-6197-4d19-bf27-256bd41b3751",
       {
          "data": {
             "userId": "f35fdc8d-6197-4d19-bf27-256bd41b3751",
             "name": "PRANTA Dutta",
             "email": "pranta@email.com",
             "role": "borrower",
             "password": "somepassword"
          }
       }
   }

How can I do that using FQL, Thanks in Advance.

Answer 1

Counterintuitively Join is not what you need:

We have a Join FQL function, but it might not be what you expect. Join is rather a traverse that goes from one set of references (e.g. a set of user references) via an index to another set of references (e.g. the loans). I call it 'traverse' since it's essentially going to replace the user references with the loan references and therefore you don't have the data together as you require it. Just wanted to clarify that before you lose time trying to figure out how to do it with Join.

Instead, use a combination of Map and Get approach

In fauna, you have to think slightly different. Think about how you would do it in a regular procedural programming language: "get all loans, map over loans, for each loan, get the user".

In FQL terms, that description would be:

• Fetch an initial Page of documents (e.g. loans), with Paginate(Match(....))
• Loop over these (in FQL, not in the host language, it's still ACID) with Map() and Lambda()
• Within the lambda:
  • If that document contains a reference to the other documents you need (e.g. user), just use Get() to fetch that reference.
  • If the linked document contains the reference or it's contained in another collection (e.g. objectified relation in many to many) then use an index (e.g. Match(Index(... some value ... )) to retrieve the linked documents. Since you will in this case receive multiple results. Use Get on that index if it's one value that you expect or Paginate on the index if you expect many (you get to paginate on multiple levels)

A full explanation to get nested documents and code can be found here:

An example, on how to get nested documents has already been written out here:

How to get nested documents in FaunaDB?

Applied on your example:

Imagine you would have stored the User reference directly it would be this:

// Disclaimer, didn't test code, sorry if I missed a bracket. 
Map(
  // get loan references
  Paginate(Documents(Collection('loans'))),
  // map over pages of loan references
  Lambda('loanRef',
    Let({
        loan: Get(Var('loanRef')),
        user: Get(Select(['data', 'userRef'], Var('loan'))),
      },
      // And now we can then return whtatever we want (or could have 
      // omitted the let and directly return an object)
      {
        loan: Var('loan'),
        user: Var('user')
      }
    )
  )
)

Since you have chosen to use user defined IDs (which is fine), it'll need an extra step.

Map(
  // get loan references
  Paginate(Documents(Collection('loans'))),
  // map over pages of loan references
  Lambda('loanRef',
    Let({
        loan: Get(Var('loanRef')),
        userId: Select(['data', 'userId'], Var('loan')),
        // get on a match assumes the userId exists
        // and that there is only one result (in your case, many-to-one.. 
        // that's fine). It also assumes you have defined that index.
        user: Get(Match(Index("users_by_userid"), Var('userId')))
      },
      // And now we can then return whtatever we want (or could have 
      // omitted the let and directly return an object)
      {
        loan: Var('loan'),
        user: Var('user')
      }
    )
  )
)

The advantages of Map/Get

A bit of reasoning why it works that way. In Fauna, you have operations that operate on Sets which are rather descriptions of how your data will look like, and then you Paginate that set to actually get pages of data.

• Join: Imagine that we would have an SQL-like join and then call Paginate on that resultset. The first page might then only contain one user and 10000 loans (ok.. in this particular scenario it's less likely, but imagine your dealing with users and tweets for example). That's probably not what you want and there are also (I assume) performance/complexity reasons not offer a feature like this (SQL-like joins are particularly hard to scale).

• Map/Get on the other hand, the map/get way which enforces pagination is much more scalable and is also pretty nifty from a user perspective. It gives you control over pagination on multiple levels. In this scenario, you will get a page (let's say our pagesize = 100) of 100 users, and then (let's say our second pagesize = 10, which we can decide individually) 10 loans per user. In case there are more than 100 users, you get an after cursor to continue (or you could increase pagesize up to 100000). In case there are more loans for a certain user you'll get an after cursor for each of those and can separately control which one to fetch more data from. This ensures that you have the flexibility as a user and at the same time ensures performance and avoids having to fetch a huge amount of data in case your join has one relation with a high cardinality that explodes the resultset.