Skip to main content

Reverse Sorting with Join

Lavanya Davuluri
  • Updated

Pagination refers to the process of splitting large result sets into smaller "pages". Doing so relieves the server, and the client, from dealing with potentially huge result sets in one step. 

Fauna pagination is achieved using the Paginate function.

(At the time of writing) There is a known issue with the results of Pagination over a Join involving reverse indexes. This is documented here - FQL - Known Issues

 

This article briefly describes the problem with an example and provides a workaround to achieve accurate results.

We are using the demo data available with the Dashboard for simplicity.

 

Problem description:

When a query involves Join operation on a reverse sorted index, retrieving consecutive pages with Pagination does not work as expected.

For example, the following query paginates over the inventory levels for products in a particular store, giving 2 results per page.

 

Paginate(
  Join(
    Match(
      Index('products_by_store'),
      Ref(Collection('stores'), '301'),
    ),
    Lambda(
      ['name', 'description', 'price'],
      Match(Index('inventory_by_product'), Var('name'))
    )
  ), {size:2}
 )

 

{

  after: [
    100, "Organic, 1 bunch", Ref(Collection("products"), "208"), 
    Ref(Collection("products"), "208")
],

  data: [
    [1000, "Conventional Hass, 4ct bag", Ref(Collection("products"), "204")],
    [1000, "Conventional, 1 ct", Ref(Collection("products"), "205")]
  ]
}

 

When retrieving the next page of results, the after cursor from the previous result is included, and we find that the results do not change. That is, they are reset to the first page.


Paginate(
  Join(
    Match(
      Index('products_by_store'),
      Ref(Collection('stores'), '301'),
    ),
    Lambda(
      ['name', 'description', 'price'],
      Match(Index('inventory_by_product'), Var('name'))
    )
  ),
  {size:2,
  after: [
    100,
    "Organic, 1 bunch",
    Ref(Collection("products"), "208"),
    Ref(Collection("products"), "208")
  ]
  }
)

 

{
  before: [
    100,
    "Organic, 1 bunch",
    Ref(Collection("products"), "208"),
    Ref(Collection("products"), "208")
  ],
  after: [
    100,
    "Organic, 1 bunch",
    Ref(Collection("products"), "208"),
    Ref(Collection("products"), "208")
  ],
  data: [
    [1000, "Conventional Hass, 4ct bag", Ref(Collection("products"), "204")],
    [1000, "Conventional, 1 ct", Ref(Collection("products"), "205")]
  ]
}

 

Note that the Index inventory_by_product in the example is reverse sorted on the value quantity.

{
  ref: Index("inventory_by_product"),
  ts: 1633146464480000,
  active: true,
  serialized: true,
  name: "inventory_by_product",
  source: Collection("products"),
  terms: [{ field: ["data", "name"] }],
  values: [
    { field: ["data", "quantity"], reverse: true },
    { field: ["data", "description"] },
    { field: ["ref"] }
  ],
  partitions: 1
}

 

Workaround:

Use Union and Lambda instead of Join with the same Indexes.

The same query can be rewritten as the following:

Paginate(
  Let(
    {
      product_set: Match(
        Index("products_by_store"),
        Ref(Collection("stores"), "301")
      ),
      products_page: Select(
        "data",
        Paginate(Var("product_set"), { size: 100000 })
      ),
      leaf_sets: Map(
        Var("products_page"),
        Lambda(
          ["name", "description", "price"],
          Match(Index("inventory_by_product"), Var("name"))
        )
      )
    },
    Union(Var("leaf_sets"))
  ),
  { size: 2 }
)
{
  after: [
    100,
    "Organic, 1 bunch",
    Ref(Collection("products"), "208"),
    Ref(Collection("products"), "208")
  ],
  data: [
    [1000, "Conventional Hass, 4ct bag", Ref(Collection("products"), "204")],
    [1000, "Conventional, 1 ct", Ref(Collection("products"), "205")]
  ]
}

Now for the next Page,

Paginate(
  Let(
    {
      product_set: Match(
        Index("products_by_store"),
        Ref(Collection("stores"), "301")
      ),
      products_page: Select(
        "data",
        Paginate(Var("product_set"), { size: 100000 })
      ),
      leaf_sets: Map(
        Var("products_page"),
        Lambda(
          ["name", "description", "price"],
          Match(Index("inventory_by_product"), Var("name"))
        )
      )
    },
  Union(Var("leaf_sets"))
  ),
  {
    size: 2,
      after: [
      100,
      'Organic, 1 bunch',
      Ref(Collection("products"), "208"),
      Ref(Collection("products"), "208")
    ]
  }
)

This gives us expected results:

{
  before: [
    100,
    "Organic, 1 bunch",
    Ref(Collection("products"), "208"),
    Ref(Collection("products"), "208")
  ],
  after: [
    30,
    "Conventional, 16 oz bag",
    Ref(Collection("products"), "207"),
    Ref(Collection("products"), "207")
  ],
  data: [
    [100, "Organic, 1 bunch", Ref(Collection("products"), "208")],
    [50, "Organic, 16 oz bag", Ref(Collection("products"), "206")]
  ]
}

 

Points to note:

  • This issue occurs only if the Index is reverse sorted.
  • The workaround using Union consumes more read-ops than the Join approach. Please see per query metrics to see resources used in the current query.