ERP5 uses ZSQLCatalog, which is like Zope's ZCatalog, but using a relational database as a backend. ZSQLCatalog allows to query a Zope Catalog with a combination of traditional Zope Catalog queries (field based, text based) and with complex queries which are implicitly implemented as SQL methods. ComplexQuery defines a faster,more efficient and more complex search for information by using nested queries.It allows you to do a search using a more specific and precise set of criteria. In fact,ComplexQuery represents a Query with subqueries, combined with an operator. Typically, the operator can be anything, but it is generally "AND" or "OR".
Table of Contents
Example¶
from Products.ZSQLCatalog.SQLCatalog import ComplexQuery
from Products.ZSQLCatalog.SQLCatalog import Query
query=ComplexQuery(Query(title='%rose%'),
Query(portal_type='Person'),
logical_operator="AND")
return context.portal_catalog(query=query)
This will retrieve all documents of portal type 'Person' with a title containing 'rose'.
Combining Complex Queries¶
Lets first consider that with the method z_create_birth_details, we have already defined a table birth_details with custom fields birth_date and birthplace_text.
create table `birth_details` (
`uid` BIGINT(20) UNSIGNED NOT NULL,
`birth_date` datetime,
`birthplace_text` varchar(255) default '',
PRIMARY KEY (`uid`),
KEY `birth_date` (`birth_date`),
KEY `birthplace_text` (`birthplace_text`)
) TYPE=InnoDB;
For more information, on how to add a table to the catalog, please refer to HowToAddTableToCatalog. We can then use the following example to find all the persons that were born within a certain range of date.
from Products.ZSQLCatalog.SQLCatalog import ComplexQuery
from Products.ZSQLCatalog.SQLCatalog import Query
return context.portal_catalog(query=Query(
birth_date=[DateTime(1980, 1, 1), DateTime(1980, 12, 31)],
range='minmax'))
This will return all persons who were born between January 1st,1980 and December 31st,1980.
The following examples show the use of multiple subcomplexqueries to create a higher and more complex ComplexQuery.
from Products.ZSQLCatalog.SQLCatalog import ComplexQuery
from Products.ZSQLCatalog.SQLCatalog import Query
query = ComplexQuery(ComplexQuery(Query(title='%jean%'),
Query(birth_date='1985/02/20'),
Query(portal_type='Person'),
logical_operator="AND"),
ComplexQuery(Query(title='%jean%'),
Query(birthplace_text='Paris'),
Query(portal_type='Person'),
logical_operator="AND"),
ComplexQuery(Query(birth_date='1985/02/20'),
Query(birthplace_text='Paris'),
Query(portal_type='Person'),
logical_operator="AND"),
logical_operator="OR")
return context.portal_catalog(query=query)
This will use the catalog to return all persons with a title containing 'jean' and who were born on '1985/02/20' or all persons with a title containing 'jean' and who were born in 'Paris' or all persons who were born on 1985/02/20 in Paris.
Sorting Complex Queries¶
The results of the queries can also be sorted by using select_expression and selection_expression_key to define what the sorting will be based on.
from Products.ZSQLCatalog.SQLCatalog import ComplexQuery
from Products.ZSQLCatalog.SQLCatalog import Query
person_title='jean pierre boulanger'
query = ComplexQuery(ComplexQuery(Query(title=person_title),
Query(birth_date='1985/02/20'),
Query(portal_type='Person'),
logical_operator="AND"),
ComplexQuery(Query(title=person_title),
Query(birthplace_text='Paris'),
Query(portal_type='Person'),
logical_operator="AND"),
ComplexQuery(Query(birth_date='1985/02/20'),
Query(birthplace_text='Paris'),
Query(portal_type='Person'),
logical_operator="AND"),
logical_operator="OR")
select_expression = \
"""((title ="%s"))AS result_order """ % (person_title)
return context.portal_catalog(query=query,
select_expression=select_expression,
sort_on=(('result_order', 'DESC', 'int'),),
select_expression_key='result_order')
This will use the catalog to return all persons with a title containing person_title and who were born on '1985/02/20' or all persons with a title containing person_tile and who were born in 'Paris' or all persons who were born on 1985/02/20 in Paris, but it will sort them so that the persons with a title containing person_title will always come first.
Related Articles¶