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¶

• Guidelines | Add Column Portal Type If Listbox Contains Multiple Types
• Guidelines | Add Date In Listbox As Editable Date-Time Field
• Guidelines | Add Viewable Listbox Fields To More Columns
• Guidelines | Define Count Method As countFolder On Module Listbox
• Guidelines | Hide Listbox Editable Fields On The Form
• Guidelines | Listbox Method Is Named Using Module Type And Portal Type List
• Guidelines | Listbox Must Only Be Put In Form Bottom Group
• Guidelines | Listbox Selection Name Must Follow module_name_selection Pattern
• Guidelines | Listbox Selection Uses Name Selection Postfix
• Guidelines | Listbox Uses List, Stat Or Count Method
• Guidelines | Method In Listbox Is Prefixed With Listbox Section
• Guidelines | Never Set Search Row And Select Column On Listbox Inside Document View
• Guidelines | Set Form Action Base_doSelect On Forms
• Guidelines | Set Form Id To ModulePortalType_viewPortalTypeList
• Guidelines | Set Form Title To What Is Displayed
• Guidelines | Set Listbox Title To Plural Of Portal Type Title If Applicable
• Guidelines | The Listbox Method Should Be Defined As SearchFolder To View Modules
• Guidelines | Use form_list Page Template On Forms
• Guidelines | Use Method Post And UTF-8 Encoding On Forms
• HowTo | Configure a Listbox
• HowTo | Edit Listbox Data Source
• HowTo | Label Listbox Columns
• Technical Note | Predicate
• Technical Note | Predicates Performances