Dynamic queries - whats the best approach?

[Harriswe]

I would like to know the best approach to adopt when building a dynamic query. I have a Search table that captures various optional search criteria such as:

1. Taxonomy (single selection) or Taxonomy code pattern e.g. YYFC
2. Has list of Features (multiple select)
3. Date range: date a product was lunched (date range or any date)

See screen-shot below:

Each one of the conditions can be optional so if nothing is entered all products would be returned.

One approach is to use virtual columns for each one of the conditions above. Then use list subtraction to remove products that do not match the criteria, starting from the entire list of products

E.g.

[1] Pattern results:
[2] Feature results:
[3] Data range results:

The results virtual column applies the conditions using list subtraction as follows:

INTERSECT(
( Product[ProductUUID] - [Pattern Results] - [Feature results] ),
[Date range results]
)

I.e. start with entire population of ALL products.
Subtract those products that do not have the taxonomy code pattern selected
Subtract those products that do not have all the features selected
INTERSECT the remaining products with those launched within the date period.

Note when nothing is entered for one of the conditions the result of that condition = zero so there will be nothing to subtract.

Another approach is use the INTERSECT function on pairs of the conditions above, as you can only use the Intersect function with two conditions as a time.

Q1 Which of the two options outlined is more efficient, or is there a 3rd option which is better?
Q2 Is there an efficiency cost using virtual columns and hence should these be real columns in the DB?

At the moment the list subtraction approach works, but there appears to be a delay on all the results appearing. I.e. it quickly comes back with some results, then a 15-30 seconds later the full results set appears.

[Steve]

I don’t like INTERSECT(). I’d probably go with a subtraction model:

Product[ProductUUID]
- IFS(
  ISNOTBLANK([Features]),
    SELECT(
      Product[ProductUUID],
      NOT(...features-selector...)
    )
)
- IFS(
  ISNOTBLANK([Taxonomy]),
    SELECT(
      Product[ProductUUID],
      NOT(...taxonomy-selector...)
    )
)
...

I don’t see a compelling reason to use intermediate virtual columns unless you intend to use the intermediate results in some other way.

[Steve]

Have you considered using a slice?

[Harriswe]

Not sure how I would use a slice, since there are a number of different tables involved.

• Product (contains the launch date for the date range condition)
• ProductDefinition (contains the list of features a product has, also contains the taxonomy code)
• Search (keeps a record of the search criteria for each condition i.e. what to filter by and the subsequent result i.e a list of products.

I accept your point about the virtual columns and INTERSECT, which I thought may be costly to process.

[Steve]

It appears the final result presented to the user will be from the Product table. If so, a slice could be used. A slice row filter condition may access data in other tables and slices. The problem is accessing the data from your filter table.

The approach you’re already considering is valid, so probably best to continue down that path.

Note that if the results of you query are stored in a virtual column, the query will be re-performed every time the app syncs. Over time, this will gradually extend sync times. Consider an approach that will avoid accumulating old, unneeded filter results, such as reusing filter table rows.

[Harriswe]

Just to add, I understand the logic behind not using a virtual column for the results. That said the following screen shot shows

• Matching Products, which is the result stored as a field in the DB as an ENUMList.
• Results is the virtual column of the results set, seen here as a list.

The ‘list’ view provides the results as a clickable table, which is advantageous. The stored enum list doesn’t, which is less useful. My understanding is only virtual columns can be of type LIST and so I cannot show the ‘stored result’ Matching products as a clickable table list; or is that incorrect?

[Steve]

Harriswe:

My understanding is only virtual columns can be of type LIST and so I cannot show the ‘stored result’ Matching products as a clickable table list; or is that incorrect?

Normal and virtual columns can be of type List. Try implementing your virtual column as a normal column.

[Harriswe]

I did try a normal column as a ‘list’ type and I see the following message.

My workaround at the moment is to have the formula calculated in the normal column, so it is not re-calculated on the re-synch. I have a virtual column that refers to the normal column called MatchingProducts, to display the results as a clickable list. Not ideal, but appears to work fine.