Clean up un-searchable fields

If you decide to make some field unsearchable, then CouchCMS doesn't add field's text data to fulltext search for any future pages. However for existing pages field's data is still available in search. My following function is called once to fix that.

Will work only on most recent (April 26, 2018) version of CouchCMS from git https://github.com/CouchCMS/CouchCMS/archive/master.zip

Code: Select all
<cms:func 'update_fulltext_exclude_unsearchable' masterpage='' ><cms:ignore>

    // This function processes editables with parameter searchable='0'
    // For each such field, text value is removed from fulltext search in db.
    //
    // Need to run only once after editable was given that parameter.
    //
    // Sample:
    // <cms:call 'update_fulltext_exclude_unsearchable' masterpage="data.php"/>

    </cms:ignore>
    <cms:set masterpage = "<cms:get 'masterpage' default=k_template_name />" />
    <cms:php>
        // Validate masterpage
        global $DB, $CTX;
        $masterpage = $CTX->get('masterpage');
        $rs = $DB->select( K_TBL_TEMPLATES, array('id'), "name='". $DB->sanitize($masterpage) ."'");
        if( !count($rs) ) die( "cms:func '". $CTX->get('k_func') ."': masterpage invalid" );
    </cms:php>

    <cms:concat 'Processing template: <strong>' masterpage '</strong><br/><br/>' />

    <cms:db_fields masterpage=masterpage>
        <cms:if searchable = '0' >
            <cms:concat "Found un-searchable field: <strong>" name '</strong><br/>'/>
            <cms:php>
                // Remove field's text value from fulltext
                global $DB, $CTX;
                $sql = "UPDATE ". K_TBL_FULLTEXT ." cf INNER JOIN ". K_TBL_DATA_TEXT ." cdt ON cf.page_id = cdt.page_id SET cf.content = REPLACE (cf.content, cdt.search_value, '') WHERE cdt.field_id = ". $CTX->get('id') ." AND NOT cdt.search_value = '' ";
                $rs = $DB->_query( $sql );
                echo( '<i>'. $sql .'</i><br/>' );
                echo( ($rs)? 'Updating... SUCCESS' : 'Updating... FAIL' );
                echo( '<br/><br/>' );
            </cms:php>
        </cms:if>
    </cms:db_fields>

</cms:func>



Sample call:
Code: Select all
<cms:call 'update_fulltext_exclude_unsearchable' masterpage="data.php"/>

Output:
Processing template: data.php

Found un-searchable field: richtext
UPDATE couch_fulltext cf INNER JOIN couch_data_text cdt ON cf.page_id = cdt.page_id SET cf.content = REPLACE (cf.content, cdt.search_value, '') WHERE cdt.field_id = 121 AND NOT cdt.search_value = ''
Updating... SUCCESS

Found un-searchable field: include
UPDATE couch_fulltext cf INNER JOIN couch_data_text cdt ON cf.page_id = cdt.page_id SET cf.content = REPLACE (cf.content, cdt.search_value, '') WHERE cdt.field_id = 32 AND NOT cdt.search_value = ''
Updating... SUCCESS