How to retrieve a collection using an index to sort the results

Help
, ,
1

Hello,

This is likely an issue with my newness to FaunaDB and FQL. I have created an index called section_all which uses values of data.sectionID and ref. When I run a Map using Paginate and a Lambda function I can get back the refs in a response. The problem I am facing is, once I have those refs what is the best way to fetch the corresponding data for those refs? I am probably going about this in the wrong way. I had been using Paginate with indexes for say courses_all which is unsorted index for another collection. I would normally fetch the courses as such:

export async function getCatalogData() {
  return new Promise((resolve, reject) => {
    let helper = clientFauna.paginate(query.Match(query.Index("courses_all")));
    const response = [];
    helper
      .map((ref) => {
        return query.Get(ref);
      })
      .each((page) => {
        if (page.length > 0) {
          for (let i = 0; i < page.length; i++) {
            // Loop through and build the result
          }
        } else {
          reject(constants.CATALOG.ERROR.FETCH_FAILED);
        }
      })
      .then(() => {
        resolve(response);
      })
      .catch((error) => reject(error));
  });
}

Which this method won’t work for a sorted index, far as I am aware. I can get the refs like this:

clientFauna
      .query(
        query.Map(
          query.Paginate(query.Match(query.Index("section_all"))),
          query.Lambda(["sectionID", "ref"], query.Var("ref"))
        )
      )
      .then((response) => {
        console.log(response);
      })
      .catch((error) => {
        console.log(error);
      });

I could loop that response and build out new queries using Get, but this seems like too many read operations. Is it possible, and if so how, to fetch an entire collection sorted based on a field in the data in one request?

Thanks ahead of time.

2

All Fauna indexes are sorted. If you don’t specify any values, then the index is sorted by the indexed documents’ references.

You can specify document fields in an index’s values field. Then you only need to fetch index results rather that calling Get on a document reference to get those values. This can reduce the number of read ops incurred, but it means that your indexes store copies of the specified field values, increasing the storage used in your database, and additional write operations for the index entries.

Fauna is a shared database service. We do try to make querying fair across all databases by preventing the mistake of allowing the equivalent of SELECT * FROM table.

That means that you have to use Paginate to iterate through results. You can only retrieve results for all of the documents in a collection when there are fewer than 100,000 documents in the collection. Beyond that, you need to use multiple queries to fetch additional pages of the result set.

1 Like
3

ewan,

Thanks for the help. I did not realize that about indexes. Putting the data in the values seems to be my solution. Though, my next problem here is I have a data field called meetingTimes. Which is an array of objects:

[
    {
      field: ["T", "R"],
      start: String,
      end: String
    },
    ...
    n objects
  ]

Which when I put this field into the values, it returns null for that field when I run the index.

“Note that Objects and Arrays are not indexed, although the fields of an object can be indexed.”

What would be the best way to return my array of objects that one of the objects fields contains another array.

Some examples

meetingTimes: [
    {
      dayInitials: ["M"],
      startTime: "09:00am",
      endTime: "09:50am"
    },
    {
      dayInitials: ["F"],
      startTime: "10:00am",
      endTime: "10:50am"
    }
  ]

From another document

meetingTimes: [
    {
      dayInitials: ["T", "R"],
      startTime: "09:00am",
      endTime: "10:15am"
    }
  ]
4

You could, perhaps, use a binding to compose a single string representation of the meetings times, and index that: Indexes | Fauna Documentation

From your sample meetings time, the compose strings could look something like “M0900-9950,F1000-1050”, and “TR0900-1015”

5

Well, I will be switching to formatting my meetingTimes array of objects etc to strings when I create the document. Then just parse it back on my server end. Thanks, I will mark you initial response as the solution.

1 Like
6

This topic was automatically closed 2 days after the last reply. New replies are no longer allowed.