This question has been flagged
2 Replies
7759 Views

currently standard odoo search field only support search exact term by query the target field using searchfield like %searchstring, normally there is no index field for such search, so the performance is not good, on the other hand it is not possible to search several key words at the same time like search engine do. fortunately there is community module fts base, as of now it is only available for version 7.0, and the full text searching function used a separate list view to do the search on all customized modules activated those full text searching function, it is a great feature to search all the indexed content in one place, but the most useful way of using full text search is embed such search ability in the corresponding view, for example when searching this forum, the full text search function should be available, otherwise, there will be more and more duplicated posts due to missing the full text search feature.

in this post, I would like to share how I managed to make the current FTS works for Odoo 8.0 and how to the extend forum module to enable it support full text search directly.
 

Avatar
Discard
Author

the proposed solution is far from perfect, currently workable on my windows 7 system, the further improvement is to fully integrate with ORM or make ORM natively support full text search by introducing new fulltext column type with relevant additional attribute, in base model and expression to handle the SQL command conversion for those special fulltext field. finally make ORM natrually support fulltext search !

Author Best Answer

adapt fts base to odoo 8.0, change the following

1. fts_base\_openerp_.py

change from update_xml to data :  'data': ["fts_proxy.xml", 'wizard/fts_config.xml'],

2. fts_base\fts_base.py

change line 40 from cr = self.pool.db.cursor() to

      cr = self.pool.cursor()  # there is no db attribute anymore,

3. fts_base\fts_base.py and fts_base\fts_proxy.py

change from to_tsquery to plainto_tsquery if searching by key words separated by space other than by adding & bebween key words, e.g search by "full text search" other than "full & text & search"

extend website_forum module to enable full text search

here the highlight of the changes needed

1. install fts_mail

2. create new module fts_forum

  2.1 activate full text search on forum_post model by inherit from fts_base and specify the content field as full text search field

 2.2 extend the search function of forum_post class, adapt the search function from fts_base to search against the forum_post table directly and return the post ID. small tips 1.) there is parent_id = blank in args which means the result will bypass the answers , 2.) the comment stored in mail_message table other than in the forum_post table.

Here the source code

from openerp.addons.fts_base.fts_base import fts_base
from openerp.osv import osv
import logging
from openerp.osv import expression
from openerp import SUPERUSER_ID
import openerp

_logger = logging.getLogger(__name__)


class Fts_Forum(fts_base):
    _model = 'forum.post'
    _indexed_column = 'content'
    _title_column = 'name'


class forum_post(osv.osv):
    _inherit = 'forum.post'
    _model = 'forum.post'
    """The model this search works on. Required."""

    _indexed_column = 'content'
    """The column this search works on. Required.
    If this is a list of strings, all of them will be indexed for the fulltext
    search.
    """

    _table = 'forum_post'
    """The table this search works on. Will be deduced from model if
    not set."""

    _tsvector_column = 'content_tsvector'
    """The column holding tsvector data. Will be created on init.
    If not set, it will be ${_indexed_column}_tsvector."""

    _tsvector_column_index = None
    """The name of the index for _tsvector_column.
    If not set, it will be ${_indexed_column}_idx."""

    _tsvector_column_trigger = None
    """The name of the trigger to update _tsvector_column when _indexed_column
    is updated.
    If not set, it will be ${_indexed_column}_trigger."""

    _tsconfig = 'pg_catalog.simple'
    """The fulltext config (=language) to be used. Will be read from
    properties if they exist: A specific one for the current module, then
    fts_base."""

    _title_column = 'name'
    """The column to be shown as title of a match. This can be an arbitrary SQL
    expression"""

    _disable_seqscan = True
    """The postgresql query planner (as of 9.0) chooses against using the query
    planner way too often. This forces hin to use it which improves speed in all
    tested cases. Disable (and report) if this causes problems for you."""

    _extra_columns = []

    def _get_filter_expression(self, cr, uid, args, context=None):
        """Return a expression for additional filtering"""
        orm_model = self.pool.get(self._model)

        applicable_args = []

        def get_applicable_args(args, index):
            if expression.is_leaf(args[index]):
                #TODO: also check for inherited fields etc
                if ((
                                    args[index][0] in orm_model._columns or
                                    orm_model._log_access and
                                        args[index][0] in ['create_date', 'create_uid',
                                                           'write_date', 'write_uid']
                    )
                    and
                # parent_id = null is in filters, but to search answers also, need to remove it
                            args[index][0] not in ['name', 'content', 'model','parent_id']

                ):
                    return [args[index]], 1
                else:
                    return [], 1
            else:
                op1 = get_applicable_args(args, index + 1)
                op2 = get_applicable_args(args, index + op1[1] + 1)
                return (([args[index]]
                         if len(op1[0]) > 0 and len(op2[0]) > 0
                         else []) +
                        op1[0] + op2[0],
                        op1[1] + op2[1] + 1
                )

        if openerp.release.version_info[0] <= 6:
            args = get_applicable_args(expression.normalize(args), 0)[0]
        else:
            args = get_applicable_args(expression.normalize_domain(args), 0)[0]
        return expression.expression(cr, uid, args, orm_model, context)

    def _get_fts_proxy_values(self, cr, uid, row):
        """Returns the values used to create a new fts_proxy object. Override if
        you want to modify standard behavior or if you added columns in
        _extra_column"""
        return {
                'model': self._model,
                'res_id': row[0],
                'rank': row[1],
                'name': row[2],
                'summary': row[3],
               }
    def search(self, cr, uid, args, offset=0, limit=None, order=None, context=None, count=False):

        res = []

        proxy_obj = self.pool.get('fts.proxy')
        searchstring = ''

        for arg in args:
            if arg[0] == 'content' and arg[1] == 'ilike':
                searchstring = arg[2]

        if searchstring:
            _logger.info('search string', searchstring)
        else:
            _logger.debug('doing nothing because i got no search string')
            return super(forum_post, self).search(cr, uid, args, offset=offset, limit=limit, order=order,
                                                  context=context, count=count)


        if self._disable_seqscan:
            cr.execute('set enable_seqscan=off')

        filters = self._get_filter_expression(cr, uid, args, context).to_sql()
        filters = cr.mogrify(filters[0], filters[1])

        cr.execute(
            (
                "SELECT " +
                (
                    "count(*)" if count else
                    """
                    id,
                    ts_rank(%(tsvector_column)s,
                        plainto_tsquery('%(language)s', '%(searchstring)s')),
                    %(title_column)s,
                    """ +
                    (
                        """
                        ts_headline('%(language)s', %(indexed_column)s,
                            plainto_tsquery('%(language)s', '%(searchstring)s'),
                            'StartSel = *, StopSel = *')"""
                        if context.get('fts_summary')
                        else 'null'
                    )
                    +
                    ((', ' + reduce(lambda x, y: ('' if x is None else x + ',' + y),
                                    self._extra_columns))
                     if self._extra_columns else '')
                ) +
                """
                FROM %(table)s WHERE %(tsvector_column)s @@
                    plainto_tsquery('%(language)s', '%(searchstring)s')"""
            ) %
            {
                'tsvector_column': self._tsvector_column,
                'table': self._table,
                'language': self._tsconfig,
                'indexed_column': ('"' + self._indexed_column + '"'
                                   if isinstance(self._indexed_column, str)
                                   else reduce(lambda x, y: ('' if x is None else
                                                             (x + " || ' ' || ")
                                                            ) +
                                                            "coalesce(\"" + y + "\", '')",
                                               self._indexed_column)),
                'title_column': self._title_column,
                'searchstring': searchstring,
            } + ' AND ' + str(filters)
        )

        record_cnt = 0
        for row in cr.fetchall():

            if count:
                record_cnt = row[0]

            res.append(row[0])

# search comment from message.mail table
        cr.execute(
            (  "SELECT " +
               (    "count(*)" if count else
                        " res_id " ) +
                """ FROM %(table)s WHERE %(tsvector_column)s @@
                        plainto_tsquery('%(language)s', '%(searchstring)s')
                         and model ='forum.post' and subtype_id = '1'""")
            %{ 'tsvector_column':'body_tsvector',
                'table': 'mail_message',
                'language': self._tsconfig,
                'searchstring': searchstring,}
        )

        for row in cr.fetchall():

            if count:
                if record_cnt == 0: #if questions and answers already found, no record count from comment,
                    record_cnt = row[0]

            res.append(row[0])

        if self._disable_seqscan:
            cr.execute('set enable_seqscan=on')

        if count:
            return record_cnt
        else:
            return res


 

Avatar
Discard
Best Answer

add fix for 7 and 8. Now it work after install.

https://github.com/straga/odoo-addon_fts

Avatar
Discard