Feature #61981
closedSearch all fields in Suggest Wizard (patch)
100%
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; }
Files
Updated by Georg Ringer about 10 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?
Updated by Einar Gislason about 10 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
Updated by Einar Gislason about 10 years ago
Any chance of some more feedback regarding this issue?
Updated by Alexander Opitz about 10 years ago
- Status changed from Needs Feedback to New
Updated by Mathias Schreiber almost 7 years ago
- Status changed from New to In Progress
- Assignee set to Mathias Schreiber
Updated by Gerrit Code Review almost 7 years 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
Updated by Gerrit Code Review almost 7 years 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
Updated by Gerrit Code Review almost 7 years 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
Updated by Anonymous almost 7 years ago
- Status changed from Under Review to Resolved
- % Done changed from 0 to 100
Applied in changeset 6b7c03854c591611fd904bf250bac37e70c53aa7.