This discussion is intended to track what are the current issues with predicate searching and help defining a solution.

Table of Contents

• Note
• Example
• SQL
• Explanation
• Related Articles

Note¶

This discussion is intended to track what are the current issues with predicate searching (DomainTool's generateMappedValue and searchPredicateList) and help defining a solution

Example¶

This is what a predicate search currently looks like

  SELECT DISTINCT
   catalog.path,   catalog.uid
  FROM
   roles_and_users AS related_roles_and_users_0, catalog AS catalog, predicate_category AS predicate_category, predicate AS predicate
  WHERE
   1 = 1
   AND ((( (predicate.quantity is NULL) AND (predicate.quantity_range_min is NULL) AND (predicate.quantity_range_max is NULL)OR (predicate.quantity = '1.0') OR (predicate.quantity_range_min <= '1.0') AND 
  (predicate.quantity_range_max is NULL) OR (predicate.quantity_range_min is NULL) AND predicate.quantity_range_max > '1.0' OR (predicate.quantity_range_min <= '1.0') AND predicate.quantity_range_max > 
  '1.0'  ) AND
  ( (predicate.start_date is NULL) AND (predicate.start_date_range_min is NULL) AND (predicate.start_date_range_max is NULL) )) AND
  ((predicate_category.category_uid = 6601 AND predicate_category.base_category_uid = 882) OR (predicate_category.category_uid = 4446 AND predicate_category.base_category_uid = 4495))) AND (predicate.uid 
  != '0' AND predicate_category.uid != '0' AND (related_roles_and_users_0.allowedRolesAndUsers = 'Member' OR related_roles_and_users_0.allowedRolesAndUsers = 'Manager' OR 
  related_roles_and_users_0.allowedRolesAndUsers = 'Authenticated' OR related_roles_and_users_0.allowedRolesAndUsers = 'Anonymous' OR related_roles_and_users_0.allowedRolesAndUsers = 'user:alex' OR 
  related_roles_and_users_0.allowedRolesAndUsers = 'user:role:Anonymous' OR related_roles_and_users_0.allowedRolesAndUsers = 'user:role:Authenticated') AND catalog.has_cell_content = 0 AND *
  (catalog.portal_type = 'Purchase Supply Cell' OR catalog.portal_type = 'Purchase Supply Line' OR catalog.portal_type = 'Sale Supply Cell' OR catalog.portal_type = 'Sale Supply Line' OR 
  catalog.portal_type = 'Supply Cell' OR catalog.portal_type = 'Supply Line') AND catalog.uid = predicate_category.uid AND catalog.uid = predicate.uid AND 
  related_roles_and_users_0.uid = catalog.security_uid)
  LIMIT 1000

SQL¶

currently, there are two tables:

• - predicate (uid, quantity, quantity_range_min, quantity_range_max, start_date, start_date_range_min, start_date_range_max) - predicate_category (uid, category_uid, base_category_uid, category_strict_membership)

The sql query currently matches all predicates that satify these condition :

• - context quantity is within the range defined on the predicate - context start_date is within the range defined on the predicate - context category is present on the predicate

The test method is then called on all retrieved predicates, as a second level of filtering, and the list of predicates whose test method passes is then returned.

Explanation¶

The problem is that if context has more than one category, the SQL query will try to match them using 'OR' in between. So, for a sales order line with a resource, a destination, and some variations and options, we will have to test all the predicates that match the resource, or the destination, or any variation or option.

In a system where we deal with resources which share some of their variations (color, size), we will quickly select a huge amount of unneeded predicates (eg. all that have resource/shirt, plus all that have color/blue, plus all that have size/42) on which we will have to call the test method.

One sure thing is that we cannot expect all categories to be here all the time. for example variations and options are not present when retrieving the base_price from the line predicate, and options (maybe variations also) on predicates are mutually exclusive (cell predicates like additional price will only define one of them at a time)

One idea could be to force some categories, like resource, which should always be present in a predicate.getAcquiredCategoryList(), but the rule is not as simple as "force all categories that are not a variationor option", as for destination, we can have a predicate which does not define a destination, if we want it to be used for all customers.

Related Articles¶