Feature #61981

Search all fields in Suggest Wizard (patch)

Added by Einar Gislason over 4 years ago. Updated 8 months ago.

Status:
Closed
Priority:
Should have
Category:
Backend User Interface
Target version:
-
Start date:
2014-09-30
Due date:
% Done:

100%

PHP Version:
Tags:
Complexity:
Sprint Focus:

Description

Problem: It is not possible to search across all available fields in the suggest wizard. This patch solves this problem
http://docs.typo3.org/typo3cms/TCAReference/AdditionalFeatures/WizardsConfiguration/Index.html#wizards-configuration-suggest

Case: We store our authors in a table with separate fields for first, middle and last name. The table is quite large and there are many people that share a first or last name. Finding a certain person is almost impossible unless you search for both first and last name. The current code only allowes you to search in one field.
Example:
Database: first_name = John, last_name = Doe
Query: "John Doe" will currently return empty as it matches neither first or last name

The patch explodes the searchString on "+" (plus) and then loops through each word. I've used "+" but this could be any character that makes sense.

So in stead of the select clause being like this (using search query "John Doe"):
(first_name LIKE '%john doe%' OR last_name LIKE '%john doe%')
...it will be (using search query "John + Doe"):
((first_name LIKE '%john%' OR last_name LIKE '%john%') AND (first_name LIKE '%doe%' OR last_name LIKE '%doe%'))

This will allow you to type two or more words and be more specific in you query. This should not break anything as you can search like normal if you don't use the "+" sign.

File: typo3/sysext/backend/Classes/Form/Element/SuggestDefaultReceiver.php

     protected function prepareSelectStatement() {
        $searchWholePhrase = $this->config['searchWholePhrase'];
        $searchString = $this->params['value'];
        $searchUid = (int)$searchString;

        if (strlen($searchString)) {
            $searchStrings = explode('+', $searchString); // Perhaps use another char than "+"?
            $selectParts = array();
            foreach($searchStrings as $searchWord){
                $searchWord = $GLOBALS['TYPO3_DB']->quoteStr($searchWord, $this->table);
                $searchWord = trim($searchWord);
                $likeCondition = ' LIKE \'' . ($searchWholePhrase ? '%' : '') . $GLOBALS['TYPO3_DB']->escapeStrForLike($searchWord, $this->table) . '%\'';
                // Search in all fields given by label or label_alt
                $selectFieldsList = $GLOBALS['TCA'][$this->table]['ctrl']['label'] . ',' . $GLOBALS['TCA'][$this->table]['ctrl']['label_alt'] . ',' . $this->config['additionalSearchFields'];
                $selectFields = GeneralUtility::trimExplode(',', $selectFieldsList, TRUE);
                $selectFields = array_unique($selectFields);
                $fieldSelectParts = array();
                foreach ($selectFields as $field) {
                    $fieldSelectParts[] = $field . $likeCondition;
                }
                $selectParts[] = '(' . implode(' OR ', $fieldSelectParts) . ')';
            }

            $this->selectClause = '(' . implode(' AND ', $selectParts) . ')';

            if ($searchUid > 0 && $searchUid == $searchString) {
                $this->selectClause = '(' . $this->selectClause . ' OR uid = ' . $searchUid . ')';
            }
        }
        if (isset($GLOBALS['TCA'][$this->table]['ctrl']['delete'])) {
            $this->selectClause .= ' AND ' . $GLOBALS['TCA'][$this->table]['ctrl']['delete'] . ' = 0';
        }
        if (count($this->allowedPages)) {
            $pidList = $GLOBALS['TYPO3_DB']->cleanIntArray($this->allowedPages);
            if (count($pidList)) {
                $this->selectClause .= ' AND pid IN (' . implode(', ', $pidList) . ') ';
            }
        }
        // add an additional search condition comment
        if (isset($this->config['searchCondition']) && strlen($this->config['searchCondition']) > 0) {
            $this->selectClause .= ' AND ' . $this->config['searchCondition'];
        }
        // add the global clauses to the where-statement
        $this->selectClause .= $this->addWhere;
    }

SuggestDefaultReceiver.php View (13.5 KB) Einar Gislason, 2014-09-30 15:44

Associated revisions

Revision 6b7c0385 (diff)
Added by Mathias Schreiber over 1 year ago

[FEATURE] Allow flexible search in Suggest Wizard

The Suggest Wizard now allows to search for multiple
terms in field by splitting the searchterm by +.

This allows to search for a combination of values which
is helpful when dealing with large data sets.

Resolves: #61981
Releases: master
Change-Id: I764039a575ca5d9ccbd97dd3dc57f3947906a5c3
Reviewed-on: https://review.typo3.org/55546
Reviewed-by: Andreas Fernandez <>
Tested-by: Andreas Fernandez <>
Tested-by: TYPO3com <>
Reviewed-by: Christian Kuhn <>
Tested-by: Christian Kuhn <>

History

#1 Updated by Georg Ringer over 4 years ago

  • Status changed from New to Needs Feedback

I don't know if this makes it too difficult? you could easily solve your problem if you just combine both fields in another column and let the search happen there. what about thaT?

#2 Updated by Einar Gislason over 4 years ago

Thanks for the response, Georg. I thought about that solution as well.

This would work where the fields in question have something in common, like first and last name could be joined into a "name" field.
However it wouldn't make any sense to combine first and last name with address and city into one field. Say I wanted to search for Alex in Berlin or if I wanted to find all with last name Thomson in Denmark. This is only possible by searching across all (searchable) fields. The solution I came up with doesn't solve all problems but in my opinion it makes searching much more flexible.

I suggested using a plus sign (+) but it could just as well be a comma (,) or some other character that makes sense.

What do you think? :)

-Einar

#3 Updated by Einar Gislason over 4 years ago

Any chance of some more feedback regarding this issue?

#4 Updated by Alexander Opitz over 4 years ago

  • Status changed from Needs Feedback to New

#5 Updated by Mathias Schreiber over 1 year ago

  • Status changed from New to In Progress
  • Assignee set to Mathias Schreiber

#6 Updated by Gerrit Code Review over 1 year ago

  • Status changed from In Progress to Under Review

Patch set 1 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/55546

#7 Updated by Gerrit Code Review over 1 year ago

Patch set 2 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/55546

#8 Updated by Gerrit Code Review over 1 year ago

Patch set 3 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/55546

#9 Updated by Anonymous over 1 year ago

  • Status changed from Under Review to Resolved
  • % Done changed from 0 to 100

#10 Updated by Benni Mack 8 months ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF