Will query.Join consider existing defined sorting?

Help
, ,
1

I have the following index:

CreateIndex({
  name: 'userRefSortedByFullName',
  source: Collection('users'),
  terms: [{ field: ['ref'] }],
  values: [
    { field: ['data', 'firstName'] },
    { field: ['data', 'middleName'] },
    { field: ['data', 'surname'] },
    { field: ['ref'] }
  ]
})

This index was made only for sorting a list of users by name, in ascending order.

Now I have another index:

CreateIndex({
  name: 'friendsByUserId',
  source: Collection('friendships'),
  terms: [{ field: ['data', 'userId'] }],
  values: [
    { field: ['data', 'numTimesOpened'], reverse: true },
    { field: ['data', 'userId'] },
    { field: ['ref'] }
  ]
})

What I want to do, is to get the list of friends of a user and sort them based on how many times that user has opened that friend’s profile (indicated by numTimesOpened ) in descending order and by full name in ascending order.

If I do:

Map(
  Paginate(
    Join(
      Match(Index("friendsByUserId"), authUser.id),
      Lambda(
        ["numTimesOpened", "userId", "ref"],
        Match(
          Index("userRefSortedByFullName"),
          Ref(Collection("users"), Var("userId"))
        )
      )
    ),
    {
      size: 20,
      after: nextToken ? Ref(Collection("friendships"), nextToken) : [],
    }
  ),
  Lambda(
    ["numTimesOpened", "userId", "ref"],
    Merge(
      Get(Collection("users"), Var("userId")),
      { numTimesOpened: Var("numTimesOpened") }
    )
  )
);

Will Join still consider the first-level order clause which is to sort by numTimesOpened ? or will it disregard that and simply sort by name alone?

I haven’t tried it yet but will definitely try it soon. I wanted to check here if someone else has done something like this and has achieved it.

EDIT 1:

Okay, so I just tried adding this query, and it didn’t work as I expected, simply because the last lambda function will not be called with what values I was expecting it to be called with, which should contain numTimesOpened that I need to return to the frontend.

2

April, what’s the definition of the userRefSortedByFullName index? Specifically, what values is it supposed to return?

Can you share the output of Get(Index("userRefSortedByFullName"))?

Cory

3

Hi Cory, the userRefSortedByFullName is also above, but I actually can’t use it because userRefSortedByFullName and friendsByUserId returns different values, as you can see above, the userRefSortedByFullName is based on users collection and returns names only, but I need to get the numTimesOpened which is not returned by userRefSortedByFullName, sadly if I do join, I would actually get the return values of the joined index which is userRefSortedByFullName, so the query I showed above is actually not going to work because of this:

Lambda(
  ["numTimesOpened", "userId", "ref"],
  Merge(
    Get(Collection("users"), Var("userId")),
    { numTimesOpened: Var("numTimesOpened") }
  )
)

Which should become:

Lambda(
  ["firstName", "middleName", "surname", "ref"],
  Get(Var("ref"))
)

for it to work, but as you can see, I don’t have the numTimesOpened, would have been good if I can combine their returned values somehow.

4

There is a known issue where Join does not behave well with reversed indexes. Known issues | Fauna Documentation

Union might work better for you.

If you are running a Lambda to fetch the underlying document, you might experiment with Let to capture the document and then use Select to retrieve the fields you want to report. For example, something like this:

Let(
  {
    doc: Get(Var("ref")),
  },
  {
    firstName: Select(["data", "firstName"], Var("doc")),
    middleName: Select(["data", "middleName"], Var("doc")),
    surname: Select(["data", "surname"], Var("doc")),
    numTimesOpened: Select(["data", "numTimesOpened"], Var("doc")),
  }
)