CODEWITHSUNDEEP
arangodbaql
isawk
isawk

Reputation: 1057

Filter out product based on options

Am trying to compute a query to filter out products based on filterable options, color, size, e.t.c.

Use case: Obtain all products that have a size 8 and red

Attempt 1: Using Intersection

FOR product IN products
    FILTER product.options != null

    FOR productOption IN product.options
        FILTER productOption.option == "Size" AND LENGTH( INTERSECTION( productOption.value, ["8","14","16"] ) ) > 0
            AND productOption.option == "Color" AND LENGTH( INTERSECTION( productOption.value, ["Red"] ) ) > 0

RETURN product

Attempt 2: Using Multiple Filters

FOR product IN products
    FILTER product.options != null

    FOR productOption IN product.options
        FILTER productOption.option == "Size"
            FOR productSizeOptionValue IN productOption.value
                FILTER productSizeOptionValue IN ["8","10"]
        FILTER productOption.option == "Color"
            FOR productColorOptionValue IN productOption.value
                FILTER productColorOptionValue IN ["Red"]

RETURN product

Am not sure if my thinking should be along the lines of first obtaining possible product candidates using LET than merging the results of each individual LET query. Seemed rather clumsy! :(

Upvotes: 2

Views: 90

Answers (1)

stj
stj

Reputation: 9097

Attempt 1 will not work because it contains two AND-combined FILTER conditions on productOption.option, with different comparison values:

...
FILTER 
       productOption.option == "Size" 
   AND LENGTH(INTERSECTION(...))
   AND productOption.option == "Color"
   AND LENGTH(INTERSECTION(...))
...

This won't work, because the option value cannot be "Size" and "Color" at the same time.

I think it may be more sensible to model product options as separate attributes instead of using generic option arrays. This will work if a product can only have a single value for each distinct option but not multiple. In this approach, a product would look like this:

{ "Size": "8", "Color": "Red", "HasWheels": true }

Then filtering on product attributes become easier, e.g.

FOR product IN products
  FILTER product.Size IN [ "8", "10" ] 
     AND product.Color == "Red"
  RETURN product

Search conditions could then also be OR-combined, e.g. 

  FILTER product.Size IN [ "8", "10" ] 
      OR product.Color == "Red"

This approach will make querying much easier. It comes with one disadvantage though, and that is that if there are many different product attributes, you will not be able to index most of them due to memory constraint. This is not a problem if there are few different product attributes, or if all search conditions use a few common attributes that are selective enough that an index greatly speeds up the lookup.

Upvotes: 1

Related Questions