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¶