Custom field search

[marius-ciclistu]

Nevermind. I managed to install a local copy.

[Sophia_OS]

Code: [Select]


function cust_search_keyword_wildcard_with_customfield($params) {

    if ($params['sPattern']) {
        $mSearch =  Search::newInstance();
        $query_elements = (array) json_decode($mSearch->toJson());
        $pattern = $query_elements['sPattern'];

        $query_elements['sPattern'] = str_replace(' ', '* ', $pattern) . '*';

        // Search by customfield too
        $mSearch->addJoinTable(count($query_elements['tables_join']), DB_TABLE_PREFIX."t_item_meta u", "pk_i_id = u.fk_i_item_id", 'LEFT');

        $aPattern = explode(' ', $pattern);
        $customfieldCond = '';

        foreach ($aPattern as $word) {
            if ($word) $customfieldCond .= sprintf(" || u.s_value like '%%%s%%'", $word);
        }

        $mSearch->addConditions("1 = 1 " . $customfieldCond);
        $mSearch->addGroupBy(DB_TABLE_PREFIX.'t_item.pk_i_id');
        $mSearch->setJsonAlert($query_elements);
    }
}

osc_add_hook('search_conditions', 'cust_search_keyword_wildcard_with_customfield', 1);


Please test.

thank you Marius, but it even didn't find customfields

[marius-ciclistu]

Query without your function

Code: [Select]

string(464) "SELECT oc_t_item.*, oc_t_item.s_contact_name as s_user_name
FROM (oc_t_item)
LEFT  JOIN oc_t_item_description as d ON d.fk_i_item_id = oc_t_item.pk_i_id
WHERE MATCH(d.s_title, d.s_description) AGAINST('test' IN BOOLEAN MODE)
AND ( d.fk_c_locale_code LIKE 'en_US' )
AND oc_t_item.b_enabled = 1 AND oc_t_item.b_active = 1 AND oc_t_item.b_spam = 0 AND (oc_t_item.b_premium = 1 || oc_t_item.dt_expiration >= '2018-06-19 07:45:32')
ORDER BY dt_pub_date desc
LIMIT 0, 12"
Query with your function

Code: [Select]

string(587) "SELECT oc_t_item.*, oc_t_item.s_contact_name as s_user_name
FROM (oc_t_item)
LEFT  JOIN oc_t_item_description as d ON d.fk_i_item_id = oc_t_item.pk_i_id
LEFT  JOIN oc_t_item_meta u ON pk_i_id = u.fk_i_item_id
WHERE MATCH(d.s_title, d.s_description) AGAINST('test*' IN BOOLEAN MODE)
AND ( d.fk_c_locale_code LIKE 'en_US' )
AND oc_t_item.b_enabled = 1 AND oc_t_item.b_active = 1 AND oc_t_item.b_spam = 0 AND (oc_t_item.b_premium = 1 || oc_t_item.dt_expiration >= '2018-06-19 07:44:53')
AND  1 = 1  || u.s_value like '%test%'
GROUP BY oc_t_item.pk_i_id
ORDER BY dt_pub_date desc
LIMIT 0, 12"

The title and description are hardcoded into
\oc-includes\osclass\model\Search.php

Code: [Select]

$this->dao->where(sprintf("MATCH(d.s_title, d.s_description) AGAINST('%s' IN BOOLEAN MODE)", $this->sPattern));

[marius-ciclistu]

Also remember the ft_min_word_len variable from the server( 2, 3 or 4 as value of min characters for search).
Also the s_value column from meta table should have full text index in db.

[Sophia_OS]

still couldn't fix the function to search by location too 

[marius-ciclistu]

Of course you didn't. I wrote why. You need core changes.

[Sophia_OS]

Of course you didn't. I wrote why. You need core changes.

How? Please explain step by step. Thanks

[marius-ciclistu]

If you can make s_value column full text index (and regenerate all indexes in your db), the solution would be:

Code: [Select]

        /**
         * Make the SQL for the search with all the conditions and filters specified
         *
         * @access private
         * @since unknown
         * @param bool $count
         */
        private function _makeSQL($count = false,$premium = false)
        {
            $arrayConditions    = $this->_conditions();
            $extraFields        = $arrayConditions['extraFields'];
            $conditionsSQL      = $arrayConditions['conditionsSQL'];

            $sql = '';

            if($this->withItemId) {
                // add field s_user_name
                $this->dao->select(sprintf('%st_item.*, %st_item.s_contact_name as s_user_name', DB_TABLE_PREFIX, DB_TABLE_PREFIX) );
                $this->dao->from(sprintf('%st_item', DB_TABLE_PREFIX));
                $this->dao->where('pk_i_id', (int)$this->itemId);
            } else {
                if($count) {
                    ($this->withPattern) ? $this->dao->select(DB_TABLE_PREFIX.'t_item.pk_i_id, GROUP_CONCAT(u.s_value)') : $this->dao->select(DB_TABLE_PREFIX.'t_item.pk_i_id');
                    $this->dao->select($extraFields); // plugins!
                } else {
                    ($this->withPattern) ?
                        $this->dao->select(DB_TABLE_PREFIX.'t_item.*, '.DB_TABLE_PREFIX.'t_item.s_contact_name as s_user_name, GROUP_CONCAT(u.s_value)') :
                        $this->dao->select(DB_TABLE_PREFIX.'t_item.*, '.DB_TABLE_PREFIX.'t_item.s_contact_name as s_user_name');
                    $this->dao->select($extraFields); // plugins!
                }
                $this->dao->from(DB_TABLE_PREFIX.'t_item');

                if($this->withNoUserEmail) {
                    $this->dao->where( DB_TABLE_PREFIX.'t_item.s_contact_email', $this->sEmail );
                }

                if ($this->withPattern ) {
                    $this->dao->join(DB_TABLE_PREFIX.'t_item_description as d','d.fk_i_item_id = '.DB_TABLE_PREFIX.'t_item.pk_i_id','LEFT');
                    $this->dao->join(DB_TABLE_PREFIX.'t_item_meta as u', DB_TABLE_PREFIX.'t_item.pk_i_id = u.fk_i_item_id','LEFT');
                    $this->dao->where(sprintf("MATCH(d.s_title, d.s_description, u.s_value) AGAINST('%s' IN BOOLEAN MODE)", $this->sPattern) );
                    if(empty($this->locale_code)) {
                        if(OC_ADMIN) {
                            $this->locale_code[osc_current_admin_locale()] = osc_current_admin_locale();
                        } else {
                            $this->locale_code[osc_current_user_locale()] = osc_current_user_locale();
                        }
                    }
                    $this->dao->where(sprintf("( d.fk_c_locale_code LIKE '%s' )", implode("' d.fk_c_locale_code LIKE '", $this->locale_code)));
                }

                // item conditions
                if(count($this->itemConditions)>0) {
                    $itemConditions = implode(' AND ', osc_apply_filter('sql_search_item_conditions', $this->itemConditions));
                    $this->dao->where($itemConditions);
                }
                if( $this->withCategoryId && (count($this->categories) > 0) ) {
                    $this->dao->where(sprintf("%st_item.fk_i_category_id", DB_TABLE_PREFIX) .' IN ('. implode(', ', $this->categories) .')' );
                }
                if($this->withUserId) {
                    $this->_fromUser();
                }
                if($this->withLocations || OC_ADMIN) {
                    $this->dao->join(sprintf('%st_item_location', DB_TABLE_PREFIX), sprintf('%st_item_location.fk_i_item_id = %st_item.pk_i_id', DB_TABLE_PREFIX, DB_TABLE_PREFIX), 'LEFT');
                    $this->_addLocations();
                }
                if($this->withPicture) {
                    $this->dao->join(sprintf('%st_item_resource', DB_TABLE_PREFIX), sprintf('%st_item_resource.fk_i_item_id = %st_item.pk_i_id', DB_TABLE_PREFIX, DB_TABLE_PREFIX), 'LEFT');
                    $this->dao->where(sprintf("%st_item_resource.s_content_type LIKE '%%image%%' ", DB_TABLE_PREFIX, DB_TABLE_PREFIX, DB_TABLE_PREFIX));
                    $this->dao->groupBy(DB_TABLE_PREFIX.'t_item.pk_i_id');
                }
                if($this->onlyPremium) {
                    $this->dao->where(sprintf("%st_item.b_premium = 1", DB_TABLE_PREFIX));
                }
                $this->_priceRange();

                // add joinTables
                $this->_joinTable();

                // PLUGINS TABLES !!
                if( !empty($this->tables) ) {
                    $tables = implode(', ', $this->tables);
                    $this->dao->from($tables);
                }
                // WHERE PLUGINS extra conditions
                if(count($this->conditions) > 0) {
                    $this->dao->where($conditionsSQL);
                }
                // ---------------------------------------------------------
                // groupBy
                if($this->groupBy != '') {
                    $this->dao->groupBy( $this->groupBy );
                }
                // having
                if($this->having != '') {
                    $this->dao->having($this->having);
                }
                // ---------------------------------------------------------

                // order & limit
                $this->dao->orderBy( $this->order_column, $this->order_direction);

                //group by
                $this->dao->groupBy(DB_TABLE_PREFIX.'t_item.pk_i_id');

                if($count) {
                    $this->dao->limit(100*$this->results_per_page);
                } else {
                    $this->dao->limit( $this->limit_init, $this->results_per_page);
                }
            }

            $this->sql = $this->dao->_getSelect();
            // reset dao attributes
            $this->dao->_resetSelect();

            return $this->sql;
        }
BUT THIS WON'T WORK  as it is now.

[marius-ciclistu]

SOLUTION
CORE MOD for searching also in custom fields values.
EDITED
Replace the function _makeSQL from

\oc-includes\osclass\model\Search.php

with this:
UPDATED TO WORK ALSO FOR "exactly these words" in custom fields.

Code: [Select]

        /**
         * Make the SQL for the search with all the conditions and filters specified
         *
         * @access private
         * @since unknown
         * @param bool $count
         */
        private function _makeSQL($count = false,$premium = false)
        {
            $arrayConditions    = $this->_conditions();
            $extraFields        = $arrayConditions['extraFields'];
            $conditionsSQL      = $arrayConditions['conditionsSQL'];

            $sql = '';

            if($this->withItemId) {
                // add field s_user_name
                $this->dao->select(sprintf('%st_item.*, %st_item.s_contact_name as s_user_name', DB_TABLE_PREFIX, DB_TABLE_PREFIX) );
                $this->dao->from(sprintf('%st_item', DB_TABLE_PREFIX));
                $this->dao->where('pk_i_id', (int)$this->itemId);
            } else {
                if($count) {
                    ($this->withPattern) ? $this->dao->select(DB_TABLE_PREFIX.'t_item.pk_i_id, GROUP_CONCAT(u.s_value)') : $this->dao->select(DB_TABLE_PREFIX.'t_item.pk_i_id');
                    $this->dao->select($extraFields); // plugins!
                } else {
                    ($this->withPattern) ?
                        $this->dao->select(DB_TABLE_PREFIX.'t_item.*, '.DB_TABLE_PREFIX.'t_item.s_contact_name as s_user_name, GROUP_CONCAT(u.s_value)') :
                        $this->dao->select(DB_TABLE_PREFIX.'t_item.*, '.DB_TABLE_PREFIX.'t_item.s_contact_name as s_user_name');
                    $this->dao->select($extraFields); // plugins!
                }
                $this->dao->from(DB_TABLE_PREFIX.'t_item');

                if($this->withNoUserEmail) {
                    $this->dao->where( DB_TABLE_PREFIX.'t_item.s_contact_email', $this->sEmail );
                }

                if ($this->withPattern ) {
                    $this->dao->join(DB_TABLE_PREFIX.'t_item_description as d','d.fk_i_item_id = '.DB_TABLE_PREFIX.'t_item.pk_i_id','LEFT');
                    $this->dao->join(DB_TABLE_PREFIX.'t_item_meta as u', DB_TABLE_PREFIX.'t_item.pk_i_id = u.fk_i_item_id','LEFT');
                    $customfieldCond = '';
                    $trimmedSPattern = trim($this->sPattern);
                    if (!(substr($trimmedSPattern, 0, 1) === '"' && substr($trimmedSPattern, strlen($trimmedSPattern)-1, 1) === '"')) {
                      $explodePattern = explode(' ', str_replace('+', '', str_replace('*', '', $trimmedSPattern)));

                      foreach ($explodePattern as $word) {
                        if ($word) $customfieldCond .= " OR u.s_value LIKE '%" . $word . "%'";
                      }
                    } else {
                       $customfieldCond .= " OR u.s_value LIKE '%" . trim($trimmedSPattern, '"') . "%'";
                    }
                    $this->dao->where(sprintf("(MATCH(d.s_title, d.s_description) AGAINST('%s' IN BOOLEAN MODE)", $this->sPattern) . $customfieldCond . ')');
                    if(empty($this->locale_code)) {
                        if(OC_ADMIN) {
                            $this->locale_code[osc_current_admin_locale()] = osc_current_admin_locale();
                        } else {
                            $this->locale_code[osc_current_user_locale()] = osc_current_user_locale();
                        }
                    }
                    $this->dao->where(sprintf("( d.fk_c_locale_code LIKE '%s' )", implode("' d.fk_c_locale_code LIKE '", $this->locale_code)));
                }

                // item conditions
                if(count($this->itemConditions)>0) {
                    $itemConditions = implode(' AND ', osc_apply_filter('sql_search_item_conditions', $this->itemConditions));
                    $this->dao->where($itemConditions);
                }
                if( $this->withCategoryId && (count($this->categories) > 0) ) {
                    $this->dao->where(sprintf("%st_item.fk_i_category_id", DB_TABLE_PREFIX) .' IN ('. implode(', ', $this->categories) .')' );
                }
                if($this->withUserId) {
                    $this->_fromUser();
                }
                if($this->withLocations || OC_ADMIN) {
                    $this->dao->join(sprintf('%st_item_location', DB_TABLE_PREFIX), sprintf('%st_item_location.fk_i_item_id = %st_item.pk_i_id', DB_TABLE_PREFIX, DB_TABLE_PREFIX), 'LEFT');
                    $this->_addLocations();
                }
                if($this->withPicture) {
                    $this->dao->join(sprintf('%st_item_resource', DB_TABLE_PREFIX), sprintf('%st_item_resource.fk_i_item_id = %st_item.pk_i_id', DB_TABLE_PREFIX, DB_TABLE_PREFIX), 'LEFT');
                    $this->dao->where(sprintf("%st_item_resource.s_content_type LIKE '%%image%%' ", DB_TABLE_PREFIX, DB_TABLE_PREFIX, DB_TABLE_PREFIX));
                    $this->dao->groupBy(DB_TABLE_PREFIX.'t_item.pk_i_id');
                }
                if($this->onlyPremium) {
                    $this->dao->where(sprintf("%st_item.b_premium = 1", DB_TABLE_PREFIX));
                }
                $this->_priceRange();

                // add joinTables
                $this->_joinTable();

                // PLUGINS TABLES !!
                if( !empty($this->tables) ) {
                    $tables = implode(', ', $this->tables);
                    $this->dao->from($tables);
                }
                // WHERE PLUGINS extra conditions
                if(count($this->conditions) > 0) {
                    $this->dao->where($conditionsSQL);
                }
                // ---------------------------------------------------------
                // groupBy
                if($this->groupBy != '') {
                    $this->dao->groupBy( $this->groupBy );
                }
                // having
                if($this->having != '') {
                    $this->dao->having($this->having);
                }
                // ---------------------------------------------------------

                // order & limit
                $this->dao->orderBy( $this->order_column, $this->order_direction);

                //group by
                $this->dao->groupBy(DB_TABLE_PREFIX.'t_item.pk_i_id');

                if($count) {
                    $this->dao->limit(100*$this->results_per_page);
                } else {
                    $this->dao->limit( $this->limit_init, $this->results_per_page);
                }
            }

            $this->sql = $this->dao->_getSelect();
            // reset dao attributes
            $this->dao->_resetSelect();

            return $this->sql;
        }
This will not be affected by ft_min_word_len value in custom fields' regard.
This will work with Teseo's solutions for searching all the words from the pattern(+) and wildcard (*)

Remove the last part from your functions file(the one about the custom fields).

[Sophia_OS]

SOLUTION
CORE MOD for searching also in custom fields values.

Replace the function _makeSQL from

\oc-includes\osclass\model\Search.php

with this:

Code: [Select]

        /**
         * Make the SQL for the search with all the conditions and filters specified
         *
         * @access private
         * @since unknown
         * @param bool $count
         */

        }
This will not be affected by ft_min_word_len value in custom fields' regard.
This will work with Teseo's solutions for searching all the words from the pattern(+) and wildcard (*)

Remove the last part from your functions file(the one about the custom fields).

@Marius,
Thank you for the code, but i didn't work for me  did you test it on modern theme?

[marius-ciclistu]

No. It gives some errors or what is the behaviour?
Did you remove that function from functions.php?

[Sophia_OS]

No. It gives some errors or what is the behaviour?
Did you remove that function from functions.php?

No errors.
It doesn't even find title and description search.
I tested with that function and also without that function and also removed custom fields part and tested but in all ways it searches nothing.

[marius-ciclistu]

I'll test it again in the morning then.

[Sophia_OS]

I'll test it again in the morning then.

Thanks

[marius-ciclistu]

I edited my solution and tested it. It workes on bender.  It had some issue with sprintf function and I used another method.

Please update or delete your quote with the unworking solution.