Merge index data from before and after a join

Help
1

I’m looking for some best practice advice. Say I have 2 collections which are related, with 2 indexes. E.g:

// Book with id #1
Books.data = {
  bookTitle: 'My book',
  authorName: 'John Smith'
}
Publications.data = {
  bookRef: Ref(Collection("Books"), '1'),
  publicationCountry: "USA",
  publicationTime: Time("2020-07-07T00:00:00Z")
}

// Indexes
CreateIndex({
  name: 'Books.info_by_bookRef_authorName.sort_bookTitle',
  terms: [
    {field: ["ref"]},
    {field: ["data", "authorName"]}
  ],
  values: [
    {field: ["data", "bookTitle"]},
    {field: ["data", "authorName"]},
    {field: ["ref"]}
  ]
})
CreateIndex({
  name: 'Publications.by_country.sort_time',
  terms: [{field: ['data', 'publicationTime']}],
  values: [
    {field: ["data", "publicationTime"], reverse: true},
    {field: ["data", "publicationCountry"]},
    {field: ["data", "bookRef"]},
    {field: ["ref"]}
  ]
})

Now, say I want all publications (with book info) written by author “John Smith” published in “USA”. I can get this via Join like so:

Let(
  {
    pubMatch: Match( // This gets all publications - even those you dont want to query
      Index('Publications.by_country.sort_time'),
      ["USA"]
    ),
    bookMatch: Join( // This filters down to only the relevant books
      Var('pubMatch'),
      Lambda(
        ['publicationTime', 'publicationCountry', 'bookRef', 'publicationRef'],
        Match(
          Index("Books.info_by_bookRef_authorName.sort_bookTitle"),
          [Var('bookRef'), "John Smith"]
        )
      )
    )
  },
  {
    books: Paginate(
      Var('bookMatch')  
    ),
    pubs: Paginate(Var('pubMatch'))
  }
)

This returns something like:

{
  books: {
    data: [
      ["Booktitle1", "John Smith", Ref(Collection("Books"), "268585911116104199")],
      ["Booktitle2", "John Smith", Ref(Collection("Books"), "269113645096501765")],
      ["Booktitle3", "John Smith", Ref(Collection("Books"), "269113828661264903")],
      ["Booktitle4", "John Smith", Ref(Collection("Books"), "269129263220785669")],
      ["Booktitle5", "John Smith", Ref(Collection("Books"), "269401393804083717")]
    ]
  },
  pubs: {
    data: [
      [
        Time("2020-07-02T07:30:49.870320Z"),
        "USA",
        Ref(Collection("Books"), "269127569049125381"),
        Ref(Collection("Publications"), "269920449379435014")
      ],
      [
        Time("2020-07-01T09:12:44.386935Z"),
        "USA",
        Ref(Collection("Books"), "269304525234373126"),
        Ref(Collection("Publications"), "269836263934132743")
      ],
      [
        Time("2020-07-01T08:46:33.172310Z"),
        "USA",
        Ref(Collection("Books"), "269401393804083717"),
        Ref(Collection("Publications"), "269834616420958727")
      ],
      [
        Time("2020-06-27T13:08:22.443175Z"),
        "USA",
        Ref(Collection("Books"), "269113645096501765"),
        Ref(Collection("Publications"), "269488700977578502")
      ],
      [
        Time("2020-06-26T12:23:54.997807Z"),
        "USA",
        Ref(Collection("Books"), "269129263220785669"),
        Ref(Collection("Publications"), "269395306986078726")
      ],
      [
        Time("2020-06-26T07:59:30.311936Z"),
        "USA",
        Ref(Collection("Books"), "269113828661264903"),
        Ref(Collection("Publications"), "269378671643460103")
      ],
      [
        Time("2020-06-17T14:09:37.552477Z"),
        "USA",
        Ref(Collection("Books"), "268585911116104199"),
        Ref(Collection("Publications"), "268586403984572933")
      ]
    ]
  }
}

Now - I only want the books results obviously, but WITH the data from the first query…! What is the best practice to include the results from the publications in the results from books?

This is what I can come up with at the moment:

  • Have a new index called “Publications.by_bookRef” which returns the desired data.
  • Somehow call ToObject(...) on the paginated results to make the returned value subscriptible using Select() on the Books’ Ref()
  • Map through the Book results and add items to the results using the aforementioned Select() method.
  • The above would only work IF there aren’t multiple Publications per Book - which isn’t logical in the above example…

This feels quite hacky, and it feels like I should already have the data available through the Index-Match I have already run?

Or maybe I should do this in a completely different way?

2

Short version:

fooMatch = Paginate(Match(
  Index('Foo.by_item2'),
  ['fooStuff2']
))
// {data: [
//   ['fooStuff1', 'fooStuff2', Ref(Collection('Bar'), '123')],
//   ['fooStuff12', 'fooStuff22', Ref(Collection('Bar'), '1234')]
// ]}

Join(
  fooMatch,
  Lambda(
    ['item1', 'item2', 'barRef'], // I want 'item1' and 'item2' to join the results!
    Match(
      Index('Bar.by_ref'),
      [Var('barRef')]
    )
  )
)
// {data: [
//   ['barStuff1', 'barStuff2', Ref(Collection('Bar'), '123')], // I want fooStuff here aswell!
//   ['barStuff12', 'barStuff22', Ref(Collection('Bar'), '1234')] // I want fooStuff here aswell!
// ]}

How to get fooStuff in the barStuff results?

3

Do not get too hung up on Join which might not be named as well as it could be, since it behaves so differently from SQL. Even Fauna folks admit

It looks like there is a one-to-many relationship between one Book and many Publications, correct? What you need to do is Paginate the relevant books, Map over them to query the related Publications and add them to the result.

You can return a javascript like object with the specific values you want, or you can use the Merge function to easily add new properties to the existing results. See this previous Topic for a related discussion.

You can also refer to this gist, which covers a lot of ways to building queries with nested data.

4

Thanks, I will need to ponder a little on my use case. The reason why I am a little confused about which approach to use is that I might need to “join” results between 4 connected collections, and allow the user to sort/range on fields from either of the collections. So in each case the pagination must happen on an Index for the related collection, and then I’ll need to “join” the data from the other 3 collections within a Map (or similar).

But again, I’ll consider my use case a little.