The stock portal_catalog lets you search database by a number of basic properties,
like id, uid, portal_type, title, description, validation_state, creation_date
and many more. Sometimes, however, you may need to add you own properties to it -
if you want to be able to quickly search on a property that you defined, or if
you need your custom property to be a sortable and searchable column in a
listbox powered by portal_catalog.
Table of Contents
Setup¶
This is how to achieve this - let's take an example: we need to be able to quickly
find an email address, and figure out to whom it belongs. Email adress is stored
in a property "url_string" of a portal_type "Email". We can search for it like this:
result=[r.getObject() for r in context.portal_catalog(portal_type='Email')]
adrs=[o for o in result if o.getUrlString()=='what@we.look.for']
But we don't want to do it, because it would give very poor perfomance.
The sql catalog is there to make things fast, let's use it.
-
Create a table in MySQL
The table will have two fields - uid
(unsigned bigint(20)) and url_string (varchar). The uid has to be a
primary key - this is important! Let's name the table 'email'.
-
Let the catalog search it
In portal_catalog/erp5_mysql/manage, in 'properties' tab, there is a box
'sql_search_tables', with some tables selected. Add the 'email' table to
the selection (Ctrl-click).
For searching, this is it - if you insert into the 'email' table uid and url_string
of an existing email address, portal_catalog will be able to find it. What we still
need to do is to make sure the table is created and cleared when necessary, and
that our email addresses catalog themselves automatically.
-
Set up catalog clearing
Create a Z SQL method 'z_create_email':
CREATE TABLE `email` (
`uid` BIGINT UNSIGNED NOT NULL,
`url_string` varchar(255),
PRIMARY KEY `uid` (`uid`),
KEY `url_string` (`url_string`)
) TYPE = InnoDB;
-
Create method 'z0_drop_email':
DROP TABLE IF EXISTS email;
- In Properties tab, add both methods to those selected in 'sql_clear_catalog' box.
Make portal_catalog record url_string upon reindexing
When you call a 'reindexObject' method on an ERP5 object, you actually tell
portal_catalog to execute all z_ methods defined in the erp5_mysql catalog
and selected in the 'sql_catalog_object_list' box. So: Create a ZSQL method
'z_catalog_email_list':
arguments: uid, getUrlString, getPortalType
<dtml-let email_list="[]">
<dtml-in prefix="loop" expr="_.range(_.len(uid))">
<dtml-if expr="getPortalType[loop_item]=='Email'">
<dtml-call expr="email_list.append(loop_item)">
</dtml-if>
</dtml-in>
<dtml-if expr="_.len(email_list) > 0">
REPLACE INTO
email (`uid`, `url_string`)
VALUES
<dtml-in prefix="loop" expr="email_list">
(
<dtml-sqlvar expr="uid[loop_item]" type="int">,
<dtml-sqlvar expr="getUrlString[loop_item]" type="string" optional>
)
<dtml-if sequence-end><dtml-else>,</dtml-if>
</dtml-in>
</dtml-if>
</dtml-let>
The tricky part here is that your method is executed every time you catalog
anything, so it has to check whether the object is actually and Email address,
otherwise it would raise an error. So it filters the list of objects to be
catalogged, and, if any are found, puts them into the email table. Add it to
the selection in 'sql_catalog_object_list' box. Create a ZSQL method
'z0_uncatalog_email':
arguments: uid
DELETE FROM email WHERE <dtml-sqltest uid op=eq type=int>
Add it to the selection in 'sql_uncatalog_object' box.
Voila - you're done. Now you can search and sort on it in a listbox exactly as
you do on title, id or description, and search for it like this:
emails=context.portal_catalog(portal_type='Email',url_string='what@we.look.for')
If you had some emails in your database before, don't forget to reindex them!
Also, keep in mind that catalog propeties are cached, so all this may not work
until you clear the cache (in portal_caches tool, or using erp5admin Firefox extension).
Related Articles¶