How to Create Related Keys

Related Keys allow to extends CatalogTool from ERP5Catalog to add new search keys. Related Keys are a way to turn complex search keys into SQL Statements.

Dynamic related key

For simple category relation, the catalog is already able to generate automatically for you related keys. Let's use the example of the subordination category, we want to be able to query catalog like that :

  portal_catalog( portal_type = 'Person', subordination_title = 'nexedi' )

You have nothing to do, this is already working. For every category all theses properties are available currently : 'title', 'uid', 'description', 'reference', 'relative_url', 'id', 'portal_type', 'simulation_state'. So this means for every category, following parameters are available for the catalog :

  [category_id]_title
  [category_id]_uid
  [category_id]_description
  [category_id]_reference
  [category_id]_relative_url
  [category_id]_id
  [category_id]_portal_type
  [category_id]_simulation_state

Creating a related key

Let's use a more complex case, let's say you want to search for the title of the destination of the causality of kind of document like this:

  portal_catalog( portal_type = 'Sale Packing List', causality_destination_title = 'nexedi' )

We have to create a ZSQLMethod in default SQLCatalog ( usually portal_catalog/erp5_mysql_innodb ). This is called a Catalog Method.The content of the "z_related_causality_destination" zsql method should be like this :

parameters :
  table_0
  table_1
  table_2
  table_3
code :

<dtml-var table_0>.uid = catalog.uid
AND <dtml-var table_0>.base_category_uid = <dtml-var "portal_categories.causality.getUid()">
AND <dtml-var table_0>.category_uid=<dtml-var table_2>.uid
AND <dtml-var table_1>.uid = <dtml-var table_2>.uid
AND <dtml-var table_1>.base_category_uid = <dtml-var "portal_categories.destination.getUid()">
AND <dtml-var table_1>.category_uid = <dtml-var table_3>.uid

table_0, table_1... are aliases for any table, you will define right after what will correspond to each of them.

Registering the related key in CatalogTool

Then still under the default SQLCatalog, you should select the tab "properties", go to the "sql_catalog_related_keys" field and add this line :

causality_destination_title | category,category,catalog,catalog/title/z_related_causality_destination

The first part (before |) is the name of catalog parameter, after you put table name for wich an alias will be created and available under respectively table_0, table_1, table_2.... On the last table you can specify wich column will be specified by the catalog parameter, and finally you have to specify the zsql method.

Now the "causality_destination_title" is ready to use.

Complex related keys

eg. forcing the portal_type of the subordination related object or returning all objects which have matching subobjects (career_skill_id)

Using related keys in a specific ZSQLMethod

maybe this should move to "HowToUseRelatedKey"

It's feasible to use buildSqlQuery with any SQL Method. buildSqlQuery takes many keywords arguments and returns :

- the list of tables to use (and their aliases).

- the "Where expression", that should be happended in the query:

SELECT
  catalog.path
FROM
<dtml-in prefix="table" expr="from_table_list">
  , <dtml-var table_item> AS <dtml-var table_key>
</dtml-in>
WHERE
<dtml-if where_expression>
  <dtml-var where_expression>
</dtml-if>
AND ...