| 1 |
================ |
| 2 |
Full text search |
| 3 |
================ |
| 4 |
|
| 5 |
The database functions in the ``django.contrib.postgres.search`` module ease |
| 6 |
the use of PostgreSQL's `full text search engine |
| 7 |
<https://www.postgresql.org/docs/current/textsearch.html>`_. |
| 8 |
|
| 9 |
For the examples in this document, we'll use the models defined in |
| 10 |
:doc:`/topics/db/queries`. |
| 11 |
|
| 12 |
.. seealso:: |
| 13 |
|
| 14 |
For a high-level overview of searching, see the :doc:`topic documentation |
| 15 |
</topics/db/search>`. |
| 16 |
|
| 17 |
.. currentmodule:: django.contrib.postgres.search |
| 18 |
|
| 19 |
The ``search`` lookup |
| 20 |
===================== |
| 21 |
|
| 22 |
.. fieldlookup:: search |
| 23 |
|
| 24 |
A common way to use full text search is to search a single term against a |
| 25 |
single column in the database. For example: |
| 26 |
|
| 27 |
.. code-block:: pycon |
| 28 |
|
| 29 |
>>> Entry.objects.filter(body_text__search="Cheese") |
| 30 |
<QuerySet [<Entry: Cheese on Toast recipes>, <Entry: Pizza Recipes>]> |
| 31 |
|
| 32 |
This creates a ``to_tsvector`` in the database from the ``body_text`` field |
| 33 |
and a ``plainto_tsquery`` from the search term ``'Cheese'``, both using the |
| 34 |
default database search configuration. The results are obtained by matching the |
| 35 |
query and the vector. |
| 36 |
|
| 37 |
To use the ``search`` lookup, ``'django.contrib.postgres'`` must be in your |
| 38 |
:setting:`INSTALLED_APPS`. |
| 39 |
|
| 40 |
``SearchVector`` |
| 41 |
================ |
| 42 |
|
| 43 |
.. class:: SearchVector(*expressions, config=None, weight=None) |
| 44 |
|
| 45 |
Searching against a single field is great but rather limiting. The ``Entry`` |
| 46 |
instances we're searching belong to a ``Blog``, which has a ``tagline`` field. |
| 47 |
To query against both fields, use a ``SearchVector``: |
| 48 |
|
| 49 |
.. code-block:: pycon |
| 50 |
|
| 51 |
>>> from django.contrib.postgres.search import SearchVector |
| 52 |
>>> Entry.objects.annotate( |
| 53 |
... search=SearchVector("body_text", "blog__tagline"), |
| 54 |
... ).filter(search="Cheese") |
| 55 |
<QuerySet [<Entry: Cheese on Toast recipes>, <Entry: Pizza Recipes>]> |
| 56 |
|
| 57 |
The arguments to ``SearchVector`` can be any |
| 58 |
:class:`~django.db.models.Expression` or the name of a field. Multiple |
| 59 |
arguments will be concatenated together using a space so that the search |
| 60 |
document includes them all. |
| 61 |
|
| 62 |
``SearchVector`` objects can be combined together, allowing you to reuse them. |
| 63 |
For example: |
| 64 |
|
| 65 |
.. code-block:: pycon |
| 66 |
|
| 67 |
>>> Entry.objects.annotate( |
| 68 |
... search=SearchVector("body_text") + SearchVector("blog__tagline"), |
| 69 |
... ).filter(search="Cheese") |
| 70 |
<QuerySet [<Entry: Cheese on Toast recipes>, <Entry: Pizza Recipes>]> |
| 71 |
|
| 72 |
See :ref:`postgresql-fts-search-configuration` and |
| 73 |
:ref:`postgresql-fts-weighting-queries` for an explanation of the ``config`` |
| 74 |
and ``weight`` parameters. |
| 75 |
|
| 76 |
``SearchQuery`` |
| 77 |
=============== |
| 78 |
|
| 79 |
.. class:: SearchQuery(value, config=None, search_type='plain') |
| 80 |
|
| 81 |
``SearchQuery`` translates the terms the user provides into a search query |
| 82 |
object that the database compares to a search vector. By default, all the words |
| 83 |
the user provides are passed through the stemming algorithms, and then it |
| 84 |
looks for matches for all of the resulting terms. |
| 85 |
|
| 86 |
If ``search_type`` is ``'plain'``, which is the default, the terms are treated |
| 87 |
as separate keywords. If ``search_type`` is ``'phrase'``, the terms are treated |
| 88 |
as a single phrase. If ``search_type`` is ``'raw'``, then you can provide a |
| 89 |
formatted search query with terms and operators. If ``search_type`` is |
| 90 |
``'websearch'``, then you can provide a formatted search query, similar to the |
| 91 |
one used by web search engines. ``'websearch'`` requires PostgreSQL ≥ 11. Read |
| 92 |
PostgreSQL's `Full Text Search docs`_ to learn about differences and syntax. |
| 93 |
Examples: |
| 94 |
|
| 95 |
.. _Full Text Search docs: https://www.postgresql.org/docs/current/textsearch-controls.html#TEXTSEARCH-PARSING-QUERIES |
| 96 |
|
| 97 |
.. code-block:: pycon |
| 98 |
|
| 99 |
>>> from django.contrib.postgres.search import SearchQuery |
| 100 |
>>> SearchQuery("red tomato") # two keywords |
| 101 |
>>> SearchQuery("tomato red") # same results as above |
| 102 |
>>> SearchQuery("red tomato", search_type="phrase") # a phrase |
| 103 |
>>> SearchQuery("tomato red", search_type="phrase") # a different phrase |
| 104 |
>>> SearchQuery("'tomato' & ('red' | 'green')", search_type="raw") # boolean operators |
| 105 |
>>> SearchQuery( |
| 106 |
... "'tomato' ('red' OR 'green')", search_type="websearch" |
| 107 |
... ) # websearch operators |
| 108 |
|
| 109 |
``SearchQuery`` terms can be combined logically to provide more flexibility: |
| 110 |
|
| 111 |
.. code-block:: pycon |
| 112 |
|
| 113 |
>>> from django.contrib.postgres.search import SearchQuery |
| 114 |
>>> SearchQuery("meat") & SearchQuery("cheese") # AND |
| 115 |
>>> SearchQuery("meat") | SearchQuery("cheese") # OR |
| 116 |
>>> ~SearchQuery("meat") # NOT |
| 117 |
|
| 118 |
See :ref:`postgresql-fts-search-configuration` for an explanation of the |
| 119 |
``config`` parameter. |
| 120 |
|
| 121 |
``SearchRank`` |
| 122 |
============== |
| 123 |
|
| 124 |
.. class:: SearchRank(vector, query, weights=None, normalization=None, cover_density=False) |
| 125 |
|
| 126 |
So far, we've returned the results for which any match between the vector and |
| 127 |
the query are possible. It's likely you may wish to order the results by some |
| 128 |
sort of relevancy. PostgreSQL provides a ranking function which takes into |
| 129 |
account how often the query terms appear in the document, how close together |
| 130 |
the terms are in the document, and how important the part of the document is |
| 131 |
where they occur. The better the match, the higher the value of the rank. To |
| 132 |
order by relevancy: |
| 133 |
|
| 134 |
.. code-block:: pycon |
| 135 |
|
| 136 |
>>> from django.contrib.postgres.search import SearchQuery, SearchRank, SearchVector |
| 137 |
>>> vector = SearchVector("body_text") |
| 138 |
>>> query = SearchQuery("cheese") |
| 139 |
>>> Entry.objects.annotate(rank=SearchRank(vector, query)).order_by("-rank") |
| 140 |
<QuerySet [<Entry: Cheese on Toast recipes>, <Entry: Pizza recipes>]> |
| 141 |
|
| 142 |
See :ref:`postgresql-fts-weighting-queries` for an explanation of the |
| 143 |
``weights`` parameter. |
| 144 |
|
| 145 |
Set the ``cover_density`` parameter to ``True`` to enable the cover density |
| 146 |
ranking, which means that the proximity of matching query terms is taken into |
| 147 |
account. |
| 148 |
|
| 149 |
Provide an integer to the ``normalization`` parameter to control rank |
| 150 |
normalization. This integer is a bit mask, so you can combine multiple |
| 151 |
behaviors: |
| 152 |
|
| 153 |
.. code-block:: pycon |
| 154 |
|
| 155 |
>>> from django.db.models import Value |
| 156 |
>>> Entry.objects.annotate( |
| 157 |
... rank=SearchRank( |
| 158 |
... vector, |
| 159 |
... query, |
| 160 |
... normalization=Value(2).bitor(Value(4)), |
| 161 |
... ) |
| 162 |
... ) |
| 163 |
|
| 164 |
The PostgreSQL documentation has more details about `different rank |
| 165 |
normalization options`_. |
| 166 |
|
| 167 |
.. _different rank normalization options: https://www.postgresql.org/docs/current/textsearch-controls.html#TEXTSEARCH-RANKING |
| 168 |
|
| 169 |
``SearchHeadline`` |
| 170 |
================== |
| 171 |
|
| 172 |
.. class:: SearchHeadline(expression, query, config=None, start_sel=None, stop_sel=None, max_words=None, min_words=None, short_word=None, highlight_all=None, max_fragments=None, fragment_delimiter=None) |
| 173 |
|
| 174 |
Accepts a single text field or an expression, a query, a config, and a set of |
| 175 |
options. Returns highlighted search results. |
| 176 |
|
| 177 |
Set the ``start_sel`` and ``stop_sel`` parameters to the string values to be |
| 178 |
used to wrap highlighted query terms in the document. PostgreSQL's defaults are |
| 179 |
``<b>`` and ``</b>``. |
| 180 |
|
| 181 |
Provide integer values to the ``max_words`` and ``min_words`` parameters to |
| 182 |
determine the longest and shortest headlines. PostgreSQL's defaults are 35 and |
| 183 |
15. |
| 184 |
|
| 185 |
Provide an integer value to the ``short_word`` parameter to discard words of |
| 186 |
this length or less in each headline. PostgreSQL's default is 3. |
| 187 |
|
| 188 |
Set the ``highlight_all`` parameter to ``True`` to use the whole document in |
| 189 |
place of a fragment and ignore ``max_words``, ``min_words``, and ``short_word`` |
| 190 |
parameters. That's disabled by default in PostgreSQL. |
| 191 |
|
| 192 |
Provide a non-zero integer value to the ``max_fragments`` to set the maximum |
| 193 |
number of fragments to display. That's disabled by default in PostgreSQL. |
| 194 |
|
| 195 |
Set the ``fragment_delimiter`` string parameter to configure the delimiter |
| 196 |
between fragments. PostgreSQL's default is ``" ... "``. |
| 197 |
|
| 198 |
The PostgreSQL documentation has more details on `highlighting search |
| 199 |
results`_. |
| 200 |
|
| 201 |
Usage example: |
| 202 |
|
| 203 |
.. code-block:: pycon |
| 204 |
|
| 205 |
>>> from django.contrib.postgres.search import SearchHeadline, SearchQuery |
| 206 |
>>> query = SearchQuery("red tomato") |
| 207 |
>>> entry = Entry.objects.annotate( |
| 208 |
... headline=SearchHeadline( |
| 209 |
... "body_text", |
| 210 |
... query, |
| 211 |
... start_sel="<span>", |
| 212 |
... stop_sel="</span>", |
| 213 |
... ), |
| 214 |
... ).get() |
| 215 |
>>> print(entry.headline) |
| 216 |
Sandwich with <span>tomato</span> and <span>red</span> cheese. |
| 217 |
|
| 218 |
See :ref:`postgresql-fts-search-configuration` for an explanation of the |
| 219 |
``config`` parameter. |
| 220 |
|
| 221 |
.. _highlighting search results: https://www.postgresql.org/docs/current/textsearch-controls.html#TEXTSEARCH-HEADLINE |
| 222 |
|
| 223 |
.. _postgresql-fts-search-configuration: |
| 224 |
|
| 225 |
Changing the search configuration |
| 226 |
================================= |
| 227 |
|
| 228 |
You can specify the ``config`` attribute to a :class:`SearchVector` and |
| 229 |
:class:`SearchQuery` to use a different search configuration. This allows using |
| 230 |
different language parsers and dictionaries as defined by the database: |
| 231 |
|
| 232 |
.. code-block:: pycon |
| 233 |
|
| 234 |
>>> from django.contrib.postgres.search import SearchQuery, SearchVector |
| 235 |
>>> Entry.objects.annotate( |
| 236 |
... search=SearchVector("body_text", config="french"), |
| 237 |
... ).filter(search=SearchQuery("œuf", config="french")) |
| 238 |
<QuerySet [<Entry: Pain perdu>]> |
| 239 |
|
| 240 |
The value of ``config`` could also be stored in another column: |
| 241 |
|
| 242 |
.. code-block:: pycon |
| 243 |
|
| 244 |
>>> from django.db.models import F |
| 245 |
>>> Entry.objects.annotate( |
| 246 |
... search=SearchVector("body_text", config=F("blog__language")), |
| 247 |
... ).filter(search=SearchQuery("œuf", config=F("blog__language"))) |
| 248 |
<QuerySet [<Entry: Pain perdu>]> |
| 249 |
|
| 250 |
.. _postgresql-fts-weighting-queries: |
| 251 |
|
| 252 |
Weighting queries |
| 253 |
================= |
| 254 |
|
| 255 |
Every field may not have the same relevance in a query, so you can set weights |
| 256 |
of various vectors before you combine them: |
| 257 |
|
| 258 |
.. code-block:: pycon |
| 259 |
|
| 260 |
>>> from django.contrib.postgres.search import SearchQuery, SearchRank, SearchVector |
| 261 |
>>> vector = SearchVector("body_text", weight="A") + SearchVector( |
| 262 |
... "blog__tagline", weight="B" |
| 263 |
... ) |
| 264 |
>>> query = SearchQuery("cheese") |
| 265 |
>>> Entry.objects.annotate(rank=SearchRank(vector, query)).filter(rank__gte=0.3).order_by( |
| 266 |
... "rank" |
| 267 |
... ) |
| 268 |
|
| 269 |
The weight should be one of the following letters: D, C, B, A. By default, |
| 270 |
these weights refer to the numbers ``0.1``, ``0.2``, ``0.4``, and ``1.0``, |
| 271 |
respectively. If you wish to weight them differently, pass a list of four |
| 272 |
floats to :class:`SearchRank` as ``weights`` in the same order above: |
| 273 |
|
| 274 |
.. code-block:: pycon |
| 275 |
|
| 276 |
>>> rank = SearchRank(vector, query, weights=[0.2, 0.4, 0.6, 0.8]) |
| 277 |
>>> Entry.objects.annotate(rank=rank).filter(rank__gte=0.3).order_by("-rank") |
| 278 |
|
| 279 |
Performance |
| 280 |
=========== |
| 281 |
|
| 282 |
Special database configuration isn't necessary to use any of these functions, |
| 283 |
however, if you're searching more than a few hundred records, you're likely to |
| 284 |
run into performance problems. Full text search is a more intensive process |
| 285 |
than comparing the size of an integer, for example. |
| 286 |
|
| 287 |
In the event that all the fields you're querying on are contained within one |
| 288 |
particular model, you can create a functional |
| 289 |
:class:`GIN <django.contrib.postgres.indexes.GinIndex>` or |
| 290 |
:class:`GiST <django.contrib.postgres.indexes.GistIndex>` index which matches |
| 291 |
the search vector you wish to use. For example:: |
| 292 |
|
| 293 |
GinIndex( |
| 294 |
SearchVector("body_text", "headline", config="english"), |
| 295 |
name="search_vector_idx", |
| 296 |
) |
| 297 |
|
| 298 |
The PostgreSQL documentation has details on |
| 299 |
`creating indexes for full text search |
| 300 |
<https://www.postgresql.org/docs/current/textsearch-tables.html#TEXTSEARCH-TABLES-INDEX>`_. |
| 301 |
|
| 302 |
``SearchVectorField`` |
| 303 |
--------------------- |
| 304 |
|
| 305 |
.. class:: SearchVectorField |
| 306 |
|
| 307 |
If this approach becomes too slow, you can add a ``SearchVectorField`` to your |
| 308 |
model. You'll need to keep it populated with triggers, for example, as |
| 309 |
described in the `PostgreSQL documentation`_. You can then query the field as |
| 310 |
if it were an annotated ``SearchVector``: |
| 311 |
|
| 312 |
.. code-block:: pycon |
| 313 |
|
| 314 |
>>> Entry.objects.update(search_vector=SearchVector("body_text")) |
| 315 |
>>> Entry.objects.filter(search_vector="cheese") |
| 316 |
<QuerySet [<Entry: Cheese on Toast recipes>, <Entry: Pizza recipes>]> |
| 317 |
|
| 318 |
.. _PostgreSQL documentation: https://www.postgresql.org/docs/current/textsearch-features.html#TEXTSEARCH-UPDATE-TRIGGERS |
| 319 |
|
| 320 |
Trigram similarity |
| 321 |
================== |
| 322 |
|
| 323 |
Another approach to searching is trigram similarity. A trigram is a group of |
| 324 |
three consecutive characters. In addition to the :lookup:`trigram_similar`, |
| 325 |
:lookup:`trigram_word_similar`, and :lookup:`trigram_strict_word_similar` |
| 326 |
lookups, you can use a couple of other expressions. |
| 327 |
|
| 328 |
To use them, you need to activate the `pg_trgm extension |
| 329 |
<https://www.postgresql.org/docs/current/pgtrgm.html>`_ on PostgreSQL. You can |
| 330 |
install it using the |
| 331 |
:class:`~django.contrib.postgres.operations.TrigramExtension` migration |
| 332 |
operation. |
| 333 |
|
| 334 |
``TrigramSimilarity`` |
| 335 |
--------------------- |
| 336 |
|
| 337 |
.. class:: TrigramSimilarity(expression, string, **extra) |
| 338 |
|
| 339 |
Accepts a field name or expression, and a string or expression. Returns the |
| 340 |
trigram similarity between the two arguments. |
| 341 |
|
| 342 |
Usage example: |
| 343 |
|
| 344 |
.. code-block:: pycon |
| 345 |
|
| 346 |
>>> from django.contrib.postgres.search import TrigramSimilarity |
| 347 |
>>> Author.objects.create(name="Katy Stevens") |
| 348 |
>>> Author.objects.create(name="Stephen Keats") |
| 349 |
>>> test = "Katie Stephens" |
| 350 |
>>> Author.objects.annotate( |
| 351 |
... similarity=TrigramSimilarity("name", test), |
| 352 |
... ).filter( |
| 353 |
... similarity__gt=0.3 |
| 354 |
... ).order_by("-similarity") |
| 355 |
<QuerySet [<Author: Katy Stevens>, <Author: Stephen Keats>]> |
| 356 |
|
| 357 |
``TrigramWordSimilarity`` |
| 358 |
------------------------- |
| 359 |
|
| 360 |
.. class:: TrigramWordSimilarity(string, expression, **extra) |
| 361 |
|
| 362 |
Accepts a string or expression, and a field name or expression. Returns the |
| 363 |
trigram word similarity between the two arguments. |
| 364 |
|
| 365 |
Usage example: |
| 366 |
|
| 367 |
.. code-block:: pycon |
| 368 |
|
| 369 |
>>> from django.contrib.postgres.search import TrigramWordSimilarity |
| 370 |
>>> Author.objects.create(name="Katy Stevens") |
| 371 |
>>> Author.objects.create(name="Stephen Keats") |
| 372 |
>>> test = "Kat" |
| 373 |
>>> Author.objects.annotate( |
| 374 |
... similarity=TrigramWordSimilarity(test, "name"), |
| 375 |
... ).filter( |
| 376 |
... similarity__gt=0.3 |
| 377 |
... ).order_by("-similarity") |
| 378 |
<QuerySet [<Author: Katy Stevens>]> |
| 379 |
|
| 380 |
``TrigramStrictWordSimilarity`` |
| 381 |
------------------------------- |
| 382 |
|
| 383 |
.. class:: TrigramStrictWordSimilarity(string, expression, **extra) |
| 384 |
|
| 385 |
Accepts a string or expression, and a field name or expression. Returns the |
| 386 |
trigram strict word similarity between the two arguments. Similar to |
| 387 |
:class:`TrigramWordSimilarity() <TrigramWordSimilarity>`, except that it forces |
| 388 |
extent boundaries to match word boundaries. |
| 389 |
|
| 390 |
``TrigramDistance`` |
| 391 |
------------------- |
| 392 |
|
| 393 |
.. class:: TrigramDistance(expression, string, **extra) |
| 394 |
|
| 395 |
Accepts a field name or expression, and a string or expression. Returns the |
| 396 |
trigram distance between the two arguments. |
| 397 |
|
| 398 |
Usage example: |
| 399 |
|
| 400 |
.. code-block:: pycon |
| 401 |
|
| 402 |
>>> from django.contrib.postgres.search import TrigramDistance |
| 403 |
>>> Author.objects.create(name="Katy Stevens") |
| 404 |
>>> Author.objects.create(name="Stephen Keats") |
| 405 |
>>> test = "Katie Stephens" |
| 406 |
>>> Author.objects.annotate( |
| 407 |
... distance=TrigramDistance("name", test), |
| 408 |
... ).filter( |
| 409 |
... distance__lte=0.7 |
| 410 |
... ).order_by("distance") |
| 411 |
<QuerySet [<Author: Katy Stevens>, <Author: Stephen Keats>]> |
| 412 |
|
| 413 |
``TrigramWordDistance`` |
| 414 |
----------------------- |
| 415 |
|
| 416 |
.. class:: TrigramWordDistance(string, expression, **extra) |
| 417 |
|
| 418 |
Accepts a string or expression, and a field name or expression. Returns the |
| 419 |
trigram word distance between the two arguments. |
| 420 |
|
| 421 |
Usage example: |
| 422 |
|
| 423 |
.. code-block:: pycon |
| 424 |
|
| 425 |
>>> from django.contrib.postgres.search import TrigramWordDistance |
| 426 |
>>> Author.objects.create(name="Katy Stevens") |
| 427 |
>>> Author.objects.create(name="Stephen Keats") |
| 428 |
>>> test = "Kat" |
| 429 |
>>> Author.objects.annotate( |
| 430 |
... distance=TrigramWordDistance(test, "name"), |
| 431 |
... ).filter( |
| 432 |
... distance__lte=0.7 |
| 433 |
... ).order_by("distance") |
| 434 |
<QuerySet [<Author: Katy Stevens>]> |
| 435 |
|
| 436 |
``TrigramStrictWordDistance`` |
| 437 |
----------------------------- |
| 438 |
|
| 439 |
.. class:: TrigramStrictWordDistance(string, expression, **extra) |
| 440 |
|
| 441 |
Accepts a string or expression, and a field name or expression. Returns the |
| 442 |
trigram strict word distance between the two arguments. |