Most Powerful Open Source ERP

How To Create Related Keys

How To showing how to extend the CatalogTool from ERP5Catalog to add new search keys.
  • Last Update:2016-02-09
  • Version:001
  • Language:en

Related Keys allow joining tables together, to verify conditions on other documents (via category relationships or parend_uid typically) or on columns not available on catalog table.

Table of Contents

Dynamic related keys

Catalog generates related keys representing category relationships between documents for base categories defined in a site.

Example using subordination base category, listing all documents having a subordination relationship with a document whose reference is 'nexedi':


portal_catalog(subordination_reference='nexedi')

The general form is:

[default_][strict_]<base category>_[related_]<column>

Prefixing with default_ has no special effect. It is a handy way to reach a dynamic related key when there is a name collision with custom related key.

strict_ prevents category hierarchy from being considered:

>>> document_1.getRegion()
'europe/west/france'
>>> document_2.getRegion()
'europe/west/spain'
>>> document_3.getRegion()
'europe/central/poland'
>>> document_4.getRegion()
'europe/west/france/nord'

>>> portal_catalog(strict_region_uid=europe.west.getUid())
[]
>>> portal_catalog(region_uid=europe.west.getUid())
[document_1, document_2, document_4]
>>> portal_catalog(strict_region_uid=europe.west.france.getUid())
[document_1]

related_ reverses the relationship:

>>> document_1.getSource()
'foo_module/document_2'
>>> document_2.getSource()
None

>>> portal_catalog(source_uid=document_2.getUid())
[document_1]
>>> portal_catalog(source_related_uid=document_1.getUid())
[document_2]

All catalog table columns are available for use as column.

All defined base categories except 'parent' are available for use as base category.

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 one of the following (table_0, table_1... are aliases for any table, you will define right after what will correspond to each of them).

Legacy

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.

Current (from SVN revision 41966 or Git commit 0349c57)

  • Parameters:
    table_0
    table_1
    table_2
    table_3
    RELATED_QUERY_SEPARATOR
    query_table
    
  • Code:
    <dtml-var table_1>.uid = <dtml-var table_2>.uid
    AND <dtml-var table_1>.base_category_uid = <dtml-var "portal_categories.destination.getUid()">
    
    <dtml-var RELATED_QUERY_SEPARATOR>
    <dtml-var table_0>.category_uid=<dtml-var table_2>.uid
    
    <dtml-var RELATED_QUERY_SEPARATOR>
    <dtml-var table_1>.category_uid = <dtml-var table_3>.uid
    
    <dtml-var RELATED_QUERY_SEPARATOR>
    <dtml-var table_0>.base_category_uid = <dtml-var "portal_categories.causality.getUid()">
    AND <dtml-var table_0>.uid = <dtml-var table_1>.uid
    AND <dtml-var table_0>.uid = <dtml-var query_table>.uid
    

Some notes about the current implementation:

  • The number of table aliases should match the number of join conditions list, otherwise it will fallback on backward-compatibility code which may not work if you have LEFT JOIN as well.
  • The catalog table must be at the end as it is joined first.

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 build``Sql``Query with any SQL Method. build``Sql``Query takes many keywords arguments and returns:

  • the list of tables to use (and their aliases).
  • the "Where expression", that should be appended 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 ...
    

Related Articles