[Erp5-dev] Search text

[pavel lstiburek]

I have encountered a problem with searching by address fulltext in Person
Module. When using Address Search Key: Default (ExactMatch) and Search
Expression: Some City 3, I?m given results of all persons living in some
city and others having 3 in address (found 700 records).

After switching Address Search Key to Keyword, results are better (150 records),

BUT after switching to next page it is showing page 2 / 35 - 700 records.

Result again contains Some City 7,Some Other City 3, etc.

Sumarized: Searching with Search Key Keyword is working at first listbox
page, but other pages seems like been searching with default search key
which is ExactMatch.

Is it possible to change default Search Key to keyword?



Pavel Lstiburek
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.tiolive.com/pipermail/erp5-dev/attachments/20120628/a4f49a01/attachment.htm>

[Vincent Pelletier]

Hi.

On Thu, 28 Jun 2012 13:56:46 +0200,

Post by pavel lstiburek
address fulltext

[...]

Post by pavel lstiburek
Search Key: Default (ExactMatch)

[...]

Post by pavel lstiburek
Search Key to Keyword

This seems to be the root of the issue you are encountering:
As you probably already know, "fulltext" is a kind of SQL index (in
addition of being a catalog column setting to lookup valued indexed
with fulltext SQL indexes).

The "Address (Fulltext)" listbox column you made visible (IIRC it is
not visible by default) is configured to use such fulltext index, which
internally uses the "SearchableText" column, located in the "full_text"
table - which as you guess has a fulltext index on that column.

This also means that the relevant SearchKey configuration is on the
"SearchableText" column, which should use the FullText SearchKey to
work properly (ie, to actually use the fulltext index, which requires
a different SQL output from other types of indexes).

Post by pavel lstiburek
After switching Address Search Key to Keyword, results are better
(150 records), BUT after switching to next page it is showing page
2 / 35 - 700 records.

This is very surprising indeed. If you can reproduce it reliably, could
you run the following python script after the first lookup and after
the second ?

portal = context.getPortalObject()
return portal.portal_catalog(
src__=1,
**portal.portal_selections.getSelectionParamsFor(
'person_module_selection'))

You may strip the list of values compared with "security_uid" column,
in order to reduce verbosity.

In order to reproduce the issue, I'll need to know better what you
installed:
- How did you install your ERP5 instance, and which version ?
If it is a vifib.net-managed instance, please send me directly
(off-list, in a separate mail) its identifier as displayed
in your admin space ("SOFTINST-" followed by a serie of numbers).
If it is a local slapos installation using slapproxy, please give me
the URL you used (the one you gave as a parameter to
slap.registerSuply().supply()).
- Which fulltext business template did you install before installing
erp5_base ? (myisam, sphinxse or mroonga)

Regards,

[pavel lstiburek]

HI,
I an not the one who instal it, but it is local slapos, with
erp5_full_text_myisam_catalog<http://bsys2.dyndns.org:12001/erp5/portal_templates/6?ignore_layout:int=1&editable_mode:int=1&selection_name=business_template_selection&selection_index=0&reset:int=1>,
verzion 5.4.7. revision 4.
I run the script and i get results:

first good page:
SELECT DISTINCT
catalog.path, catalog.uid
FROM
full_text AS related_child_address_SearchableText_1_full_text,
catalog AS catalog, catalog AS related_child_address_SearchableText_catalog

WHERE
1 = 1
AND (((`catalog`.`security_uid` IN (73, 74, 75, 76, 77, 78, 79, 80, 81,
82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93)
OR `catalog`.`viewable_owner` = 'zope')
AND `catalog`.`portal_type` = 'Person'
AND `related_child_address_SearchableText_1_full_text`.`SearchableText`
LIKE '%praha 7%')
AND (catalog.uid = related_child_address_SearchableText_catalog.parent_uid
AND related_child_address_SearchableText_catalog.portal_type = 'Address'
AND related_child_address_SearchableText_catalog.uid =
related_child_address_SearchableText_1_full_text.uid))
LIMIT 1000

second bad page:

SELECT DISTINCT
catalog.path, catalog.uid ,MATCH
(`related_child_address_SearchableText_1_full_text`.`SearchableText`)
AGAINST ('%praha 7%') AS `SearchableText`
FROM
full_text AS
related_child_address_SearchableText_1_full_text, catalog AS catalog,
catalog AS related_child_address_SearchableText_catalog
WHERE
1 = 1
AND (((`catalog`.`security_uid` IN (73, 74, 75, 76, 77, 78, 79, 80,
81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93)
OR `catalog`.`viewable_owner` = 'zope')
AND `catalog`.`portal_type` = 'Person'
AND MATCH (`related_child_address_SearchableText_1_full_text`.`SearchableText`)
AGAINST ('%praha 7%'))
AND (catalog.uid = related_child_address_SearchableText_catalog.parent_uid
AND related_child_address_SearchableText_catalog.portal_type = 'Address'
AND related_child_address_SearchableText_catalog.uid =
related_child_address_SearchableText_1_full_text.uid))
LIMIT 1000


Is it possible to change some script to get the same result? or instal some
other fulltext business template to get better results?

Pavel Lstiburek
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.tiolive.com/pipermail/erp5-dev/attachments/20120630/4d84cc72/attachment.htm>

[Vincent Pelletier]

Hi.

On Sat, 30 Jun 2012 07:06:01 +0200,
[...]

Post by pavel lstiburek
`related_child_address_SearchableText_1_full_text`.`SearchableText`
LIKE '%praha 7%')

[...]
[...]

Post by pavel lstiburek
AND MATCH
(`related_child_address_SearchableText_1_full_text`.`SearchableText`)
AGAINST ('%praha 7%'))

The second request is what is expected in both cases when using the
SearchableText column: being fulltext-indexed, it needs to use
MATCH..AGAINST, not LIKE.

When using a fulltext field to search for a value, the "%" syntax
doesn't apply anymore. You should search for "praha 7" instead,
preserving the quotes in the searched value.

Nevertheless, the fact that they are different shows that there is a
bug in ERP5, I'll look into that. Could you give me the output of this
one-liner, so I can check how that column is configured ?

return repr(context.getPortalObject().portal_catalog.getSQLCatalog().getSearchKey('full_text.SearchableText'))

Regards,

[pavel lstiburek]

2012/7/2 Vincent Pelletier <vincent at nexedi.com>

Post by Vincent Pelletier
Hi.
On Sat, 30 Jun 2012 07:06:01 +0200,
[...]

Post by pavel lstiburek
`related_child_address_SearchableText_1_full_text`.`SearchableText`
LIKE '%praha 7%')

[...]
[...]

Post by pavel lstiburek
AND MATCH
(`related_child_address_SearchableText_1_full_text`.`SearchableText`)
AGAINST ('%praha 7%'))

The second request is what is expected in both cases when using the
SearchableText column: being fulltext-indexed, it needs to use
MATCH..AGAINST, not LIKE.
When using a fulltext field to search for a value, the "%" syntax
doesn't apply anymore. You should search for "praha 7" instead,
preserving the quotes in the searched value.
Nevertheless, the fact that they are different shows that there is a
bug in ERP5, I'll look into that. Could you give me the output of this
one-liner, so I can check how that column is configured ?
return
repr(context.getPortalObject().portal_catalog.getSQLCatalog().getSearchKey('full_text.SearchableText'))
Regards,
--
Vincent Pelletier
ERP5 - open source ERP/CRM for flexible enterprises

result is <Products.ZSQLCatalog.SearchKey.FullTextKey.FullTextKey object at
0x7f5e2cc8cd50>

btw. my friend tells me that on tiolive erp5 site they have in search page
set search key Default(Keyword).
This may be solution to my problem as well. Is it possible to set this on
my erp5?