Django 1.10 full text search across multiple fields. How to get distinct records?

Question

Given these models:

class Listing(models.Model):
  features = models.ManyToManyField('Feature', related_name='listing_details')
  comments = models.TextField()

class Feature(models.Model):
  feature = models.CharField(max_length=100, unique=True)

How do I do a full-text search for Listings with text in either comments or one of the related Features?

I tried this:

In[28]: Listing.objects.annotate(search=SearchVector('comments', 'features__feature')).filter(search='something').count()
Out[28]: 
1215

So, I know not all those records contain the text something.

However, the number is "right" in the sense that a regular non-full-text query comes up with the same number:

In[33]: Listing.objects.filter(Q(comments__icontains='something') | Q(features__feature__icontains='something')).count()
Out[33]: 
1215

I can get down to just the Listing objects containing the text something in the comments field or in features__feature like so:

In[34]: Listing.objects.filter(Q(comments__icontains='something') | Q(features__feature__icontains='something')).distinct().count()
Out[34]: 
25

The real question boils down to how do I get those same 25 records back with full text search?

Deathtrap

Did you find a solution? The only solution I see is to either do the SQL by hand or use 3 queries, one to get the ids of the search, the second one to filter out this list of ids on the 'distinct' field you need, and the third one to redo the search (if you need to rank the results) using the unique ids. – Westernism

No, I gave up on it and plan to revisit the problem again in the future. – Deathtrap

Did you find a solution? – Mountain

No. I never got back around to looking at it more. – Deathtrap

Answer 1

I used ManyToManyField in SearchVector with StringAgg to avoid strange duplication and have correct results.

In your example the correct query should be:

from django.contrib.postgres.aggregates import StringAgg
from django.contrib.postgres.search import SearchVector

Listing.objects.annotate(
    search=SearchVector('comments') + SearchVector(StringAgg('features__feature', delimiter=' '))
).filter(search='something')

Filiate

Will it work with SearchRank? And how to use it? – Mountain