How to optimally use FQL to convert from SQL that has OR and/or AND with <> in the WHERE clause?

Help
, ,
2

The most efficient way to perform the search that you want is to use Indexes. Combining multiple Indexes can take some planning to get the order of operations correct.

theory for WHERE clause

One thing you did not bring up is inequalities (i.e. searching for less-than or greater-than), but it’s important to note it. See this Stack Overflow post for a good discussion on that.

What we need is a separate Index with the respective term for every equality condition (= or <>), and an Index with the respective value for every inequality condition (<, >, <=, >=). Then we can use Set operations to combine the Indexes (See Set functions in the FQL cheat sheet for reference).

In general the following WHERE clauses will roughly translate into using FQL functions

  • AND → Intersection
  • OR → Union
  • NOT → Difference
  • any inequalities → Range

theory for ORDERED BY clause

For ORDER BY, sorting with FQL requires that either all Indexes output the specific value you wish to sort on, or by using the Join function to apply one additional Index to sort.

How to apply to your query

Let’s work from the innermost parts out. We’ll create the necessary Indexes and then build up the query piece by piece.

isRead = false

CreateIndex({
  name: "sometable_by_isRead",
  source: Collection("SomeCollection"),
  terms: [{ field: ["data", "isRead"] }]
})

isRead = false --> 

Match(Index("sometable_by_isRead"), false)

_ AND status <> “pending”

CreateIndex({
  name: "sometable_by_status",
  source: Collection("SomeCollection"),
  terms: [{ field: ["data", "status"] }]
})

Consider that _ <> _ is equivalent to NOT _ = _.

_ AND status <> "pending" -->
_ AND NOT status = "pending" --> 

Difference(_, Match(Index("sometable_by_status"), "pending"))

// including the previous Index is
Difference(
  Match(Index("sometable_by_isRead"), false), 
  Match(Index("sometable_by_status"), "pending")
)

_ OR (isShipping = true)

CreateIndex({
  name: "sometable_by_isShipping",
  source: Collection("SomeCollection"),
  terms: [{ field: ["data", "isShipping"] }]
})

_ OR (isShipping = true) -->

Union(_, Match(Index("sometable_by_isShipping"), true))

// including the previous Index is
Union(
  Difference(
    Match(Index("sometable_by_isRead"), false), 
    Match(Index("sometable_by_status"), "pending")
  ),
  Match(Index("sometable_by_isShipping"), true)
)

docType = “xyz” AND docID = “123-123-123-123” AND _

CreateIndex({
  name: "sometable_by_docType",
  source: Collection("SomeCollection"),
  terms: [{ field: ["data", "docType"] }]
})

CreateIndex({
  name: "sometable_by_docID",
  source: Collection("SomeCollection"),
  terms: [{ field: ["data", "docID"] }]
})

docType = "xyz" AND docID = "123-123-123-123" AND _ -->

Intersection(
  Match(Index("sometable_by_docType"), "XYZ"),
  Match(Index("sometable_by_docID"), "123-123-123-123"),
  _
)

// including the previous Index is
Intersection(
  Match(Index("sometable_by_docType"), "XYZ"),
  Match(Index("sometable_by_docID"), "123-123-123-123"),
  Union(
    Difference(
      Match(Index("sometable_by_isRead"), false), 
      Match(Index("sometable_by_status"), "pending")
    ),
    Match(Index("sometable_by_isShipping"), true)
  )
)

ORDER BY created DESC

For ORDER BY created DESC we will join with another Index.

CreateIndex({
  name: "sometable_by_ref__created_desc",
  source: Collection("SomeCollection"),
  terms: [{ field: ["ref"] }],
  values: [
    { field: ["data", "created"], reverse: true }, // this value to sort by
    { field: ["ref"] },                            // this ref to obtain the full Document
  ]
})

Join(
  Intersection(
    Match(Index("sometable_by_docType"), "XYZ"),
    Match(Index("sometable_by_docID"), "123-123-123-123"),
    Union(
      Difference(
        Match(Index("sometable_by_isRead"), false), 
        Match(Index("sometable_by_status"), "pending")
      ),
      Match(Index("sometable_by_isShipping"), true)
    )
  ),
  Index("sometable_by_ref__created_desc")
)

SELECT *

For the SELECT * part, we will use Map to transform our results into Documents.

Map(
  Paginate(
    Join(
      Intersection(
        Match(Index("sometable_by_docType"), "XYZ"),
        Match(Index("sometable_by_docID"), "123-123-123-123"),
        Union(
          Difference(
            Match(Index("sometable_by_isRead"), false), 
            Match(Index("sometable_by_status"), "pending")
          ),
          Match(Index("sometable_by_isShipping"), true)
        )
      ),
      Index("sometable_by_ref__created_desc")
    )
  ),
  Lambda(["created", "ref"], Get(Var("ref")))
)
3 Likes