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.

Searching and sorting

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 Reference 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.

We can now use both the new indexes to create a query that does both searching and sorting.

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

The points of interest for this query are:

  • The Join function takes the values in the first set (people with the last name Cook) and matches them with 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.

The above query uses the people_search_by_last index to find all the people with the last name Cook, and the people_sort_by_letter_asc index to sort them by their letter field. The results look similar to the following:

{
  data: [
    {
      ref: Ref(Collection("People"), "316175372989240897"),
      ts: 1637787182700000,
      data: {
        first: "Stephen",
        last: "Cook",
        degrees: ["BS", "PhD"],
        letter: "F"
      }
    },
    {
      ref: Ref(Collection("People"), "316175372989241921"),
      ts: 1637787182700000,
      data: {
        first: "Tim",
        last: "Cook",
        degrees: ["BS", "MBA"],
        letter: "G"
      }
    }
  ]
}

What if we want to search for more than one term? We can do that by using multiple Match expressions and combining them with the Union function.

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

Note that in the above query the Union function is used to combine the matches for Turing and the matches for Cook into a single set. Similar to the previous example, the Join function takes the values in the first set (the result of the Union) and matches them with the terms field in the people_sort_by_letter_asc index. Union behaves like the OR operator in an SQL query, while Intersection behaves like AND.

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",
      "degrees": [ 'BA', 'MA', 'MS', 'PhD' ],
      "letter": "B"
      }
    },
    {
    "ref": Ref(Collection("People"), "240166254282807817"),
    "ts": 1565299238420000,
    "data": {
      "first": "Stephen",
      "last": "Cook",
      "degrees": [ 'BS', 'PhD' ],
      "letter": "F"
      }
    },
    {
    "ref": Ref(Collection("People"), "240166254282802697"),
    "ts": 1565299238420000,
    "data": {
      "first": "Tim",
      "last": "Cook",
      "degrees": [ 'BS', 'MBA' ],
      "letter": "G"
      }
    }
  ]
}

Once all your indexes are in place, you can use Fauna Query Language to create searching and sorting queries as complex as necessary for your application’s requirements. The following query combines the Join, Intersection, and Union functions to:

  • Find all people with the last name Turing;

  • Find all people with the last name 'Cook';

  • Find all people with the first name 'Grace';

  • Filter out people who do not have 'PhD' in their degrees field;

  • Sort by the contents of their letter field.

Map(
  Paginate(
    Join(
      Intersection(
        Union(
          Match(Index('people_search_by_last'), 'Turing'),
          Match(Index('people_search_by_last'), 'Cook'),
          Match(Index('people_search_by_first'), 'Grace')
        ),
        Match(Index('people_search_by_degrees'), 'PhD')
      ),
      Index('people_sort_by_letter_asc')
    )
  ),
  Lambda(
    ["letter", "ref"],
    Get(Var("ref"))
  )
)

The above query produces the following results:

{
  data: [
    {
      ref: Ref(Collection("People"), "316175372989236801"),
      ts: 1637787182700000,
      data: {
        first: "Alan",
        last: "Turing",
        degrees: ["BA", "MA", "MS", "PhD"],
        letter: "B"
      }
    },
    {
      ref: Ref(Collection("People"), "316175372989237825"),
      ts: 1637787182700000,
      data: {
        first: "Grace",
        last: "Hopper",
        degrees: ["BA", "MA", "PhD"],
        letter: "C"
      }
    },
    {
      ref: Ref(Collection("People"), "316175372989240897"),
      ts: 1637787182700000,
      data: {
        first: "Stephen",
        last: "Cook",
        degrees: ["BS", "PhD"],
        letter: "F"
      }
    }
  ]
}

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

Is this article helpful? 

Tell Fauna how the article can be improved:
 Visit Fauna's forums or email docs@fauna.com

Thank you for your feedback!