Fulltext searches¶

Since version 1.11, Data Query supports fulltext searches. A working fulltext query requires several components.

1. First and foremost, the tables which we want to query using a fulltext search must have at least one fulltext index. For example, the “tt_content” table could be extended to be searchable on header and bodytext by using the adding SQL statement to an extension’s ext_tables.sql file:

   CREATE TABLE tt_content (
       FULLTEXT KEY SEARCH (header,bodytext)
   );
   

2. In a Data Query query, the fulltext search index must be used as part of the list of selected fields and reference with an alias. A special syntax is needed, using the fulltext keyword and the name of the index (i.e. “SEARCH” from the example above). Example:

   SELECT uid, pid, header,
   fulltext:SEARCH AS score
   FROM tt_content
   

3. A Data Filter must be defined using either the fulltext or the fulltext_natural operator. The former will trigger a boolean search, the latter a natural language search. Please refer to the MySQL documentation for more details.

   All operators are supported except for the distance operator (@).

   Note

   Query expansion is currently not supported.

   In the Data Filter, the alias declared in the query must be used. In the example below, the first line will lead to a boolean search, the second one to a natural language search:

   score fulltext gp:sword
   score fulltext_natural gp_sword
   

   When the query is interpreted a MATCH() ... AGAINST construct is used, automatically placed both in the SELECT part and in the WHERE clause.

   The alias can of course be used in the “Order by” part of the Data Filter, making it possible to sort the query results by relevance. Example:

   field = score
   order = desc
   

   This will place most relevant results first.

In the TYPO3 BE, Data Query provides some help and hints below the query field.

Hints displayed about fulltext indices in the TYPO3 BE