Having issues returning results sorted by Date

Help
#1

Hello! I thought I had this working but for some reason I cannot get it to work. What I am attempting is to get an index for a list of projects sorted by their due date. I created an index that seems to be working as expected when I test it in the dashboard. I then need get the ref from that index and so I used Join() and a ‘refByRef’ index as described in the Core FQL Concepts blog series. I need to do this so I can run Difference() with my other indexes all before I Paginate(). I get a list of projects at the end with no errors but they are not in order by due date. Can you see what I have so far below and let me know if more information is needed in order to get my list to return in date order. Thanks!

Map(
  Paginate(
    Difference(
      Join(
        Match(Index("projectsByDueDate")),
        Lambda(["value", "ref"], Match(Index("projectsRefByRef"), Var("ref")))
      ),
      Match(Index("getActiveProjects"), false),
      Match(Index("projectsByStatus"), "Closed"),
      Match(Index("projectsByStatus"), "Pending")
    )
  ),
Lambda("ref", Get(Var("ref")))
)
Intersect indexes that return Array and Ref
#2

Would the order of the other indexes inside Difference() affect the order of the final page?

#3

Hi @yellowspaceboots,

I’m a bit puzzled regarding the usefulness of index projectsRefByRef, since looks like you pass to it a Ref and get back a Ref.

I’m happy to take a look at your case if you explain a bit how documents look like and what is the expected result.

Luigi

#4

@Luigi_Servini Thanks for replying! the index projectsRefByRef is only to get the result of my index projectsByDueDate into a shape that can be used by Difference() with my other indexes. Difference() only works if all the indexes have the same shape. Since I need the date field to get my index in the correct order the result of my projectsByDueDate include both the ref and the date where the other indexes only include the ref. The Join() allows me to take a sorted index and put it in the shape needed to be used in Difference(). At least that is the idea :slight_smile:

Currently when I view the index projectsByDueDate in Fauna’s dashboard the result is as expected. I get back my projects in ascending date order. Also the Difference() is working as expected in the sense that I am basically using it to filter out projects that are active: false, status: "Closed" and status: "Pending". The Projects that I am getting back all look like they have been correctly filtered. The only issue is that they are not in the expected order by date the same way the plain index projectsByDueDate.

It looks like the Join() is what is messing up my sort. Is there a better way to use a sorted index inside of a Difference() with not sorted indexes? Thanks!

#5

Hi @yellowspaceboots,

if you can provide me with some documents, I can try to write a query that returns what you expect.
Not sure if you are in a test phase or you hare in prod with sensitive data.
In case, you can share in private.

Let me know.

Luigi

New pricing disappoints me immensely