Index sorting using relationship?

If I have the following index:

q.CreateIndex({
  name: 'blockListByBlockerId',
  source: q.Collection('blockedUser'),
  terms: [{ field: ['data', 'blockerId'] }],
  values: [
    { field: ['ref'] },
    { field: ['data', 'userId'] }
    /**
     * how do I add the user's firstname, middlename, and surname here
     * so that the result will be sorted by those data without
     * adding them to the collection
     */
  ]
});

which can have the following data:

{
  blockerId: '1',
  userId: '2'
}

What I want to do is something like this:

q.Map(
  q.Paginate(q.Match(q.Index('blockListByBlockerId'), '1')),
  q.Lambda(
    ['ref', 'userId'],
    q.Get(q.Ref(q.Collection('users'), q.Var('userId')))
  )
);

The result has to be sorted by user.firstName, user.middleName (which can be null), user.surname, so far I cannot find a way to do this without adding these values manually to the pivot collection.

Hmmm I think I have the answer.
First of all, I advise you to use full Ref instead of ids like ‘1’, ‘2’… Why ? Because you can use all the tools from Fauna to Intersect, Union, Join, etc…

So lets say you have an index :

q.CreateIndex({
  name: 'blockListByBlockerId',
  source: q.Collection('blockedUsers'),
  terms: [{ field: ['data', 'blockerId'] }],
  values: [
    { field: ['ref'] },
    { field: ['data', 'user'] } // user is a Ref
  ]
});

This would return something like :

{ 
  data: [
    [
      Ref(Collection("users"), "289710370146222597"), // blocker
      Ref(Collection("users"), "289710160550560261") // blocked user
    ],
    ...
  ]
}

You actually need a second Index !

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

And then use a beautiful Join :slight_smile:

q.Map(
  q.Paginate(
    q.Join(
      q.Match(q.Index('blockListByBlockerId'), Ref(Collection("users"), "289710370146222597")),
      q.Lambda(['blockerRef', 'blockedUserRef'],
        q.Match(q.Index('usersSortedByName'), q.Var('blockedUserRef'))
      )
    )
  ),
  q.Lambda(
    ['firstName', 'middleName', 'ref'],
    q.Get(Var('ref'))
  )
);

I tried this and it seems to work, so I also learned that you can sort when using Join :slight_smile:

2 Likes