DEV Community

loading...
Cover image for Core FQL concepts part 4: Range queries and advanced filtering
Fauna, Inc.

Core FQL concepts part 4: Range queries and advanced filtering

Fauna
The data API for modern applications.
Originally published at fauna.com ・8 min read

I briefly introduced Range() in a previous article and, as promised, we're going to take a deeper look at it today.

This series assumes that you have a grasp on the basics. If you're new to FaunaDB and/or FQL here's my introductory series on FQL.

In this article:

  • Introduction to range queries
  • Multi-items boundaries
  • Range and then filter
  • Index and then range
  • Combine indexes
  • Range with index bindings

Introduction to range queries

The idea of range queries is to be able to filter results by providing two boundaries, expressed as start and end values. Since FaunaDB needs to be able to compare values to determine when a result should be included, these boundaries have to be scalar values such as numbers, strings, or dates, but not other FQL types such as booleans.

Let's create some data to see how Range() works:

> CreateCollection({
  name: "Alphabet"
})
Enter fullscreen mode Exit fullscreen mode

And let's add some documents with a Map() :

> Map(
  [
    {leter: "A", position: 1},
    {leter: "B", position: 2},
    {leter: "C", position: 3},
    {leter: "D", position: 4},
    {leter: "E", position: 5},
    {leter: "F", position: 6},
    {leter: "G", position: 7},
    {leter: "H", position: 8},
    {leter: "I", position: 9},
    {leter: "J", position: 10}
  ],
  Lambda(
    "data",
    Create(
      Collection("Alphabet"),
      {data: Var("data")}
    )
  )
)
Enter fullscreen mode Exit fullscreen mode

We're also going to need an index to be able to query our documents:

> CreateIndex({
    name: "Alphabet_by_letter_position",
    source: Collection("Alphabet"),
    values: [
      {field: ['data', 'letter']},
      {field: ['data', 'position']}
    ]
})
Enter fullscreen mode Exit fullscreen mode

Do note that we've configured the index to return some values. Range() needs those values to be able to compare them with the boundaries. Check the CreateIndex() docs for more info on the values field.

By default, this is what our index returns:

> Paginate(Match(Index("Alphabet_by_letter_position")))

{
  data: [
    ["A", 1],
    ["B", 2],
    ["C", 3],
    ["D", 4],
    ["E", 5],
    ["F", 6],
    ["G", 7],
    ["H", 8],
    ["I", 9],
    ["J", 10]
  ]
}
Enter fullscreen mode Exit fullscreen mode

We're now ready to make our first range query. For each boundary, Range() accepts either a single scalar value or an array. Let's use these the single values of A and E for now:

> Paginate(
  Range(
    Match(Index("Alphabet_by_letter_position")),
    "A",
    "E"
  )
)

{
  data: [
    ["A", 1],
    ["B", 2],
    ["C", 3],
    ["D", 4],
    ["E", 5]
  ]
}
Enter fullscreen mode Exit fullscreen mode

As we can see, Range() has filtered the results that fall within the defined boundaries by comparing the start and end values with the first item of each result.
Alt Text
Because the string "A" is superior or equal to the start value of "A" then ["A", 1] is included in the results. Likewise, because the string "F" is superior to the end value of "E" then ["F", 6] is not included in the results.

Instead of a single value for the boundaries, we could also use an array with a single item. The result would be the same:

> Paginate(
  Range(
    Match(Index("Alphabet_by_letter_position")),
    ["A"],
    ["E"]
  )
)

{
  data: [
    ["A", 1],
    ["B", 2],
    ["C", 3],
    ["D", 4],
    ["E", 5]
  ]
}
Enter fullscreen mode Exit fullscreen mode

It's also possible to use an empty array to tell FaunaDB to use the first or last result as start or end values, respectively, for the boundaries. In this case we'll get everything between the first result up to "C":

> Paginate(
  Range(
    Match(Index("Alphabet_by_letter_position")),
    [],
    "C"
  )
)

{
  data: [
    ["A", 1],
    ["B", 2],
    ["C", 3]
  ]
}
Enter fullscreen mode Exit fullscreen mode

Multi-item boundaries

An interesting aspect of Range() is that we can use an array with multiple values for our boundaries. The results might not be what you're expecting though!

See this example:

> Paginate(
  Range(
    Match(Index("Alphabet_by_letter_position")),
    ["A", 3],
    ["G", 4]
  )
)

{
  data: [
    ["B", 2],
    ["C", 3],
    ["D", 4],
    ["E", 5],
    ["F", 6]
  ]
}
Enter fullscreen mode Exit fullscreen mode

The first time I ran that query I was confused since I expected each item in the start/end arrays to act as its own range filter, so to speak, but that's not how Range() works.

Each start/end array is actually used as a single value to determine which results are superior or inferior.
Alt Text
As you can see, FaunaDB considers ["F", 6] to be inferior to the upper limit of ["G", 4], but how does that work?

Think of it this way. If you were sorting strings alphabetically, which one would come first: "A8" or "Z1"? Obviously "A8" because A comes before Z, right? Since the first character has the highest priority when sorting we don't care about the second one to determine which string comes first.

This also explains why FaunaDB considers ["G", 7] to be superior to ["G", 4] and excludes it from the results. The first item is the same (the string "G") so only the second item affects the comparison.

Let's see another example. Imagine we had a collection of people with their age and name and we had an index that returned these results:

> Paginate(Match(Index("People_by_age_name")))

{
  data: [
    [27, "Alex"],
    [33, "Abigail"],
    [39, "Adam"],
    [41, "Pier"],
    [50, "Anna"],
    [64, "Charles"]
  ]
}
Enter fullscreen mode Exit fullscreen mode

If we now wanted to get all the people between 30 and 60 we could execute the following query:

> Paginate(
  Range(
    Match(Index("People_by_age_name")),
    30, 60
  )
)

{
  data: [
    [33, "Abigail"],
    [39, "Adam"],
    [41, "Pier"],
    [50, "Anna"]
  ]
}
Enter fullscreen mode Exit fullscreen mode

So far so good.

What if we now wanted to refine those results and get the names between A and B?

We wouldn't be able to use Range() to solve this for the reasons I explained before:

> Paginate(
  Range(
    Match(Index("People_by_age_name")),
    [30, "A"],
    [60, "B"]
  )
)

{
  data: [
    [33, "Abigail"],
    [39, "Adam"],
    [41, "Pier"],
    [50, "Anna"]
  ]
}
Enter fullscreen mode Exit fullscreen mode

Even though the string "Pier" obviously comes after the string "B", FaunaDB is only taking the age into consideration to determine that [41, "Pier"] is inferior to the upper bound of [60, "B"].
Alt Text

Range and then filter

So how would we actually get all the people between 30 and 60 but also with names between "A" and "B"?

The solution is to simply iterate over the results of Range() and then express any needed condition using FQL:

> Paginate(
  Filter(
    Range(
      Match(Index("People_by_age_name")),
      [30],
      [60]
    ),
    Lambda(
      ["age", "name"],
      And(
        GTE(Var("name"), "A"),
        LTE(Var("name"), "B"),
      )
    )
  )
)


{
  data: [
    [33, "Abigail"],
    [39, "Adam"],
    [50, "Anna"]
  ]
}
Enter fullscreen mode Exit fullscreen mode

Here we're using GTE() (greater than or equal) and LTE() (less than or equal) to compare the name and making sure both conditions return true with And().

We could refine this query even further by, for example, only listing people that also have an "n" or an "i" in their names:

> Paginate(
  Filter(
    Range(
      Match(Index("People_by_age_name")),
      [30],
      [60]
    ),
    Lambda(
      ["age", "name"],
      And(
        GTE(Var("name"), "A"),
        LTE(Var("name"), "B"),
        Or(
          ContainsStr(Var("name"), "n"),
          ContainsStr(Var("name"), "i")
        )
      )
    )
  )
)

{
  data: [
    [33, "Abigail"],
    [50, "Anna"]
  ]
}
Enter fullscreen mode Exit fullscreen mode

ContainsStr() will return true when a string contains the string defined in its second parameter, in this case an "n" or an "i".

These conditions can be as complex as you need them, we're really just scratching the surface. Here are some useful FQL functions you should check out to compare values and express conditions:

Index and then range

So far, in this article we've only used indexes that return all of the documents in a collection and then filtered those results using Range(). We can of course use indexes with terms that already select a number of documents before filtering with a range.

Let's create a new collection:

> CreateCollection({
  name: "RobotRepairs"
})
Enter fullscreen mode Exit fullscreen mode

And an index that allows us to filter by type and also provide the necessary terms to be able to use Range() :

> CreateIndex({
  name: "RobotRepairs_startTs_endTs_type_by_type",
  source: Collection("RobotRepairs"),
  values: [
    {field: ["data", "startTs"]},
    {field: ["data", "endTs"]},
    {field: ["data", "type"]},
    {field: ["ref"]}
  ],
  terms: [
    {field: ["data", "type"]}
  ]
})
Enter fullscreen mode Exit fullscreen mode

Now, let's also insert a couple of documents with this format:

> Create(
  Collection("RobotRepairs"),
  {
    data: {
      startTs: Time("2020-09-25T10:00:00Z"),
      endTs: Time("2020-09-27T18:00:00Z"),
      type: "CPU_REPLACE"
    }
  }
)
Enter fullscreen mode Exit fullscreen mode

This is what this index returns when filtering by "CPU_REPLACE" :

> Paginate(
  Match(
    Index("RobotRepairs_startTs_endTs_type_by_type"),
    "CPU_REPLACE"
  )
)

{
  data: [
    [
      Time("2020-09-22T11:00:00Z"),
      Time("2020-09-23T13:00:00Z"),
      "CPU_REPLACE",
      Ref(Collection("RobotRepairs"), "278203011981902355")
    ],
    [
      Time("2020-09-25T10:00:00Z"),
      Time("2020-09-27T18:00:00Z"),
      "CPU_REPLACE",
      Ref(Collection("RobotRepairs"), "278203042867708435")
    ],
    [
      Time("2020-10-01T17:00:00Z"),
      Time("2020-10-01T19:00:00Z"),
      "CPU_REPLACE",
      Ref(Collection("RobotRepairs"), "278202807195009555")
    ]
  ]
}
Enter fullscreen mode Exit fullscreen mode

So now, we could first filter by repair type, and then use Range() to only get the repairs done between two timestamps:

> Paginate(
  Range(
    Match(
      Index("RobotRepairs_startTs_endTs_type_by_type"),
      "CPU_REPLACE"
    ),
    [
      Time("2020-10-01T00:00:00Z"),
      Time("2020-10-01T00:00:00Z")
    ],
    [
      Time("2020-10-02T00:00:00Z"),
      Time("2020-10-02T00:00:00Z")
    ]
  )
)

{
  data: [
    [
      Time("2020-10-01T17:00:00Z"),
      Time("2020-10-01T19:00:00Z"),
      "CPU_REPLACE",
      Ref(Collection("RobotRepairs"), "278202807195009555")
    ]
  ]
}
Enter fullscreen mode Exit fullscreen mode

To get actual documents instead of arrays with values, we'd need to use Map() with Lambda() and Get() :

> Map(
  Paginate(
    Range(
      Match(
        Index("RobotRepairs_startTs_endTs_type_by_type"),
        "CPU_REPLACE"
      ),
      [
        Time("2020-10-01T00:00:00Z"),
        Time("2020-10-01T00:00:00Z")
      ],
      [
        Time("2020-10-02T00:00:00Z"),
        Time("2020-10-02T00:00:00Z")
      ]
    )
  ),
  Lambda(
    ["startTs", "endTs", "type", "ref"],
    Get(Var("ref"))
  )
)

{
  data: [
    {
      ref: Ref(Collection("RobotRepairs"), "278202807195009555"),
      ts: 1601580160340000,
      data: {
        startTs: Time("2020-10-01T17:00:00Z"),
        endTs: Time("2020-10-01T19:00:00Z"),
        type: "CPU_REPLACE"
      }
    }
  ]
}
Enter fullscreen mode Exit fullscreen mode

Range with index bindings

In a previous article, we learned about index bindings, which are pre-computed values on index results.

Let's create a new index, that returns the duration of each repair, using a binding for a particular repair type:

> CreateIndex({
  name: "RobotRepairs_duration_type_by_type",
  source: {
    collection: Collection("RobotRepairs"),
    fields: {
      durationMinutes: Query(
        Lambda("doc",
          TimeDiff(
            Select(["data", "startTs"], Var("doc")),
            Select(["data", "endTs"], Var("doc")),
            "minutes"
          )
        )
      )
    }
  },
  values: [
    {binding: "durationMinutes"},
    {field: ["data", "type"]},
    {field: ["ref"]}
  ],
  terms: [
    {field: ["data", "type"]}
  ]
})
Enter fullscreen mode Exit fullscreen mode

This is what this index returns by default:

> Paginate(
  Match(
    Index("RobotRepairs_duration_type_by_type"),
    "CPU_REPLACE"
  )
)

{
  data: [
    [
      120,
      "CPU_REPLACE",
      Ref(Collection("RobotRepairs"), "278202807195009555")
    ],
    [
      1560,
      "CPU_REPLACE",
      Ref(Collection("RobotRepairs"), "278203011981902355")
    ],
    [
      3360,
      "CPU_REPLACE",
      Ref(Collection("RobotRepairs"), "278203042867708435")
    ]
  ]
}
Enter fullscreen mode Exit fullscreen mode

We could now use Range() to only get the repairs that lasted less than 1 day (or 1,440 minutes):

> Paginate(
  Range(
    Match(Index("RobotRepairs_duration_type_by_type"), "CPU_REPLACE"),
    [],
    [1440]
  )
)

{
  data: [
    [
       120,
       "CPU_REPLACE",
       Ref(Collection("RobotRepairs"), "278202807195009555")]
  ]
}
Enter fullscreen mode Exit fullscreen mode

Conclusion

So that's it for today. Hopefully you learned something valuable!

If you have any questions don't hesitate to hit me up on Twitter: @pierb

Discussion (3)

Collapse
wilsonm54598619 profile image
Wilson Masih

Garmin is an American multinational technology company founded in 1989 by Gary Burrell and Min Kao in Lenexa, Kansas, United States, with headquarters in Olathe, Kansas.Their website is garmin. Since 2010, the company is incorporated in Schaffhausen, Switzerland garmin express is an application that is used to monitor and watch the working of the devices on your computer. it can be used for registration of a new product, store all the fitness data, synchronization of the remote device data and everything else concerned.
We at garmin lifetime updater provide updates and installation for every Garmin products like Garmin Nuvi, Garmin Drive, Garmin dezl etc.
You can visit the blog to get any type updates and issues related to Garmin Devices .Chat icon on the website is available for instant help.Roku is a line of digital media players manufactured by
American company Roku, Inc. roku.com/link.offer access to streaming media content from various online services.
Visit garmin map updates free download 2020 for free downloads. garmin nuvi 1300 update allows you to update your garmin device,to properly update your device, install the Garmin Express app on your computer. The software, which updates most Garmin devices including the Nuvi model, is available free and is compatible with systems running Windows Vista SP2 or newer as well as Mac OS 10.10 or newer operating systems.
Visit Igarminto know more about garmin devices.

Collapse
hellverse8 profile image
hellverse

Garmin Express is an application which is used to easily update maps and software, sync with garmin connect and register your Device. You can download garmin.com/express software from official garmin site, where you will get options to download for Windows and for Mac.

The Garmin lifetime updater is a free program which lets you update and manage maps on your garmin device. You can download Garmin lifetime updater for Windows and MAC from official garmin site. The latest version of garmin lifetime updater is 2.1.11 which is available for both, 32 and 64 bit pcs.

Garmin Express software has many uses, such as Update your maps, sync with connect, update your software, manage content, udate marin charts, redeem a voucher and golf course updates. You can add your device to Garmin express after logging into garmin account. Follow the steps given on garmin express login .

Garmin sat nav devices comes with a variety of applications. But it is crucial to keep those devices up to date. Visit garmin sat nav update to update your maps. You can also update your sat nav devices of camper van or RV using this method. Garmin sat nav update provides you European maps

Garmin express software has various versions which kept changing. Garmin express allows you to download and update garmin maps for free. Garmin express was used in 2018, 2019 and 2020, garmin map updates free download 2020 to download Free garmin express software.

Garmin.com/express is official site to download Garmin Express. You can download garmin express for mac by clicking on that option. Garmin Express is also available on mac book pro. Garmin express software is not available on ios.

Garmin provides a range of GPS devices including zumo, smart drive and Garmin nuvi. Garmin nuvi devices are used for gps navigation, and they can be updated using Garmin Express software. Garmin nuvi update is a very important and not-to-miss step, as older devices are more vulnerable without updates.