How to join two collections in Fauna DB

I'm new to FaunaDb FQL, and I'm trying to query data from 2 collections through the indexes but I just don't get it. I created the indexes from the FaunaDB GUI:

Index #1 (users_waitlist) (No terms):

data.waitlist_meta (this is the reference to the product collection)
data.first_name
data.last_name
data.email
data.mobile_number
ref

Index #2 (products) (No terms)

data.name
data.dateStart
data.dateEnd
ref

In the users collection, we change the waitlist_meta to the product ref when the users are subscribed to the waitlist of the product, therefore, I created the users_waitlist index.

If I do the query like this:

Paginate(Match(Index('users_waitlist')))

It works by giving me all the documents from the users_waitlist index

But how can I retrieve also the documents from the products index that are referenced in the users_waitlist?

I hope that my question makes sense and I really thank you in advance

Solution

For this answer, I have created some sample data that is similar to what you have outlined:

> CreateCollection({ name: "users" })
{
  ref: Collection("users"),
  ts: 1642027810090000,
  history_days: 30,
  name: 'users'
}

> CreateCollection({ name: "products" })
{
  ref: Collection("products"),
  ts: 1642027821280000,
  history_days: 30,
  name: 'products'
}

> Create(
  Collection("products"),
  {
    data: {
      name: "grapple grommets",
      dateStart: Now(),
      dateEnd: TimeAdd(Now(), 30, "days"),
    }
  }
)
{
  ref: Ref(Collection("products"), "320622240400933376"),
  ts: 1642028045960000,
  data: {
    name: 'grapple grommets',
    dateStart: Time("2022-01-12T22:54:05.933Z"),
    dateEnd: Time("2022-02-11T22:54:05.933Z")
  }
}

> Create(
  Collection("users"),
  {
    data: {
      first_name: "Test",
      last_name: "User",
      email: "test@test.test",
      mobile_number: "+1 (123) 456-7890",
      product: Ref(Collection("products"), "320622240400933376")
    }
  }
)

{
  ref: Index("users_waitlist"),
  ts: 1642029519070000,
  active: true,
  serialized: true,
  name: 'users_waitlist',
  source: Collection("users"),
  values: [
    { field: [ 'data', 'product' ] },
    { field: [ 'data', 'first_name' ] },
    { field: [ 'data', 'last_name' ] },
    { field: [ 'data', 'email' ] },
    { field: [ 'data', 'mobile_number' ] },
    { field: [ 'ref' ] }
  ],
  partitions: 8
}
{
  ref: Index("users_waitlist"),
  ts: 1642028625760000,
  active: true,
  serialized: true,
  name: 'users_waitlist',
  source: Collection("users"),
  values: [
    { field: [ 'data', 'product' ] },
    { field: [ 'data', 'first_name' ] },
    { field: [ 'data', 'last_name' ] },
    { field: [ 'data', 'email' ] },
    { field: [ 'data', 'mobile_number' ] },
 
  ],
  partitions: 8
}

With that in place, your example query works as expected:

> Paginate(Match(Index("users_waitlist")))
{
  data: [
    [
      Ref(Collection("products"), "320622240400933376"),
      'Test',
      'User',
      'test@test.test',
      '+1 (123) 456-7890'
    ]
  ]
}

We need to modify the query to also retrieve the associated product. How should that be reflected in the result? Since there are likely going to be multiple users, each with an associated product, one solution is to return an object that contains the associated product document.

To do this, we need to use Map to iterate over all of the paginated results, and then use Let to both perform the Get for the product document and to compose an object for the result. Something like this:

Map(
  Paginate(Match(Index("users_waitlist"))),
  Lambda(
    [ "product_ref", "first", "last", "email", "mobile", "ref" ],
    Let(
      {
        product: Get(Var("product_ref")),
      },
      {
        product: Var("product"),
        first_name: Var("first"),
        last_name: Var("last"),
        email: Var("email"),
        mobile_number: Var("mobile"),
        ref: Var("ref"),
      }
    )
  )
)
{
  data: [
    {
      product: {
        ref: Ref(Collection("products"), "320622240400933376"),
        ts: 1642028045960000,
        data: {
          name: 'grapple grommets',
          dateStart: Time("2022-01-12T22:54:05.933Z"),
          dateEnd: Time("2022-02-11T22:54:05.933Z")
        }
      },
      first_name: 'Test',
      last_name: 'User',
      email: 'test@test.test',
      mobile_number: '+1 (123) 456-7890',
      ref: Ref(Collection("users"), "320622637091914240")
    }
  ]
}

Even though FQL is rather verbose and complex, it has a lot of power to compose results any way that you need.

Update: If a document does not have a reference set, blindly attempting to fetch the document results in an error. You can guard against that condition by first checking if the field that stores a reference actually has a reference, and if that reference exists.

To do that, replace this line in the query:

        product: Get(Var("product_ref")),

with:

        product: If(
          IsRef(Var("product_ref")),
          If(
            Exists(Var("product_ref")),
            Get(Var("product_ref")),
            {}
          ),
          {},
        ),

If the product is not a reference, or the reference does not exist, then the value returned is an empty object.