Intersect indexes that return Array and Ref

Help
,
1

I need to combine two or more indexes for my product collection to provide search filters in my application. Per example, the user can be filter by lower or higher price, by category (shirts, skirts, dresses, …), and so on. But, my problem is that I need to sort some of this indexes by your field, per instance, all_products_by_lower_price return an array of the price and the ref where the price determines the order, while the the index all_products_by_category return only the refs. So, I need an approach to intersect the results of each index.

2

This answer might be helpful?

1 Like
3

I need to order by price (low or high) and intersect with the by category index, but when using Join to get only the refs (dropping prices) the order is broken. How to use Join keeping the original ordering the by price index?

Paginate(
  Intersection(
    Join(
      Match(Index("all_products_by_category"), "blusas"),
      Lambda("ref", Match("product_ref_by_ref", Var("ref")))
    ),
    Join(
      Match(Index("all_products_by_price_asc")),
      Lambda(["price", "ref"], Match(Index("product_ref_by_ref"), Var("ref")))
    ),
    Join(
      Match(Index("all_products_by_views")),
      Lambda(["views", "ref"], Match(Index("product_ref_by_ref"), Var("ref")))
    )
  )
)
4

err… it’s not a great solution but you could make an index that uses join to resort the intersected set. term = ref, values = (price, ref)

6

I am having this same issue posted here Having issues returning results sorted by Date. It seems that Join() with a refbyref does not function as expected. The expected result is a set sorted by the order of the first index. In my case date and in this case price. But for some reason when the sorted index is passed into the Lambda() the result seems to get unsorted again. Strange.

7

Have you read this thread?
The workaround proposed to me did not fit my case, but maybe you could find something useful.

8

I’d like to follow on to this question as I cannot find an answer, has anyone worked out a solution to this? I am having similar problems preserving the sort integrity of one index after using Join to bind it to a refbyref index. Whilst I understand why this happens (i.e. sorting based on 'ref'after the merge), is there another work around that will allow preservation of said order and avail of inbuilt Pagination commands?

If there is no way around this, I believe this will prove to be a real limitation for FQL if there is no other alternative. Ultimately the issue arises due to the fact that you may have one unique index (to a user/location - values: ['sort_result','ref']) and any number of generic indexes (used as filters for all users/locations - values: ['ref']). You will not be able to preserve sort order derived from the unique index which would be sorting based on values unique to that user/location without paginating prior to combining with generic indexes. The flow on from this is that pagination commands are near worthless under this use case because pagination limits will be applied prior to combination. After combination, the page/array size will be varied. It’s that, or I work on paginating manually.

If “paginating” manually, is there a command that allows you to slice an array without using multiple Drop() and Reverse() calls on the index (reading entire collection) and severely slowing down function time?

@Jay-Fauna @Luigi_Servini @databrecht any help would be really valuable for me right now. This is the last barrier to production for us and is a real pivot point for this database as I’ve been working on a way around this for a few months now.

9

@ben, how would this work? Isn’t it the Join that erased the sort order in the first place? How could another Join help you re-sort the output, especially now it sorts based on ‘ref’?

10

@Darryl_Naidu Did this workaround not help you?

11

Hi all,

I actually worked this out myself using an interpretation of @ben’s explanation. Ultimately my function finally defined the sorted output page as:

Paginate(
   Join(
      Var("filterIndex"),
      Lambda(
         ["ref"],
         Match(
            Index("sortIndex"),
            Var("ref")
         )
      )
   ),
   { after: Var("after"), size: Var("limit") }
)

Where sortIndex defined as:

{
  name: "sortIndex",
  serialized: true,
  source: "[Insert collection]",
  terms: [
    {
      field: ["ref"]
    }
  ],
  values: [
    {
      binding: "sort_result",
      reverse: true
    },
    {
      field: ["ref"]
    }
  ]
}

and filterIndex defined as:

{
  name: "filterIndex",
  unique: false,
  serialized: true,
  source: [insert collection],
  terms: [
    {
      field: ["data", [insert field]]
    }
  ]
}

This took me too long to get my head around as I didn’t think to filter first and then sort (I was initially trying to sort, then filter which I still can’t get to work) so hopefully this can help anyone else, especially if the following doesn’t simply address your use case:

The concept is the same though it helps if someone has been down an identical path before you and dropped a few crumbs along the way.

1 Like