Search and sort with indexes

Searching and sorting is fairly easy, especially if you have been following the search and sort tutorials. Search is accomplished by matching inputs against an index’s terms field, and sort is accomplished by ordered of an index’s values field. That’s great if the search and sort can be handled by a single index.

What if we need to, say, search for the people with the last name "Cook" or "Turing" and sort the results by the letter field? This tutorial demonstrates how to combine multiple indexes to achieve searching and sorting.

This tutorial assumes that you have successfully prepared your database by creating the necessary collections and documents.

To achieve our goal, we need to compose our sort index a little bit differently than we saw in the sort tutorial. Copy the following query, paste it into the Shell, and run it:

Do(
  CreateIndex({
    name: "people_search_by_last",
    source: Collection("People"),
    terms: [
      { field: ["data", "last"] }
    ]
  }),
  CreateIndex({
    name: "people_sort_by_letter_asc",
    source: Collection("People"),
    terms: [
      { field: ["ref"] }
    ],
    values: [
      { field: ["data", "letter"] },
      { field: ["ref"] }
    ]
  })
)

The points of interest for this query:

We’re using the Do function to combine the creation of both indexes into a single query. Do executes each intermediate query in order, and returns the result from the last query.
The people_search_by_last specifies the last field, which is inside the data field, as our search terms. The values field is not specified, so the index contains a Ref to each document, by default.
The people_sort_by_letter_asc specifies the letter field, which is inside the document’s data field, as well as the document’s Ref, as the result/sort criteria. It also uses the document’s Ref as the terms, which is the important bit.

When you run the query, the result should be similar to:

{
  "ref": Index("people_sort_by_letter_asc"),
  "ts": 1565365919920000,
  "active": true,
  "serialized": true,
  "name": "people_sort_by_letter_asc",
  "source": Collection("People"),
  "terms": [
    {
      "field": [
        "ref"
      ]
    }
  ],
  "values": [
    {
      "field": [
        "data",
        "letter"
      ]
    },
    {
      "field": [
        "ref"
      ]
    }
  ],
  "partitions": 1
}

We only see the output for the second index, because that’s all that Do returns.

A query that can use these indexes to return the results we want could look like this:

Map(
  Paginate(
    Join(
      Union(
        Match(Index('people_search_by_last'), 'Turing'),
        Match(Index('people_search_by_last'), 'Cook'),
      ),
      q.Index('people_sort_by_letter_asc')
    )
  ),
  Lambda(
    ["letter", "ref"],
    Get(Var("ref"))
  )
)

The points of interest for this query are:

The Union function is used to combine the matches for Turing and the matches for Cook into a single set.
The Join function takes the values in the first set (the result of the Union) and matches them with the the terms field in the people_sort_by_letter_asc index.

Since the values from the Union call are document Refs, Join works a bit like Match, but with multiple terms at once rather than a singular set of terms.
The Lambda function has to accept two variables, because the result of the Join is the values from the people_sort_by_letter_asc index, which includes both the documents letter value and its Ref.

Copy the query, paste it into the Shell, and run it. The result should look similar to:

{
  "data": [
    {
      "ref": Ref(Collection("People"), "240166254282801673"),
      "ts": 1565299238420000,
      "data": {
        "first": "Alan",
        "last": "Turing",
        "letter": "B"
      }
    },
    {
      "ref": Ref(Collection("People"), "240166254282807817"),
      "ts": 1565299238420000,
      "data": {
        "first": "Stephen",
        "last": "Cook",
        "letter": "F"
      }
    },
    {
      "ref": Ref(Collection("People"), "240166254282802697"),
      "ts": 1565299238420000,
      "data": {
        "first": "Tim",
        "last": "Cook",
        "letter": "G"
      }
    }
  ]
}

Conclusion

This tutorial has demonstrated how to perform a search and sort in a single query. The main point is that the index used for sorting should have the document’s Ref specified in its terms field. Then, any of FQL’s Set functions can by used to combine result sets for matching indexes in a variety of ways:

Next steps

Was this article helpful?

We're sorry to hear that.
Tell us how we can improve! documentation@fauna.com

Thank you for your feedback!