CODEWITHSUNDEEP
djangopostgresqldjango-modelsfull-text-searchdjango-queryset
fpghost
fpghost

Reputation: 2954

Django SearchVector on choices field

If I have a simple Django model like:

from model_utils import Choices

class Art(models.Model):
    CATEGORIES = Choices(
        (1, 'DIGITAL_ART', 'Digital Art'),
        (2, 'MULTIMEDIA', 'Multimedia'),
    )
    title = models.TextField()
    category = models.PositiveSmallIntegerField(
        db_index=True, choices=CATEGORIES, blank=True, null=True
    )

How can I use SearchVector in postgres to allow for searching on both the title and categories fields? E.g. so "Some Book Digital Art" would query over both the title and categories fields.

The problem is that at the database-level the choices field is stored as an integer, not text.

Is there a way I could just map the integer to the corresponding text value when creating my search vector?

Upvotes: 1

Views: 967

Answers (1)

Paolo Melchiorre
Paolo Melchiorre

Reputation: 6122

The first solution I can think is using Conditional Expression to add the category text into the SearchVector:

from django.db.models import Case, CharField, Value, When
from django.contrib.postgres.search import SearchVector


Art.objects.annotate(
    category_text=Case(
        When(category=1, then=Value('Digital Art')),
        When(category=2, then=Value('Multimedia')),
        default=Value(''),
        output_field=CharField()
    ),
    search=SearchVector('title') + SearchVector('category_text')
).filter(
    search='Some Book Digital Art'
).values_list('title', flat=True)

The resuklt of this query will be similar to:

<QuerySet ['Some Book']>

And the SQL generated for PostgreSQL like this:

SELECT "arts_art"."title"
FROM "arts_art"
WHERE (
  to_tsvector(COALESCE("arts_art"."title", '')) ||
  to_tsvector(COALESCE(
    CASE
      WHEN ("arts_art"."category" = 1) THEN 'Digital Art'
      WHEN ("arts_art"."category" = 2) THEN 'Multimedia'
      ELSE ''
    END, '')
  )
) @@ (plainto_tsquery('Some Book Digital Art')) = TRUE

Update

You can automatically build your query from your choices list:

from django.db.models import Case, CharField, Value, When
from django.contrib.postgres.search import SearchVector

CATEGORIES = (
    (1, 'Digital Art'),
    (2, 'Multimedia'),
)

Art.objects.annotate(
    category_text=Case(
        *[When(category=c, then=Value(v)) for c, v in CATEGORIES],
        default=Value(''),
        output_field=CharField()
    ),
    search=SearchVector('title') + SearchVector('category_text')
).filter(
    search='Some Book Digital Art'
).values_list('title', flat=True)

Upvotes: 4

Related Questions